Provided by Douglas, December 2007.
The same query runs much slower under Vista than previous versions of Windows.
Throw a large and complex query - say hitting 8 large tables, and 6 nested queries deep - at Windows XP on a Dual Core stand-alone PC. Win XP CPU utilization runs one core flat out (50% overall CPU utilization) and stays around 49% to 51% for as long as the query takes to run, say ten seconds.
But under Vista, (using the same dual-core hardware) the CPU utilization hits maybe 20% for one second, then falls off to about one or two percent, and stays down near zero, essentially idling. The query is still running, and it does eventually deliver results in a data sheet - after about 40 seconds, or 4 times slower in Vista than XP.
This 2% CPU utilization seems consistent across Access 2000, 2002, and 2003, with various service packs.
The COOL thing about Vista for MS Access developers, is that this literally negligible CPU utilization means you can massively load Vista with background queries to pre-calculate into temporary tables, without appreciably slowing down the foreground user sessions.
To do this on a spare core of a new cheap Quad Core CPU, auto-load those batch queries under a different user name, and output their results into temporary tables. The user forms in the Front End simply attach to those tables, for split instant results access, no matter how long the background queries took to run!
The BAD thing about Vista for MS Access developers, is that Vista would rather sit there idling along, while refusing to let MS Access Queries to seriously use the hardware.
So unless you are willing to re-write your large MS Access apps as I've suggested above, Vista will actually run them much slower than Windows XP!
While this type of rewrite will eventually be inevitable to efficiently leverage the future Quad, 8 (and more) core CPU's it would be nice if MS would at least allow it's own database product to actually USE a humble core.
For other general suggestions on optimizing Access across various versions and operating systems, see the Microsoft Access Performance FAQ by Tony Toews.
|Home||Index of tips||Top|