Subject Re: [firebird-support] Re: Slow search on a primary key index
Author Robin Davis

Adam wrote:
>> I have a table of invoices where the primary key is also the invoice
>> number. I use Access 2003 as the front-end and would like to be able to
>> search my Firebird 2.0 database by invoice number quickly when asked to
>> - usually by people questioning their bill! ;-) However, even with a
>> tiny number like 7000 records to search, it can take over 8 seconds to
>> find the invoice required. Is there any way I can speed up the searches
>> so that they are virtually instantaneous, as they are with other
>> non-primary key indexed fields?
>> Well it doesn't seem stupid but it is definately unusual.
>> Please tell me you did not explicitly define an index on Invoice
>> Number.
Nope, no second index.

>> Run your
>> query from iSQL. If you don't get the same issue, then your problem
>> isn't with Firebird.
I did that, and the result came back instantly. Ergo, a problem with the
search fucntion in Access I suppose. Strange, if I FILTER for the
record, the result is as quick as iSQL. A way round the problem I
suppose, but not ideal.
>> Secondly, it is usually a bad practice to use a field that has real
>> world significance as your primary key.
Thanks for the advice, I will try and sort that out. I tried to create a
computed field that was equal to the primary key, but then Firebird
wouldn't let me create an index on it.

Thanks for getting back,

Rob Davis