Sudden change in query performance

[Crusado]

Hi

I've built a macro that runs through about 12 queries one after the other, which I run every day. They basically bring in data from a data warehouse held on a Pervasive server through an ODBC link, then perform calculations based on other criteria in tables held within the access database. Up until about 10 days ago, the whole thing took about an hour to run. Then all of a sudden, this changed and it now takes around 5 hours. I haven't made any changes to the macro or any of the queries which it runs. I was wondering if anyone has any ideas what might have caused this (as it's now almost unusable). I've compacted the database to remove bloat, added more RAM, moved the Access DB onto my C drive instead of the server, stopped my antivirus program from looking at either my access DB or the datawarehouse where the data comes from, pestered the network guy to find out if they've changed anything to do with the server (he says no). Any ideas for other things to try would be great, as I'm on the verge of throwing my PC out of the window in case that helps!

Many Thanks

Andrew

 

[DJkarl]

A lot depends on what type of queries you're running and where, if they've added indexes to the Pervasive tables that could cause the slow down.

Depending on the number of records you're dealing with turning off transactions for udpate / append queries could save you significant time at the expense of some safety.

 

[The_Doc_Man]

DJkarl made some good suggestions.

Here's the rule of thumb. When you see changes in behavior, you have had changes in environment. You are now officially trekking through the jungle with gun and camera, looking for the source of your problem.

The ONLY way to find the problem is to find out what changed just before your performance went to squat.

I would look for changes in indexes as DJkarl suggested. I would also see if someone patched the server with some security updates. That'll get you every time.

A reconfiguration of the firewall, a change to routing, an addition of a proxy server, and any change in versions of your network stack would also have this effect.

I'm not familiar with "Pervasive" so I'll blue sky for a minute. There are issues in indexing called "bucket splitting" that have to do with what happens when your system fragments the indexes. You would indeed see really bad drops in performance that way. Compacting a database via ODBC might not be as effective as going to the actual server and doing some things from it. Such as using the Pervasive software to reorganize things with whatever is its moral equivalent to a compact and repair.

As a crazy thought, find the table you hit most often. Drop all of its keys, then add them back again, one at a time in decreasing order of importance until you find one that makes the difference - if any. But of course, dropping and re-adding keys causes the indexes to be rebuilt from scratch, which might fix the problem anyway if all that was wrong was the indexes needed to be rebuilt. 'tain't unheard of that you would need to do so. Happens all the time in our big plug-ugly ORACLE beastie.

 

[Crusado]

Hi guys, thanks for your comments. Just to clarify, all my Access tables have indexes (but I thought this was supposed to make the DB more stable, not slow it down?). The Pervasive tables have primary keys defined, but no indexing. I only reference the data held in the pervasive tables, i dont actually append or update anything in there. All calcs and results are held in my Access DB. I regularly compact and repair my Access DB, but not the pervasive tables (never worked with Pervasive before, so not sure what the equivalent is, but i'll suggest it to the Systems Analyst who owns that DB). The majority of my queries are make table queries, where the next query references the table created previously. I initially created it this way so I could check the results at each stage - does this slow the process down at all? Should I change them so that they just reference the previous query?

Not sure about the Transactions that DJKarl is talking about, I'll have a search on the internet in a minute.

Stopping the virus checker looking at my DB or the datawarehouse seems to have helped - the macro ran in 2.5 hrs last night, rather than the 5 that it had jumped up to. I'm going to forward your suggestions on to our network and datawarehouse guys to see if they can find anything else that helps. Thanks for your help so far.

Cheers

Andrew