Subject Re: [IBO] How to know the size of result set before fetching?
Author Eyal
--- In, Lester Caine <lester@l...> wrote:
> Eyal wrote:
> > I wonder is there's anything in the IB/FB API that can be used to
> > retrieve the number of selected rows before fething.
> You have exactly the same problem here. You have to RUN the query
> before you know the total number of records in the answer - in
> which case FetchAll would by then have a local copy of the data ;)

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

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.

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

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.

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

> This comes back to the simple question - Why do you need to know the
> total? The user can't see them all at once, so only the records

Actually the user doesn't see any data. The user just has to wait
during a process which scans a lot of data, checks some stuff and
occasionaly updates some of the rows. I want to provide the user with
a visual progress indication because the process might take some time.

> There are a number of ways of maintaining counts via triggers, so
> that a full count of records is not required, and a simple table
> with count results in it can be accessed fast to remove the
> COUNT(*) delay. It's a matter of looking at the alternatives.

I agree, and indeed I use such a table for some of the data. However
it's only applicable when there's a well defined criteria to the data
that I want to count. In the particular case that I'm working on at
the moment, it isn't trivial to maintain a count.