Mendip Data Systems

Database applications for businesses and schools

LogoTransparent

Example Apps for Businesses, Schools & Personal Use

Version 2.5      Updated 06/03/2019               Approx 0.8 MB

 

1. Introduction

 

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

 

JET ShowPlan Manager

Click any image to view a larger version ...

Screenshots

Return to Example Databases Page JetShowPlanManager1 JetShowPlanManager2 JetShowPlanManager3 JetShowPlanManager4 JetShowPlanManager5 JetShowPlanManager6 JetShowPlanManager7

2. Using the application

 

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 Figure 1 on the right

 

 

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

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. See Figure 3

 

Click the button again to enable the feature as in Figure 4

 

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 in the default database directory.

 

 

 

 

An example showplan file is shown in Figure 5

 

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 JETSHOWPLAN 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 Figure 6 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 as in Figure 7:

 

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. 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

 

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 the Wow6432Node of the registry for 32-bit Access in 64-bit Windows

 

As a further complication, if a retail version of Office 2013/2016/2019 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

 

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.

 

Also, 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

 

13/12/2019 - Version 2.4 - Initial release

 

06/03/2019 - Version 2.5 - Minor correction to API in modSysInfo - no new functionality

Figure 1

Figure 2

Figure 4

Figure 3

Figure 5

Figure 6

Figure 7

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) - this is included with each of the above items

 

NOTE: The JET Show Plan Manager is NOT available for Access 2003 or earlier

* Required

To provide feedback on this example database, please enter your name, e-mail address and company (optional) below, add your comment and press the submit button. Thanks