At 03:50 AM 22/04/2007, you wrote:

>I am running a large db (around 4GB size) on FB2.0.1. I have a table
>around 1 million records with a primary key of 3 integer fields (say A,
>B, C). When I try to load a batch of records (say 10) starting from a
>particular record (e.g. A=1, B=2, C=3), I use the query:
>
>select first 10 * from MyTable where A>1 or (A=1 and (B>2 or (B=2 and
>C>=3))) order by A, B, C
>
>Sometimes, it is very fast but sometimes it is very slow. I've tried
>eliminating other factors (e.g. network) by running it in a standalone
>PC. I have also tried to force the plan by adding the clause
>
>PLAN(DOC ORDER PK INDEX(PK, PK)) // note: PK is the primary key's name
>
>The performance is still unpredictible. I have also checked the plan
>used in those cases of slow performance and it is using index.

Using your plan, the optimizer can't use its own plan: your plan is
forcing the PK to be used (twice!?!?!?! is this a typo?) for the
ORDER BY operation and it has nothing to use for the searches.

That PK is not particularly useful for those complex or-ed searches,
especially the last one (it can't be used at all). I think you will
find a benefit from adding individual indexes on B and C and removing
your plan.

>Usually when the query is executed the first time, it is slow. It is
>very fast when it is executed the second time. It seems that FB uses a
>lot of time to determine to use the index at the first time.

The first time it is constructing several bitmaps and tracking
through the same index tree twice. It doesn't have the benefit of
indexes for the search. Subsequently, it may be able to locate some
(or even most) of the data and index pages to avoid disk i/o, making
it less slow. However, the problem you really need to address is how
to make it easier for the optimizer to use a good plan.

>Pls help to see if there is a way to resolve this issue.

I think it would be good for you abandon the belief that all database
engines use identical optimization methods. You don't use scissors
to cut timber and you don't use a chainsaw to cut silk.

Study up on Firebird's indexing and realise a) how your plan is
preventing the optimizer from doing what it knows best and b) how
relatively useless compound indexes really are for complex search conditions.

This would be a good place to start:
http://www.ibphoenix.com/main.nfs?a=ibphoenix&page=ibp_expert1

./heLen