I developed an Excel app that pulls data from an Access database that I also developed. The Access database and it's related app have been in use for about 7 years (originally developed in Office 2000). When I run it on my slow old notebook it takes about 10 minutes to successfully complete.
Then I took it to a client that has a Dell duo core server (that I installed) running Win2003 that is used only as a file server. They have about 10 XP Pro workstations talking to the server. It easily handles the load. When I run the Excel - Access app from a workstation it runs incredibly slowly. I estimate that it would take 27+ hours to complete.
So then I installed the app on the server and ran it locally - amazingly no improvement. I reconfigured the server to give higher priority to foreground tasks, still no improvement. When I look at TaskMgr it shows the cpu at about 1% - 2%. Excel or Access get a little bit of run time (fractions of a second) and then the cpu is idle for several seconds. When I run the app on my laptop the CPU is maxed for the whole 10 minutes.
The Excel app is accessing a live DB, but I set the locking to essentially ReadOnly. So, I seriously doubt that it is stalling on locks. Also, it is accessing about 35,000 records from the DB. The other users would at most have 4 - 5 in use. There is one query that gets a set of records and then a loop in VB in Excel to process each record. I put in a MsgBox to fire every 100 records processed. That's how I came up with the 27 hour estimate.
Start Free Trial
