Dlookup running very slowly

[matt753]

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?

[NeoPa]

Generally, like any table access, searching via a non-indexed field would be my best guess.

[matt753]

Changed them to indexed but still the same speed

[NeoPa]

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?

[missinglinq]

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)>

[matt753]

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.

[matt753]

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(DlookupSt atement,2)

[NeoPa]

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.

[matt753]

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.

[NeoPa]

I can see nothing there that would account for any noticeable lag Matt. The server and accoutrements all seem well up to spec.

[matt753]

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.

[NeoPa]

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.

[matt753]

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.

[NeoPa]

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.

[matt753]

Thats understandable