Subject RE: [firebird-support] Forcing optimizer to use index
Author Leyne, Sean
> > Without I_PBMCLAIM_DATESBM instead of scanning a few months of data
> > it's scanning a few years.
> Keep in mind and make sure, that:
> * You test in a fetch all rows scenario. While a certain index might make
> execution of a statement look fast when fetching the first bunch of rows
> automatically due to a client request, things may look different compared to
> non-indexed access when you fetch all records

A tip,

The easiest way I have found to measure the fetch all/total query execution is to enclose the candidate query in a SELECT COUNT(*) FROM () as in:


The advantage of this approach is that the entire query is processed on the server and only a single row is returned.

Otherwise, as Thomas stated, you may end up measuring only the time it takes to return the first batch of rows, not the entire query -- which can have dramatically different performance profile (it may take 1s to process the first batch of rows, but 10m to process the next 10 -- due to the query construction/sub-queries).