On Sat, 9 Nov 2002, Paul Reeves wrote:

> Robert S. Sciuk wrote:
> >
> > I apologize in advance if this is an FAQ, but I've never seen it on any of
> > the web resources I've scanned (quite a few, actually). Doc pointers
> > would be very welcome!
> >
>
> Actually, this limitation is sort of documented - you just asked the
> question in the wrong way! Typically the discussion revolves around the
> use of 'select count()' and why it takes so long.
>
> Essentially the answer is to do with the multi-generational
> architecture. Because of versioning it is impossible to maintain an
> accurate count as the value will usually differ from transaction to
> transaction. Hence there is no api call to do this a priori. You need to
> do a 'select count()', which takes a long time as every row must be
> checked for the correct version for the current transaction.
>
> So, you have a choice - either lay in the overhead of running a count
> query first, or dispense with this and fetch rows as needed. The new
> Firebird FIRST SKIP syntax should help with this.

Paul,

Aha! I'm not crazy (well, maybe I am ... 8-).

Thanks for your kind reply. Had I put a little more brain power into it,
I probably would have deduced the architectural side effects ... but hey,
it was Friday afternoon.

I guess there also exists a race condition unless the select count/select
are within a single transaction ... I'll code up a workaround and see how
it goes. Your explanation also indicates that this is a "feature" and not
a "glitch" 8-), and so I'll just have to live with the overhead -- not a
problem. Most likely I'll cache the results locally, and maintain a
counter on the cache buffer.

I suspect that the 2nd pass would have the added advantage of hitting
table data in cache, so the 2nd select should proceed somewhat faster???
Perhaps I'll try to determine in advance whether the query is likely to
have a large result set (no where clause) vs a more manageable one.

Thanks again.

Cheers,
Rob.