At 08:51 AM 9/03/2006, Jason Dodson wrote:

>The reason for that is the time to run this is the time it takes to
>prepare the query, NOT fetch the records. The
>preperation of these two will be the same, only what they fetch will
>be different.
>
>Jason
>
>
>jimbehr3446 wrote:
> > --- In firebird-support@yahoogroups.com, "Ivan Prenosil"
> > <Ivan.Prenosil@...> wrote:
> >> Or are you saying that first command is not significantly faster
> > than second one?
> >> SELECT FIRST 50 * FROM TABLE WHERE FLAG='Y';
> >> SELECT COUNT(*) FROM TABLE;

Nope. Prepare simply queries the metadata and populates the XSQL*
definitions. The only size difference between these two queries is
that the first one will return n xsqlvar definitions (where n is the
number of output fields), whereas the second will return one xsqlvar,
with the metadata of the count. The difference in terms of time
elapsed would be insignificantly small, since the first has to
perform a joined query on four small system tables and return a
single row, whereas the engine doesn't have to query anything to work
out the type that count(*) returns.

What will be significant, with this large table, is the first-time
run of the first query, if that index is used, since it will be
paging through many levels of indirection on disk to build the index
bitmap. The size of the table AND the fact that it's got a lot of
garbage record versions means that building the initial bitmap will
be expensive, since the index pages contain pointers to all
interesting rows, not just those currently committed; and the query
will do whatever garbage collection it can do while visiting the pages.

The bitmap will be cached, so a subsequent run of the same query will
read the bitmap directly from the cache and will find a high
proportion of the pages in the cache as well.

SELECT COUNT(*) of course doesn't use an index, but it will cache
visited pages AND do whatever garbage collection it can.

./heLen