JET ShowPlan Manager

Version 2.7           Last Updated 26 Nov 2021               Approx 0.8 MB

Section Links:       Introduction       Using the Application       How it works       Acknowledgments       Version History       Downloads       Further Reading

1.     Introduction                                                                                         Return To Top

The Jet ShowPlan feature is used to view the execution plan of Access queries and SQL statements.

The query execution plan is a set of instructions to the database engine that tell it how to execute a query.

As a simple example, consider a query that retrieves all customers located in the UK.
One way to do this would be to examine every record and select the ones where the Country field equals UK. But if there's an index on that field, a more efficient way to perform the same query would probably be to examine the index, and then jump straight to the records from UK.

The following information is taken from an excellent article Use Jet Showplan to write more efficient queries written by Susan Haskins back in 2003:

Jet creates this plan each time you compile the query – for example the first time you run it, when you save a change to the query, or when you compact the database. Jet uses this plan behind the scenes to determine the quickest way to go about executing the query. Once the plan exists, Jet simply refers to the plan to run the query instead of re-evaluating the query each time you run it. One easy way to optimize a query is to compact the database if you make several changes to the data or add a lot of new data. Doing so will force a re-evaluation of the query plan. What works best for ten rows might not be the best plan for 10,000 records. The plan contains information on the following components:
•  WHERE or HAVING clauses
•  ORDER BY clause
•  Joins
•  Indexes
•  Table stats

Additional information bringing this article up to date can be found in another article Show Plan – Run Faster elsewhere on this website

In order to use the JET ShowPlan feature, you first need to setup the feature in the registry. To do this requires knowledge of the correct locations for several registry keys, some of which are version dependant.

The JET ShowPlan Manager application is designed to make this process as simple as possible



2.    Using the application                                                                       Return To Top

In order to setup the JET ShowPlan feature, Access MUST be run as an administrator.

To do so, right click on the Access shortcut in the start menu or desktop and click Run As Administrator.
If this option isn’t available (e.g. Access 2010), hold the shift key down as you right click the shortcut

Access versions prior to 2007 cannot be run as an administrator so no MDB version is available for this utility

When the application first opens, it will collect information about the version of Windows and Access being used.
It will also determine whether these are 32-bit or 64-bit and whether a copy of Office 365 is installed.

This information is needed to determine the correct registry path needed for the JETSHOWPLAN string value

This process will take a few seconds and the result will look similar to this



If the application was not being run as an administrator, parts of the screen will be disabled.



If so, close and reopen using the run as administrator option.

In the examples shown, a 32-bit version of Access 365 is being run on 32-bit Windows.

The correct registry key in this case for the JETSHOWPLAN string is:

HKLM\SOFTWARE\Microsoft\Office\ClickToRun\REGISTRY\MACHINE\Software\Microsoft\Office\16.0\Access Connectivity Engine\Engines\Debug\JETSHOWPLAN

The registry path depends on the Windows ‘bit-ness’, the Access version and bit-ness and whether or not it is an Access 365 installation. For further details on the various paths, see the attached PDF file

The registry key is NOT created automatically when Access is installed.
Click the Create JETSHOWPLAN Key button to do so.

After a couple of seconds, the screen will be updated with the JETSHOWPLAN key value is set to OFF
The button caption will change to Set JETSHOWPLAN = ON



Click the button again to enable the feature as shown below



Whilst the JETSHOWPLAN value = ON, the execution plan of every query or SQL statement used by this version of Access will be saved to a plain text file showplan.out, usually in the default database directory.

An example showplan file is shown in the next screenshot



The same file is used each time so it can over time become very large indeed.

In addition, the time needed to complete queries increases by around 14% when the feature is ON
It is therefore strongly recommended that the JETSHOWPLAN feature is switched OFF when it isn’t required.

To view an example showplan.out file, click the View Example ShowPlan button.

This will check the default database directory in the registry, associate .out files with Notepad, run a simple query qryComputerInfo and then open the showplan.out file in Notepad

When Access is installed, the default database directory is usually set as C:\Users\UserName\Documents
OR if you are using a Microsoft account it may be set as C:\Users\UserName\OneDrive\Documents
where the UserName part will usually be the user's Windows login name (or an abbreviated version)


NOTE:
It is STRONGLY recommended that OneDrive is NOT used as the default directory.
As that requires an online connection, any interruptions to that connection can cause corruption leading to loss of data and/or an unusable database.

Unfortunately, the default database directory is only stored in the registry if it is changed!

In order to view the show plan file, the application will next add the default database directory key & value to the registry if it doesn’t already exist.
If so, a message box similar to this will appear



Clicking YES, will set the default to the default My Documents area e.g. C:\Users\YourName\Documents.

If that location isn’t correct, click NO instead.
A Browse folder dialog will appear :



Browse to and select the folder required then click OK.

The new default folder will be implemented next time Access is opened.
If you have changed the default folder, this will affect all new databases created from now on.

Restart the application – remember to use Run As Administrator

Click the View Example ShowPlan button again.
This will run a simple query and open the showplan.out file which was saved in your default database directory.


NOTE:
If you still have OneDrive as your default directory, the showplan.out file will be created and may flash briefly but then close. Yet another good reason NOT to use this online folder area!

You can now use the JET ShowPlan feature to assist with optimising queries and SQL statements.
Do remember to switch this feature OFF in the registry when NOT required for query optimisation.



3.     How the application works                                                           Return To Top

Details of the functions used to detect the Windows & Access versions and 'bitnesses' and to check for Office 365 are included in the attached JET ShowPlan Manager Help PDF file.

These functions are also explained in the description for the Access/Windows/Office365 Version Checker utility

Checking whether Office 365 is installed is quite complex but is necessary to to use the Jet ShowPlan feature.
As Microsoft uses the same version numbers for both retail Office and the Office 365 subscription model, the approach used is to check the registry.

It is partly because the CheckAccess365 function needs to read from the HKEY_LOCAL_MACHINE (HKLM) registry hive that the application MUST be run as an administrator.

The process is complicated by the use of Wow6432Node registry key for 32-bit Access in 64-bit Windows

As a further complication, if a retail version of Office 2013/2016/2019/2021 is installed but the user enters their Microsoft account information either during installation or at a later time, this triggers the ClickToRun registry structure to be created!

In other words, it is then treated as Office 365 even though it is still a retail product.
However, the software is not updated with new features as is the case with a true Office 365 product

Why Microsoft decided to make this so very difficult is very hard to understand!

Details of the registry paths involved in setting up and using this are explained in some detail in the attached PDF file as the same ideas could, in principle, be used to modify other features of Access using appropriate registry keys.



4.     Acknowledgments                                                                       Return To Top

I am extremely grateful to Utter Access forum member Jeff Holm for repeatedly testing different versions of this application in mixed 32/64 bit systems. Also for making several valuable suggestions and providing code snippets used for solving issues with registry keys using the Wow6432Node without having to deal with the complexities of registry redirection.

Thanks also to Access team member, Shane Groff, for clarifying details about the version numbering in Access 2016/2019/2021/365.

Further thanks are due to Tom Stiphout, Dev Ashish and Daniel Pineault for various items of standard code used in this application.

I would be grateful for any feedback related to this application.
To do so, please send an email or use the contact form below.



5.     Version History                                                                             Return To Top

        13/12/2019 - Version 2.4 - Initial release
        06/03/2019 - Version 2.5 - Minor correction to API in modSysInfo - no new functionality
        26/11/2021 - Version 2.7 - Updated version info for 2021/365 - no new functionality
        24/08/2022 - Version 2.8 - Now also available as an Access add-in - see JET ShowPlan Manager Add-In


6.     Downloads                                                                                     Return To Top

        Click to download:

        JET Show Plan Manager 2010 (zipped - for Access 2010 or above)

        JET Show Plan Manager 2007 (zipped - for Access 2007)

        JET Show Plan Help (PDF file)

        NOTE:
        The JET Show Plan Manager is NOT available for Access 2003 or earlier as it requires Access to
        be run as an administrator



7.    Further Reading                                                                             Return To Top

        Use Microsoft Jet's ShowPlan to write more efficient queries
        The original article from 2003 by Susan Haskins

        MS Access – JET SHOWPLAN
        A more recent article from 2021 by Daniel Pineault

        JetShowPlan: A Primer
        Another excellent article from 2021 by Mike Wolfe
        By far the most thorough reference I have seen on the topic



Colin Riddington           Mendip Data Systems                 Last Updated 26 Nov 2021