Inexplicable Slowness in Executing Queries (1 Viewer)

RogerCooper

Registered User
Joined
Jul 30, 2014
Messages
166
I have a module that executes a long series of queries listed in table. These queries spot missing & erroneous data in our ERP. I run automatically at midnight & noon.

Recently the same queries run at noon started taking more than x10 longer to run than the ones executed at midnight. I am generally working in the database at noon (using a different computer), but that has not caused problems before. In addition, if I run the queries by opening up a new instance of Access with same database, they run fine, even while the database is executing slowly. I compact the database every morning.

Some things I have tried without success"

* Shutting down everything and restarting (I do that every weekend)

* Create a new database and copying everything into it

Does anyone have any ideas on how to fix this?
 
Last edited:

The_Doc_Man

Happy Retired Curmudgeon
Joined
Feb 28, 2001
Messages
15,596
To quote Elmer Fudd, "To make hassenpfeffer, first you must catch the rabbit."

It appears that you have a transient hot-spot and to fix it you have to find it. We can only suggest where to look, but to do that, we need to know the physical layout of your network. More specifically, we need to know a bit about how the queries talk to the ERP data. For instance, how many machines are involved and how are they connected?

The trick here will be to determine WHY you have this problem, which might require you to be logged in on the machine or machines experiencing the problem. You might have to use the Windows Resource Monitor at noon and midnight to check for things like:

* Network usage level on each involved system
* I/O rate on each involved system
* CPU load on each involved system
* Amount of free memory on each involved system
* Page Fault rate on each system

You are looking for sources of contention or competition. Therefore, you might wish to take readings before, during, and after the problematic procedures to see if you are starting from the same baseline of usage at noon and midnight. I.e. if you have no load just before midnight, you would expect the optimum performance for the extant queries. But if you start with a half-saturated CPU, you might have to consider what else is trying to steal some cycles.
 

gemma-the-husky

Super Moderator
Staff member
Joined
Sep 12, 2006
Messages
13,873
i would imagine you have lots of users at noon, but not at midnight.

assuming you have a split database. then you might be needing to transfer a lot of data from server to pc, which will take time is other users are also on the network.

How long are the processes taking? if we are talking about seconds, maybe it's bearable. If it's minutes, maybe you can redesign the process
 

RogerCooper

Registered User
Joined
Jul 30, 2014
Messages
166
The queries are running against Oracle database for the most part. But the excessive runtime only occurs when I run them through the VBA module at noon. If I run the same queries directly in the same database, at the same time, they run at normal speed.

The queries can't be redesigned to run faster. Under normal circumstances, they take only a few seconds to run and checking for everything takes less than a hour.

I may try splitting the database, but the database runs normally overnight (and run normally until 2 weeks ago) and most queries involve the Oracle ERP, not Access tables, so I would not expect splitting the database to make a difference.
 

gemma-the-husky

Super Moderator
Staff member
Joined
Sep 12, 2006
Messages
13,873
As I say, if you run the queries on the oracle machine, they will probably run in that database.

if you use access, the data will probably be fetched over the network to your PC for processing. That may be the reason for the performance difference. Are you running the access database on a local PC, or on a server. That may add another layer of data moving.
 

The_Doc_Man

Happy Retired Curmudgeon
Joined
Feb 28, 2001
Messages
15,596
(and run normally until 2 weeks ago)
And the question that IMMEDIATELY pops up is "What changed 2 weeks ago?"

But the excessive runtime only occurs when I run them through the VBA module at noon. If I run the same queries directly in the same database, at the same time, they run at normal speed.
Here we need precision of description for troubleshooting. If we are talking about "same database" do you mean that the queries work fine if run inside the ORACLE DB? And when the queries are run from Access they run slower? That begs the question, "What happened to the network configuration 2 weeks ago?" Because if I read this using conventional inferences, the problem occurs when you run the query over the network.

If the same queries are available within ORACLE, is there a way you can trigger them? OR can you convert the queries run via Access into PASSTHRU queries so that they run locally under ORACLE no matter what? Because if they are NOT currently running that way then I think you are STILL paging through all the ORACLE tables to let Access pick what it wants, which is far more work than if ORACLE runs the query and just returns the results. (Other forum members, check me on this one. I've not worked with an ORACLE BE in Access, only on my larger FE OpenVMS server that was using another product.)

If this isn't a PASSTHRU query case or a triggered query case, then the final question is, "Did the tables suddenly get loaded with extra data 2 weeks ago to make the bigger?"

Because the symptoms are verging towards network issues, which will pop up:

a) if the network changed (more complex internal routing including security changes)
b) if the amount of data to be scanned changed (larger)
c) if the load on either the ORACLE server or the Access host changed (higher)

You said this changed 2 weeks ago. Did ANY of the above occur 2 weeks ago. I'm asking it this way because computers are highly consistent in behavior. They have this strength called "predictability." They do the same thing the same way EVERY TIME - until the conditions change. So if results changed, conditions have to have changed. To "catch the rabbit" you have to find what changed.
 

RogerCooper

Registered User
Joined
Jul 30, 2014
Messages
166
And the question that IMMEDIATELY pops up is "What changed 2 weeks ago?"
Here we need precision of description for troubleshooting. If we are talking about "same database" do you mean that the queries work fine if run inside the ORACLE DB? And when the queries are run from Access they run slower? That begs the question, "What happened to the network configuration 2 weeks ago?" Because if I read this using conventional inferences, the problem occurs when you run the query over the network.
Nothing happened in the databases 2 weeks ago. This may be a network issue. Resource monitor shows CPU usage at 3-6%. Only about 25% of the memory being used. Network speeds seems to be typically 1-2mbps, but I saw it spike to 16mbps. Utilization is running from 0% to 11%.

I will try rewriting some queries as pass through queries and see if that helps.
 

The_Doc_Man

Happy Retired Curmudgeon
Joined
Feb 28, 2001
Messages
15,596
OK, at that level of CPU, it is NOT a CPU issue. If using 25% of memory, it is not a memory issue. For standard Ethernet, 11% is high if sustained but normal if simply a peak. You can go up to about 30% before you have significant probabilities of collisions - though with the case of non-PASSTHRU queries, the number of packets required for data transfer still "widens the window" of opportunity for a higher collision rate. By making the queries run locally and just returning the result set, you "narrow the window" by reducing the number of packets needed to return those results.

I noted also that Dave (Gemma) was asking the same questions as I was. The actual location where the query is run makes a HUGE difference in the level of traffic required to support the operation. Where possible, you ALWAYS ALWAYS ALWAYS want to minimize network traffic even if it complicates the programming aspect of what you are doing.
 

RogerCooper

Registered User
Joined
Jul 30, 2014
Messages
166
I determined that the problem was poorly behaving program on the server that I don't even use. So the problem was completely external to Access. Thanks to everyone for your help.
 

The_Doc_Man

Happy Retired Curmudgeon
Joined
Feb 28, 2001
Messages
15,596
Ah, saturating the SQL host? But you should also have seen that when running the query locally.
 

RogerCooper

Registered User
Joined
Jul 30, 2014
Messages
166
Everything is on the network, even the Access database is sharing a server with the misbehaving program. We can't get rid of the misbehaving program for now, but over the weekend the system administrator will move all the Access files to a different server.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom