Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2009
    Provided Answers: 14

    Unanswered: DLookup and the third leg of a duck.

    I do not want to start a polemic here but I do believe it's time to "cut the third leg of a duck" (as we say in French).

    I keep on reading, here as well on many other places, that you should never use the DLookup function because it's slow and degrades the performances of an application.

    I wanted to see by myself, so I decided to write a test function that uses three different methods to retrieve pieces of information from a table:
    - the first method uses DLookup,
    - the second method uses a recordset (snapshot) and the FindFirst method,
    - The third method uses a recordset (table) and the Seek method.
    Those methods were used 10000 times each in a loop.

    Here is what I got:

    - Milliseconds when using DLookup: 11641
    - Milliseconds when using a Recordset and FindFirst: 32860
    - Milliseconds when using a Recordset and Seek: 31

    I came to the conclusion that simply saying or writing "do not use DLookup, it's very slow" is not enough. It would be useful to add "if you can use Seek instead, because FindFirst is almost three times slower".

    Unfortunately, we all know that it is not always possible to use Seek: you need to be able to open your recordset as a table and there must be an index on the column(s) used to perform the search. As far as I'm concerned, I'll not systematically disregard DLookup without having second thoughts in the future.

    You can find the mdb I used in the attachment, so you'll be able to test it by yourself, just call the Test_Using_DLookup() function then have a look at the Test_DLookup.log file that will be created in the process.

    Comments are very welcome.

    Have a nice day!

  2. #2
    Join Date
    May 2005
    You may find Allen Browne's article on Dlookup and a proposed alternative interesting. Usually if it's not a one off lookup or if it's in a multi-user enviornment than I'll do a recordset method and just loop through till I find what I need.
    Me.Geek = True

  3. #3
    Join Date
    Mar 2009
    Provided Answers: 14
    Thanks nckdryr!

    I already know the site of Allen Browne's, as well as some others, and they are true gold mines as far as Access is concerned. My point was that there is no "one fits all" method. It depends on the situation you're faced to.

    Have a nice day!

  4. #4
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    I don't have a problem with using dLookups, and other domain functions , however they do come with a performance hit. used sparingly (or perhaps appropriately) they are a powerful tool.

    For the majority of circumstances there are other ways of retrieving the information.

    its a while back, I forget the details, but removing numerous domain functions (including many Dlookups), the report run time was reduced from over 6 minutes+ to sub 15 seconds.

    so to me, although I tend to regard domain functions as the spawn of the devil they are sometimes a useful tool. but that doesn't mean they should be used any and everywhere. they shouldn't be used if say there are several columns to be retrieved from the DB using the same domain/where clause
    Last edited by healdem; 03-31-09 at 01:40.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2007
    Adelaide, South Australia
    I use domain functions all over the place. Except in Queries and Reports, where the performance hit is too great.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!

    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Jun 2005
    Richmond, Virginia USA
    Provided Answers: 19
    I tend to agree with you, Sinndho! I think way too much is made of this in light of the processor speeds available on even the bottom-of-the-line PCs. I think all too often we take something that may have been true a number of years ago and keep repeating it, even after technology has made it a moot point.

    Obviously you wouldn't want to use a series of 12 DLookUps to fill in 12 fields in a search form, but rather use a recordset, but retrieving a single value in this manner is not going to "break the bank" as it were.

    I have the same problem with people who post code that is so terse and cryptic as to be unintelligible to all but the most advanced programmers. When memory was at a premium, i.e. when "big" hard drives were 20 megs, brevity in code was important. With the hard drives we have today, brevity means little if anything, but having code that is easily read/understood is still important.

    Just one man's opinion!
    Hope this helps!

    The problem with making anything that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts