Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2009

    Unanswered: SQL vs Dlookup ( performance )

    A penny for your thoughts.

    Im working on a system where one of the reports 12 parameters per Employee from 12 tables and a query. This reports can be done for any number of Employees.

    There seem to be 2 ways to do this in VBA:

    - Write a very, very long SQL statement, to extract all the right data in one sweep. I also need a couple of sub queries in this. Then INSERT * FROM SQL into a tempTable, use the tempTable as the source for the report.

    - Or
    For Each Employee
    For each parameter
    Dloopkup / Elookup ( parameter, in table , employee = x )
    INSERT parameters in tempTable

    Imo, first one looks more elegant, and as it should be done. But it is a hell of a job to write the SQL statement and Im not sure if I will get all the sub-queries working.

    Second one looks a bit rough and Im not sure about the performance.

    Any thoughts on performances of the second route? Should I be worried there? Number of Emplouees is 300, some tables will reach 2000 records.



  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    domain functions such as DLookup are fine when used selectively hwoever they can be a resource hog and can significantly affect throughput/perfromance. for me they are fine in a form, and fine if used once or twice in a report (say to get group header/footer stuff)

    however you should be able to structure your query to get all the data it needs in one hit by using joins (or using the query designer). there should be no need to dump that stuff into a temporary table. tem tables are fine if you have limited access to the main data (say you need to run a reporting suite against a production (server) database). dumping that data into temp tables can save the live db from being trashed whilst you run reports.

    joins are vital to getting the best from SQL, so in my books theres no time like the present to dive on in and start JOINing tables. you can do most joins using the query designer if you so wish, bear in mind you can alter the JOIN properties by selecting the JOIN line and then changing its properties
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2009
    Provided Answers: 15
    Same opinion here: Use DLookUp and other domain functions wisingly and cautiously, which usually means sparsely.
    Have a nice day!

  4. #4
    Join Date
    Feb 2004
    One Flump in One Place
    Yup, domain functions run like a dog. They are handy though as you can use these for UPDATE statements in lieu of MAX() and other aggregate functions (which fail)

  5. #5
    Join Date
    Apr 2009
    Well, that seems an unanimous decision. No excuses left, I will build a string of 12 left joins. It will be a record for me, that's for sure. Thank you all.

Posting Permissions

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