Question Acess Agonizingly Slow

[ThinkingHealthcare]

A puzzling situation.

We have an Access program that contains a simple loop that does an ADODB open, edit, update then close.
This program runs successfully and with reasonable speeds (about 8 minutes for 5000 trips through the loop)
on all versions of MS Access 2000 and 2003 (regardless of service packs) and on all stand-alone versions of Windows XP.
It also runs with reasonable speed on most networks with Access running on Windows XP.

When we installed the program on Windows Server 2003 Enterprise Edition (Service Pack 2),
the program with the afore mentioned loop takes HOURS to run instead of minutes.
Looking at the Windows Task Manager, MS Access seems to "go to sleep" during each loop for a varying period of time.
During this period, MS Access displays "not responding" and the Task Manager shows CPU Usage at 0%.
Then after some time, it "wakes up" and then a second MS Access task shows up momentarily in the task list.
When this task goes away the next record in the loop starts the cycle over again.

The problem is an apparent interaction between Access (Access 2003 with office SP2) and the Operating System.
We have tried this on two machines on the same network.
The first machine was shared with other applications and so we originally thought that it was an interaction with these other apps.
When we moved to a new "blade" with nothing else on it but Windows Server 2003 Enterprise Edition and Microsoft Office 2003 SP2 and ran our program, we got the same result.

We are accessing this server via Remote Desktop and running the application (in one mde file) and its database (an mdb file) on the same machine.
We have also tried to run this with the application on a stand-alone machine and the database on a mapped drive with the same slow result.
We then took this to another customer where they run the program on their network of systems running Windows XP (SP3),
the program showed similar (albeit not as slow as with the first customer, roughly 1 hour instead of 8 minutes on a stand-alone) results.

Can someone fit the missing piece to the puzzle?

 

[gemma-the-husky]

i cant locate the knowledge base article

but there is a ms knowledge base article about access performance, and it specifically refers to a registry entry called "sharinglockviolation"

try finding that, see if it helps

 

[ByteMyzer]

I'm concerned about the statement:

We have an Access program that contains a simple loop that does an ADODB open, edit, update then close.
This program runs successfully and with reasonable speeds (about 8 minutes for 5000 trips through the loop)
on all versions of MS Access 2000 and 2003 (regardless of service packs) and on all stand-alone versions of Windows XP.

Does this mean that, within your program, the loop:
1) executes an ADODB open
2) executes an ADODB edit
3) executes an ADODB update
4) executes an ADODB close
... 5,000 times?

Or, does this mean that your program:
1) executes an ADODB open
2) executes a loop which:
-- a) executes an ADODB edit
-- b) executes an ADODB update
-- c) moves to the next record
3) executes an ADODB close when the end of the loop is reached?

Please clarify. This could explain why you are having performance issues.

 

[ThinkingHealthcare]

The loop:
1) executes an ADODB open
2) executes an ADODB edit
3) executes an ADODB update
4) executes an ADODB close
... 5,000 times?

 

[boblarson]

Why are you opening and closing the ADODB connection each time instead of doing it once and then leaving it open until completed?

 

[ThinkingHealthcare]

The reason is because the loop is based upon an ever changing query. More important is the fact that this blazes right along on a stand-alone machine with quite acceptable times. The need in my opinion is to search out what Access may be interacting with on Windows Server which may be causing the slowdown.

 

[boblarson]

The reason is because the loop is based upon an ever changing query.

You do not need to open and close the ADODB connection because of a different query. You do, need to change the recordset object, yes, but not the connection.

More important is the fact that this blazes right along on a stand-alone machine with quite acceptable times. The need in my opinion is to search out what Access may be interacting with on Windows Server which may be causing the slowdown.

Stop opening and closing the connection (what works on a stand alone machine can have detrimental results on a server). So try that FIRST and then we'll move on if that doesn't help.

 

[Pat Hartman]

8 minutes for 5,000 updates is not exactly blazing. It is quite likely that an update query would do the job in 30 seconds. Can you conver the process to be an update query that accepts arguments rather than an ADO update loop?