Looking to take your VBA skills further?...

Discover twinBASIC — a powerful new development platform that expands on VBA and VB6 with advanced features, modern tools, and enhanced compatibility. Perfect for those ready to elevate their projects or transition from VBA, twinBASIC lets you build on what you already know and take your applications to the next level!

Try out twinBASIC Community Edition - it's free!

Slow queries in Microsoft Vista

        1 votes: *****     2,599 views      No comments
by Allen Browne, 18 January 2008    (for Access 97+)

Microsoft Access Tips for Serious Users

Provided by Douglas, December 2007


CPU utilization under Windows Vista

The same query runs much slower under Vista than previous versions of Windows.

Issue

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.

Suggestion

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.

Other Performance Issues

For other general suggestions on optimizing Access across various versions and operating systems, see the Microsoft Access Performance FAQ by Tony Toews.


HomeIndex of tipsTop

Rate this article:  Your rating: PoorYour rating: Not so goodYour rating: AverageYour rating: GoodYour rating: Excellent


This is a cached tutorial, reproduced with permission.

Have your say - comment on this article.

What did you think of 'Slow queries in Microsoft Vista'?

No comments yet.

Why not be the first to comment on this article?!

Have your say...

Name
E-mail (e-mail address will be kept private)
Comments


Comments require approval before being displayed on this page (allow 24 hours).