473,320 Members | 1,876 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Dlookup running very slowly

91
Some of my Dlookups run very fast, where some take up to 3 or 4 seconds to return a value. The table the slow one is coming from is only 10 records and 3 columns.

What are the causes for Dlookup to run slow?
Jun 30 '10 #1
14 7165
NeoPa
32,556 Expert Mod 16PB
Generally, like any table access, searching via a non-indexed field would be my best guess.
Jul 1 '10 #2
matt753
91
Changed them to indexed but still the same speed
Jul 1 '10 #3
NeoPa
32,556 Expert Mod 16PB
Nothing else comes to mind.

How do you know it is actually the DLookup() call that's actually eating up the time? Are you tracing it? Could it be form initialisation or other such factors muddying the waters perhaps?
Jul 1 '10 #4
missinglinq
3,532 Expert 2GB
I have to agree with NeoPa! DLookup() taking 3 or 4 seconds to return a value from a table with only 10 records and 3 columns doesn't sound right! It really does sound like something else is going on here.

What datatype(s) are we talking about?

Linq ;0)>
Jul 1 '10 #5
matt753
91
How do you know it is actually the DLookup() call that's actually eating up the time? Are you tracing it? Could it be form initialisation or other such factors muddying the waters perhaps?
If I put a debug dot in and step through it, once the yellow highlight is on the line of code, then I press F8 and it pauses for a few seconds then assigns the lookup value to the textbox.
Jul 5 '10 #6
matt753
91
I have to agree with NeoPa! DLookup() taking 3 or 4 seconds to return a value from a table with only 10 records and 3 columns doesn't sound right! It really does sound like something else is going on here.

What datatype(s) are we talking about?
It is looking up a 'Double' with 2 decimal places from the backend. It goes either into a textbox or a double variable.

I'm not sure if it has anything to do with it, but for some reason in the form, if the looked up value is "0.12", it will show "0.119385766345" instead. Even if I put Round(DlookupStatement,2)
Jul 5 '10 #7
NeoPa
32,556 Expert Mod 16PB
matt753: If I put a debug dot in and step through it, once the yellow highlight is on the line of code, then I press F8 and it pauses for a few seconds then assigns the lookup value to the textbox.
That's good reasoning. I wonder how far away the back end is? It seems like a long time for such a small table. I very much doubt that any numeric conversion is causing an appreciable delay.
Jul 5 '10 #8
matt753
91
Seems to still run a bit faster if I move the backend onto the local machine. The whole database runs a bit faster, a lot less lag between switching records, etc.

Not sure why it slows down so much when running it off the server. We have a small Acer server running Windows Home Server with a 64GB SSD inside (only thing on the SSD is the backend). Have a new 24 port Cisco Gigabit switch, with everyone running gigabit to their computers. Approx 5 users or less connected at any time.
Jul 6 '10 #9
NeoPa
32,556 Expert Mod 16PB
I can see nothing there that would account for any noticeable lag Matt. The server and accoutrements all seem well up to spec.
Jul 7 '10 #10
matt753
91
For some reason it runs faster with the backend on another users computer rather than the server. Should it not be exactly the same?

I thought it should almost be as fast as running it off the local machine with a gigabit network and SSD.
Jul 7 '10 #11
NeoPa
32,556 Expert Mod 16PB
There's nothing to say it should be the same. there are a hundred and one reasons why two machines may differ. None of which are related directly to Access at all.

If asked to guess, I would normally expect a highly specced server to perform faster than a simple PC all else being equal, but there's no guarantee. I expect the all else being equal is your sticking point though. We're not going to get to the bottom of that on here though I'm afraid.
Jul 7 '10 #12
matt753
91
Yea kind of went off topic of the Dlookup but I suspect the overall slow speed must be the problem. A lot of other Dlookups run quite a bit faster in other places in the database.

I tried putting a couple posts up in this site before (about the slow network speed) but never really got any responses.
Jul 7 '10 #13
NeoPa
32,556 Expert Mod 16PB
Although it may be true that network issues are not best dealt with in an Access forum, that was not the reason I suggested finding a solution here would not work. The reason was that the nature of the problem, where there are so many potential issues and we would have to rely solely on the information you were able to provide (not likely to be very good as you have little understanding of the issue as you're needing the assistance in the first place), as well as needing to be pretty expert in all the areas, would mean that we would be unlikely to be able to help. The medium of a forum can be limited in some respects. Sometimes it can substitute for on-site help, but I doubt that would be the case here.
Jul 8 '10 #14
matt753
91
Thats understandable
Jul 9 '10 #15

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: Bill Dika | last post by:
Hi I am trying to calculate a running total of a calculated textbox (tbAtStandard) in GroupFooter1 for placement in a textbox (tbTotalAtStandard) on my report in Groupfooter0. The problem...
6
by: Norman Fritag | last post by:
Hi there, I am wonder why this dlookups return null, despite having a record in the table with a matching date? Am I missing something here??? Am i running in some limitations, that I am not...
3
by: DFS | last post by:
FYI, Using DLookup("ResultsField","Pass-thru query") consumes 2 SEQUENCE numbers each time it's called. Anyone know why?
1
by: mstery | last post by:
I have a report generated via an ID selection made in a dropdown on a form. The report filters by an on click event in a preview report button on the form. Everything in the report, including...
4
by: MLH | last post by:
I have tried using DLookUp in this manner... If DLookUp("","tblClients","='2021234567'") Then MsgBox "Found it!" End If I am wondering if that is a misuse of the DLookUp command? Type...
26
by: Bruno Jouhier [MVP] | last post by:
I'm currently experiencing a strange phenomenon: At my Office, Visual Studio takes a very long time to compile our solution (more than 1 minute for the first project). At home, Visual Studio...
0
by: Claire | last post by:
Ive written a threaded service application, realtime monitoring across a network, heavy processor usage. Probably will be running on a dedicated pc When it's running as an application it's nippy....
11
by: MLH | last post by:
DLookup("", "tblPreliminaryVINs", "=Forms!frmVINODO!SerialNum") is giving me a Type Mismatch error. That's confusing to me and I don't know how to circumvent it. The field in...
2
by: jonvan20 | last post by:
I have been having trouble with a simple Dlookup command that was Reccommended to me by a nice fellow named Vic, On the other hand I have statements like this that wont run they give me a run time...
0
by: Sebastian | last post by:
Hello I develop my applications in Access 2002. My development system is running Windows XP SP2 and I have Microsoft Office XP Developer. Microsoft Office XP is at SP3. I used Inno Setup (great...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.