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(s) used by Access to execute the query. With this information, it is possible to perform query optimizations, determine which fields require indexing… to optimize your queries. In some cases, this can have significant impact on database performance well worth the time invested!
I’m not going to rewrite the article, if the subject interests you, then simply look at the source:
A Few Extra Precisions
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
Once you locate the base registry key, create a new key entitled Debug, and create a String value entitled JETSHOWPLAN with a value of ON. Finally, restart Access and the query plans automatically will get created in your Documents folder.
Office365 Click-To-Run Show Plan Registry Key
With the more recent Office365 Click-to-run versions of Access the registry key has changed. For my installation, for Access 2016 x32 for instance, I ended up using:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\REGISTRY\MACHINE\Software\Wow6432Node\Microsoft\Office\16.0\Access Connectivity Engine\Engines
I then created a new key entitled Debug, then created a String value entitled JETSHOWPLAN with a value of ON. Then, I restarted Access and the query plans automatically got created in my Documents folder whenever I ran a query.
How did I know where to create this key? I didn’t and documentation is impossible to find (what’s new). I simply used the RegEdit’s Find tool to locate the term Access Connectivity Engine and was able to locate one with a key entitled Engines and then tried my luck by adding the Debug key… and it worked. So even if the exact key does not exist for you, the above steps should enable you to find the proper key for your setup, but do be careful messing around with the registry!
So just be aware that the CTR versions use another registry key.