Slow transaction on terminal server

[DataMiner]

I have a couple of update queries nested within a transaction. These have been in place for several years, running every day. Normally takes about 3 minutes to run. Normally run on terminal server using Citrix.

About a week ago, this stuff started taking anywhere from 30 minutes to more than an hour (not sure how much longer, as I killed it after that), instead of the normal 3 minutes. If I run it directly from my desktop instead, I'm back down to the normal 3 minutes.

Of course, the IT department insists that they haven't changed anything on the server.

I've tried rebuilding the database, compact and repair, running on different servers, different desktops, with different user login, etc, etc. and the results are the same. Run from desktop: fast. Run from server: SLOOOWWWW.

Any theories on what's causing this and what I can do (or can ask the IT guys to do) to get back to normal speed???

 

[RuralGuy]

Have you had IT run any diagnostics on your network? Maybe a node or hub is failing.

 

[DataMiner]

IT says everything in the network looks normal. Plus, I'm not seeing a problem with other applications.

Last night, out of other ideas, I archived a bunch of older data from the tables that the queries were accessing. This morning, everything seems to have run normally. So, my theory is that maybe there is some limit to the amount of RAM that the server will let me use....? And maybe my tables had grown to the point where involving that many records in a transaction was exceeding that limit??? Does that sound plausible? I've spent most of this week dealing with this headache and don't want it to happen again.

 

[RuralGuy]

Is it an Access BackEnd? Do you Compact and Repair it on occasion?

 

[DataMiner]

It's an access backend, and gets compacted and repaired every night.

 

[RuralGuy]

Well that pretty much exhausts my bag of tricks. Have you examined your queries to make sure they limit the returned records as much as possible? Do you have indexes on the appropriate fields? Here are some suggestions from MVP Tony Toews.