DLOOKUP vs. LEFT JOIN (1 Viewer)

benjamin.weizmann

Registered User.
Local time
Today, 03:29
Joined
Aug 30, 2016
Messages
78
hi :)
I need all the columns of table1 and just one column of table 2
in any case, I need pass thought every record

what is the best way (memory and time) to do dlookup for ever record to looking the value in table 2

or to do JOIN between the two tables from the start?

thanks u
Ben
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:29
Joined
May 7, 2009
Messages
19,175
Use join (left join)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:29
Joined
May 7, 2009
Messages
19,175
The join is only created once, while dlookup, a function is evaluated every time you recurse to each record of the resulting query. If can get aeay without using func in query do so.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:29
Joined
Feb 28, 2001
Messages
27,001
Here's the techie explanation.

Doing a JOIN (of any kind) forms a single recordset, which you can then search (using a DLOOKUP).

Using a DLOOKUP on your tables in a loop means you call DLOOKUP once per entry in your main table. But DLOOKUP( field, source, criteria ) does the equivalent of this:

Code:
QryString = "SELECT " & field & " FROM " & source & " WHERE " & criteria & " ;"
SET recordset = CurrentDB.OpenRecordset( qrystring, dbOpenDynaSet )
recordset.MoveFirst
If NOT recordset.EOF Then
   answer = recordset!field
Else
   answer = Null
End If
Recordset.Close
SET Recordset = Nothing

(And I'm not saying this is exactly what is done, only that it is something LIKE this.)

So if you do this in a loop, you create and destroy as many recordsets as you have records in the main table. That's a lot of gyrations.

If you build the JOIN first, you only need one recordset (based on the JOIN) to find the values from your 2nd table. If you still need to loop through the findings, you do it by opening ONE recordset and stepping through the records of that set, which would stay open until you were done.

Will either work? Yes. Will there be a difference in performance? Oh, HECK yes! Will there be a difference in the results? ... maybe, if the database is shared and someone adds records to the main table or updates the dependent table. Which is why one of the options in opening a recordset is dbSeeChanges, which you can look up to see how it works.
 

Users who are viewing this thread

Top Bottom