Dlookup Vs Recordset and efficiency

[Chris]

I make a lot of use of the DLookup function to retrieve data back from the
tables, but wondered at what point it is more efficient to use a recordset
when retrieving many values back from the same table.

e.g. if I needed only 1 item back from table A, I would have thought that
DLookup would be more efficient than defining a recordset to get the data. If
I needed two values back from the same table, would two DLookups still be
more efficient that using a recordset. At what point would it make sense to
use a recordset?

Many Thanks
Chris

 

['69 Camaro]

Hi, Chris.

At what point would it make sense to
use a recordset?

At the point where you are willing to sacrifice a little bit of extra coding
time in exchange for speed of execution. The Domain functions (DLookup,
DCount, DMax, et cetera) will be less efficient than coding for a Recordset
Object. (Unless one intentionally misuses the Recordset, such as addint
JOIN's with many other unnecessary tables and returning all fields instead of
just the necessary fields). The reason the Domain functions are less
efficient is because there's some additional overhead involved with Access's
automatic creation of a Recordset Ojbect. This additional overhead costs
time and system resources.

If this Domain function were to be processed within a loop that is executed
numerous times, the extra overhead becomes very noticeable to the user.
However, when used sparingly, there's only a little bit of difference in
execution times that the average user often won't be able to positively
identify as "This is so S-L-O-W!" But it is. They just don't recognize it.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[David C. Holley]

I was using probably 10 or so DLookup()'s using the same criteria on the
same table to get a single value. Converting to using a RS object was
actually quite easy. In my case, I created a single SUB to load up
global variables from the recordSet and then call it right before I need
the values. As soon as the code finishes, I've got another SUB that is
called that clears out the global variables.

 

[Chris]

Hi Gunny

Thanks for that. That is useful to know. I guess the key points I need to
consider would be:

1. network traffic and
2. Frequency (if as you say running in a loop)

 

[SteveS]

You might also look at

http://allenbrowne.com/ser-42.html

Allen wrote a function called -> Extended DLookup() <-

"Purpose: Faster and more flexible replacement for DLookup()"
"Arguments: Same as DLookup, with additional Order By option."

All the work has been done for you!!! Well, most of it

 

[David C. Holley]

I know that using DLookups() in a query is a BAD thing due to ONE
DLookup() being done per record in the results. What are the effects if
the DLookups are spun out to a function? In my DB, I have the following
three tables

tblTransports
tblTransportGuests
tblClients

tblTransportGuests contains the lngTransportId and lngClientId for the
transport and guest respectively. In tblTransportGuests the field
ynPrimaryPassenger indicates that the passenger is the PrimePax.

I'm now wondering if I should now mod the function below to use a
RecordSet or not.

Ideas?

Function getPrimaryPassengerForTransport(lngTransportId As Long,
strFormat As String)

Dim lngClientID As Variant
Dim strPassengerFirstName As Variant
Dim strPassengerLastName As Variant
Dim strWhereCriteria As String

lngClientID = DLookup("lngClientID", "tblTransferGuests",
"lngTransportId = " & lngTransportId & " AND ynPrimaryPassenger = True")
If IsNull(lngClientID) = True Then
getPrimaryPassengerForTransport = "-No Primary PAX-"
Else
strWhereCriteria = "lngClientId = " & lngClientID
strPassengerFirstName = DLookup("txtClientFirstName",
"tblClients", strWhereCriteria)
strPassengerLastName = DLookup("txtClientLastName",
"tblClients", strWhereCriteria)
Select Case strFormat
Case "FN", "FirstLast"
getPrimaryPassengerForTransport = strPassengerFirstName
& " " & strPassengerLastName
Case "NF", "LastFirst"
getPrimaryPassengerForTransport = strPassengerLastName
& ", " & strPassengerFirstName
Case Else
getPrimaryPassengerForTransport = strPassengerFirstName
& " " & strPassengerLastName
End Select
End If

End Function

 

[Rick Brandt]

I know that using DLookups() in a query is a BAD thing due to ONE
DLookup() being done per record in the results. What are the effects
if the DLookups are spun out to a function? In my DB, I have the
following three tables

tblTransports
tblTransportGuests
tblClients

tblTransportGuests contains the lngTransportId and lngClientId for the
transport and guest respectively. In tblTransportGuests the field
ynPrimaryPassenger indicates that the passenger is the PrimePax.

I'm now wondering if I should now mod the function below to use a
RecordSet or not.

Ideas?

You are calling three DLookups. If you use two Recordsets that return one
row each and use a single database object to retrieve all three values then
you reduce the creation of database objects from three to one and the number
of actual SQL statements run from three to two.

Is that more efficient? Obviously it is. Will you notice the difference?
I doubt it. Again the problem with Domain functions is not that apparent
when you are using just a few of them. If I have more than a couple I will
usually opt for another method, but there are no absolutes as to when the
user will be able to tell the difference.

 

[David C. Holley]

Thought so. I can drop it down to a single recordSet by using a JOIN in
the statement. The issue that I was curious about was that a slightly
unnoticeable delay can become noticable when your dealing with large
number of records. (Feel free to send me a dollar a day.)

 

[Rick Brandt]

Thought so. I can drop it down to a single recordSet by using a JOIN
in the statement. The issue that I was curious about was that a
slightly unnoticeable delay can become noticable when your dealing
with large number of records. (Feel free to send me a dollar a day.)

With proper indexing the number of records to be searched should not be a
factor, only how many rows you need to retrieve and how many lookups it takes to
do that.

The join is a good idea though.

 

[David C. Holley]

So it really wouldn't matter that much that I might have to do the
DLookup on 1,000 records?

 

[Rick Brandt]

So it really wouldn't matter that much that I might have to do the
DLookup on 1,000 records?

If you mean you need to use DLookup to retrieve one value from a table with 1000
records (a very small table actually) then yes, as long as the field used in the
WHERE argument is indexed the number of records in the table matters little.
You should expect instantaneous results even with a few hundred thousand rows in
the table.