--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:

>
> First, I don't think it's going through the entire table. I think
> the slowness has one or two possible causes, viz.
>
> 1) it's using your index and, because of the physical size of the
> index, it's taking a horrendously long time to build the
> bitmap. (Check the plan to verify or discount this theory!)
>

Why does FB need to build a bitmap to use the index? There could be
millions of records that are indexed. Can't FB just start moving
through the index list/tree once it decides what index to use?

>
> If so, then add a condition to prevent it from using the index , e.g.
> SELECT
> FIRST 50 * FROM TABLE WHERE FLAG='Y' and 1=1
>
> ...or drop the index if you have nothing that needs it.
>

When weve processed most of the records we could have just a few left
to process out of millions. Our thought was that an index would be
most helpful in this situation.

>
> 2) You are using a ReadCommitted transaction with WAIT lock
> resolution. No doubt, a "queue" table is taking inserts fairly
> frequently. With these settings, your query will literally keep
> waiting until there is a long enough gap in the inserting for it to
> actually get a valid "first 50" records, which could take ages. Each
> time the feeding application commits something it has to re-run the
> query to ensure that it's got the specified "first 50".
>
> So try your query with Concurrency/WAIT and see if it makes a

difference.

>

We will give this a try.

>
> If this is actually part of a stored procedure then don't use SELECT
> FIRST at all. Instead, do this:
>
> [snip]
>

We will give this a try also. Thank you for your extensive comments.