Subject Re: [IBO] Problem using locate
Author Helen Borrie
At 08:10 AM 15/08/2006, you wrote:
>I have an application that has a table with a little over 3 million
>records. In the Paradox version the user can enter a record ID, which
>is the unique primary key and select that record. The record is
>selected instantly since the table uses ID as the primary key.

That's not why the record is selected instantly in Paradox. It's
because Paradox tables and indexes are physical files. Forget
Paradox. It is chalk to Firebird's or IB's cheese.

>In the
>IBO version the locate goes off into never never land, puts out a
>message that says Fetching Query Results - Row # xxxxxx and appears to
>be searching the file sequentially. The table's primary key is the
>ID. Can anyone tell me what I am doing wrong? The locate can take 15
>or 20 minutes depending on the record ID selected.

Everything in a client/server database is based on sets. There is no
physical structure to tables. Broadly speaking, performing a Locate
on an entire huge table is potentially very costly. Locate() was
designed for desktop databases like Paradox.

Client/server performance depends on highly economical searches
restricted by WHERE criteria. IBO implements some work-alike stuff
to make sensible Locate() calls on SQL sets feasible. However, a
Locate() on a set of 3 million records is not even slightly sensible.

Provide the following:

-- the DDL statement[s] that define[s] the table and its keys
-- the SQL you are using for the set (unless you are using TIBOTable,
of course!)
-- your KeyLinks
-- the LOCATE call from your application code.