--- In firebird-support@yahoogroups.com, Robin Davis <Ro.Davis@...> wrote:

>
> Hi,
>
> I'm sure this question has been asked a thousand times. If so, I'm

sorry.

>
> 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?
>
> Sorry if this is a stupid question.
>
> Thanks in advance,
>
> Rob Davis

Well it doesn't seem stupid but it is definately unusual. A query of a
single record by its primary key is usually under 10ms on any
reasonable hardware. The primary key constraint automatically adds a
supporting index to the table, so the query will to an indexed read
from this index.

Please tell me you did not explicitly define an index on Invoice
Number. This would create a second index that wastes space and
confuses the optimiser. Drop the index right now.

But even if you manage to confuse the optimiser to do a natural scan
of the table, 8 seconds for 7000 records is slow for a PII-200, unless
each record is huge, so you may have something else in play. Run your
query from iSQL. If you don't get the same issue, then your problem
isn't with Firebird.

Secondly, it is usually a bad practice to use a field that has real
world significance as your primary key. By all means give it a unique
constraint if that is the requirement, but it will probably bite you
one day when the powers that be change your requirement and the field
is no longer suitable as a PK. This has no bearing on performance, but
is just a recommended practice.

Adam