Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Which is faster (DLookup textbox vs query) (2000)

    I have a few values I would like to have constantly displayed on my main form. They must be retrieved using DLookup. Right now the formulas are in text boxes, and it seems to me that using DLookup in a query would add an unnecessary step in querying the information. Am I correct in assuming that putting it in a query and then referring to the query would be slower?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Which is faster (DLookup textbox vs query) (2000)

    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.

    In short DLookup's can slow down your form updates.

    I have used crosstab queries for sum calculations of data and then requeried summing the column data in the past.
    Jerry

  3. #3
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Which is faster (DLookup textbox vs query) (2000)

    Thanks for the info. The dlookups are the only info being displayed on that form (it's pretty much a switchboard, specific to a certain office), so the rest is command buttons.... I'll try to build some queries, however my question still lies - if I use dlookups in the query, am I really just chasing my tail?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Which is faster (DLookup textbox vs query) (2000)

    Putting DLookups in a query, then referring to a value from the query will most probably be slower than using the DLookups directly. If you could avoid the DLookups altogether, that would be faster, but on a switchboard-like form, that is probably not possible. I don't think it really matters in a switchboard form, by the way.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Which is faster (DLookup textbox vs query) (2000)

    Hans

    >I don't think it really matters in a switchboard form, by the way

    Just for my learning experience can you clarify that last sentence...why please?
    Jerry

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Which is faster (DLookup textbox vs query) (2000)

    A switchboard form is static, so a delay of .1 second won't matter. It would be different in a form where the user browses through hundreds of records.

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Which is faster (DLookup textbox vs query) (2000)

    Thanks, I don't use switchboards myself, but that is an interesting point. Would there be similar response time if I am using an unbound form, say for a menu or admin function then?

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>Sorry for hijacking your thread Jeremy, this is an interesting subject
    Jerry

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Which is faster (DLookup textbox vs query) (2000)

    In an unbound form, the DLookup expression will only be evaluated when you open the form, unless you recalculate it explicitly, so you won't notice a small delay.

  9. #9
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Which is faster (DLookup textbox vs query) (2000)

    No worries on the hijack - it's interesting to me as well. FYI, my form is unbound, it has a total of 24 text boxes with a mix of dlookups and dcounts on it, and they're blank for less than a second - and by no means is it on a fast computer.

    Hans - thank you for your input, I'd figured as much, but wanted to know if there was *any* benefit of putting the dlookup in a query.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  10. #10
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Which is faster (DLookup textbox vs query) (2000)

    Jeremy

    Then I would say there was no benefit in putting them in a query (for your example) as it appears you want the figures for information purposes only, you don't appear to be storing them so go ahead.
    Jerry

  11. #11
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Which is faster (DLookup textbox vs query) (2000)

    <img src=/S/grin.gif border=0 alt=grin width=15 height=15> <img src=/S/yep.gif border=0 alt=yep width=15 height=15> All is well....still paddling <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  12. #12
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Which is faster (DLookup textbox vs query) (2000)

    Given how you plan on using them (displaying information on a switchboard), you don't even need to consider the performance issues. Any timing differences would only be noticable when processing large recordsets containing thousands (and more likely tens of thousands) of records. You couldn't possible notice the difference in a single DLookup vs. a query. Just do whichever is easier in the situation.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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