DLookup hideously slow when used as query criteria

  • Thread starter Thread starter alanh
  • Start date Start date
A

alanh

I am looking up rows in an order_details table in a Sybase
database by order_number. The order_number I want is put
into a 1 row table. The query I run uses the DLookup
function as criteria in the order_number column of the
Sybase table which I link to using Sybase's ODBC driver.

The Sybase table that I am looking into has millions of
rows. It has a unique non-clustered index on order_number.

When I use DLookup as the criteria of the query to pull
the data from the order_details Sybase table, it takes
hours to complete. If I explictly use the actual order
number as the criteria, the results are returned in a
fraction of a second.

I've also tried making a join between my 1 row table with
the order_number and the order_details Sybase table. In my
1 row table, I've made the order_number column the primary
key so it has a unique index on it. I do the join between
my 1 row table and the Sybase order_details table on
order_number. This join also takes hours to complete.

If I make a 1 row table in Sybase, put a unique non-
clustered index on it and then join it to the
order_details table, the results are returned almost
instantly.

So the question is why does the DLookup approach or the
join approach taking so long to complete in Access? It's
like neither is making use of the indices.

OS = W2k sp4. Office version is O2k sr-1. I am running a
2.4GHz P4 with 1GB ram and wide scsi drives. Sybase ASE
v12. ODBC driver is also v12.
 
Yes, DLookup() will be completely unworkable for what you describe.

It should be possible to get a JOIN to give near-instantaneous results: did
you experiment with whether it makes any difference if you place the
criteria under the related table's foreign key field as against the main
table's primary key?

A subquery would be much faster than DLookup(), but still not as good as an
INNER JOIN should be.
 
Back
Top