Subject Performance
Author pokka_chi
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.

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.

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

Thanks in advance.