I came across an article about optimizing queries using a hidden JET tool call SHOWPLAN which basically generates a showplan.out file (which is merely a text file) which elaborates the method used by Access to execute the query. With this information it is possible to perform optimizations, determine which fields require indexing…
I'm not going to rewrite the article, if the subject interests you, then simply look at the source article.
That said, I did want to try and add a little more information for anyone trying to get Showplan to work on Windows 7 (possibly Windows Vista – untested). To be able to use ShowPlan one must create a registry entry to enable it. In the original atricle, they indicate that the base registry key is found at:
\\HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\JET\4.0\Engines
this may be the case for Windows XP, but if you are using Windows 7 (and I suspect Vista as well) you will not find this key. Instead look for:
\\HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines
The above mentioned article appears to have been moved to: Use Microsoft Jet's ShowPlan to write more efficient queries.
