""mircostange

> (As posted before) I have an SQL select statement with a number of
> joins - pretty complicated - which performs the SELECT * FROM ...
> in 0.0300 secs.

IB Server only fetches the required rows to fill the buffers requested by
the remote interface. The remote interface only requests a certain number
of buffers, then keeps requesting more buffers as it's client side buffers
become drained. A Client such a Delphi requests records a row at a time.

From this you can see that if you supply a query and fetch the first row, IB
may be able to get away with retrieving little data from disk, if it is a
"natural" query (deliver the data in any order), or can be retrieved using
an index (or merged indices) to negate the need for IB to get all data
before providing data to the client.

Any kind of sort or aggregation that means that the whole resultset needs to
be formed prior to delivering a row of data (e.g. count(*) or order by a
non-indexed field), will mean that IB will have to go and read every
relevant page of data from disk (or cache), thus slowing the first (or only
in the case of count(*)), row of data to the client.

Now for me that was pretty clear explaination, but if it still seems foggy,
let me know.

Cheers,


JAC


To my surprise, the same statement modified to do a

> SELECT count(*) from ... takes more than 5 secs - this is more than
> 150 times slower!
>
> Does anyone have an explanation for this? For the details of the
> query see my previous post with "horrible performance" in it.
>
> Mirco
>