Subject Re: [IBO] How to know the size of result set before fetching?
Author Helen Borrie
At 12:09 AM 7/06/2005 +0000, Eyal wrote:

>There's a difference between running the query and fetching the data.

Yes. Running a SELECT query on a table or view begins caching rows on the
server. They stay there until the client asks to fetch them. Running a
SELECT procedure is different. There is a single row "cache" in which the
latest output row simply sits until the client asks for that row.

>After a client issues a query the server processes it and produces a
>result set. I'm not really familiar with the innerworkings of IB/FB,
>but as far as I understand, a final result set must be prepared before
>a single row can be transferred to the client.

You're confusing things here. "Preparing" is a metadata query that returns
a structure containing various facts about the objects referred to in a
STATEMENT. User data is not prepared.

>That's because a query isn't just a simple retrieval of rows, but also
>involves conditions and re-ordering. Also, due to the strict
>versioning of IB/FB, all rows must be "stamped" with a transaction

While that's true, it's not equivalent to a "transaction context" from the
client's point of view. The server maintains a transaction inventory so
that it knows the state of all rows in all tables at all times. Client
applications never reach in there - although the internal transaction id is
available to applications via the CURRENT_TRANSACTION context
variable. This isn't the same as the transaction handle that the client
passes in the TPB.

>So at the point when the result set is ready the server already knows
>exactly how many rows are in the result set, even though not a single
>row has yet been transferred to the client.

Wrong. Rows are ready to be fetched as soon as the server-side buffer is
loaded. Buffered datasets on the client side - such as TIB_BDataset and
its offspring - create their own buffers to receive fetched rows into. The
two buffers are unrelated. Datasets fetch one row at a time from the
server's buffer and stop fetching when the client-side buffer is full. As
others have told you, the number of rows that will ultimately be delivered,
once the statement is finished executing and the client has fetched the
last row, is unknown until the last row is fetched.

>Maybe there's some way to get this information WITHOUT fetching any rows.

There isn't....well, unless you do what RecordCountAccurate does - a
(usually) costly "select count(*)" query that is run before the actual
statement request is passed.