DLOOKUP vs Multiple Queries

J

Joe Williams

What is the advantages/disadvantages to using dlookup to pull a value from
different tables into a query or joining the query to other queries to get
values form the other tables? Performance, speed, etc impacts?

I have queries where there are two or three sub queries to get the record
set I need and was wondering if it is just faster to do dlookups to get teh
values I need rather than running all ofthe sub queries and tabel joins to
find the data.

Thanks

-joe
 
K

Ken Snell

DLookups in queries are extremely slow, and usually much slower than using
subqueries.
 
D

Duane Hookom

Although slow, DLookup()s may create an updateable recordset while
subqueries may not. Also, crosstabs might work better with DLookup()s while
erroring with subqueries. If you can use queries only, then avoid
DLookup()s.
 
K

Ken Snell

Thanks for the added info, Duane.

Just to add to this, if a subquery causes a problem with updateability and
DLookup is too slow, I often create a temp table to hold the subquery's
results and then use that temp table as a source table for the query.
 
D

Duane Hookom

Good point Ken. I often have resorted to the same for performance reasons.
This usually requires a primary or unique index on the temporary table.
 
Top