Subject Re: [IBO] Many records and slow searchkeybykey ?
Author Geoff Worboys
> I'm trying now to rewrite a accounting program from
> Access to Interbase (IBO+Delphi) and I'd like to know if
> there'd be any speed benefit in the main workload of the
> process: findind records :)

Then you will be very pleased to have chosen IBObjects, and I would
strongly suggest that you go with the native controls, since
IB_LookupCombo is designed to fit exactly this requirement.


> What I mean is that there are millions of records containing
> serial numbers of different products (ex: 15AT233XXX, where
> XXX is incremented from 000). Locating such a record in
> Microsoft Access was getting slower and slower as the
> database grow, getting to the point of waiting 5 sec
> after pressing a key...which is not productive at all.

And you were lucky or the app was well written. I've 30 second delays
from Access lookups (although my system was much slower way back
then).


> Could you please tell me if the same thing will happen
> in IBO+Interbase? Is there anything I can do to speed the
> things up, to be prepared for the times when the database
> will have 200.000 or more records ?

IB_LookupCombo - using a properly defined lookup dataset can allow you
to tune the application to suit. It provides an optional POS=n
(position = n) parameter on its lookup ordering which stops a search
from starting until n characters have been typed. This is implemented
via a parameter mechanism that means that the returned set of records
is greatly reduced, this is a boon to slow network connections and
essential when performing lookups against a large dataset.

You may also want to consider redesigning things a little bit to
optimise performance in this regard. If serial numbers are always...
[someprefix][xxx]

then store the prefix in a separate field. You can always create a
computed column that concatenates the two fields back into a single
string for easy access when required.


There are issues with sequence numbers of the form you mention -
unless these are allocated manually by the user - make sure you
understand the issues before going too far. There is one solution
offerred in the IBO online help (in the "How To" chapter, topic
"Maintain a pure sequence"). There are other possibilities.


Geoff Worboys
Telesis Computing