isladogs
MVP / VIP
- Local time
- Today, 19:40
- Joined
- Jan 14, 2017
- Messages
- 18,513
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 by Susan Haskins written in 2003:
Use JET Showplan to write more efficient queries/
Additional information bringing this article up to date is contained in another article Show Plan – Run Faster on my 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
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 down the shift key as you right click the shortcut
NOTE:
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 section of the utility is identical to the Access/Windows/Office365 Checker utility in the code repository
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 the screenshot below:
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 example shown above, 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 has changed to Set JETSHOWPLAN = ON. Click the button again to enable the feature.
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.
For example:
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
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. You will have the opportunity to select the required folder.
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
The JET ShowPlan Manager reads from & writes to various sections of the registry in the following hives:
HKEY_CLASSES_ROOT (HKCR)
HKEY_LOCAL_MACHINE (HKLM)
HKEY_CURRENT_USER (HKCU)
Like any registry changes, you should make sure you understand what changes are being made.
Please read the attached PDF file in detail before running this application
4. Acknowledgements
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
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 by Susan Haskins written in 2003:
Use JET Showplan to write more efficient queries/
Additional information bringing this article up to date is contained in another article Show Plan – Run Faster on my 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
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 down the shift key as you right click the shortcut
NOTE:
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 section of the utility is identical to the Access/Windows/Office365 Checker utility in the code repository
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 the screenshot below:
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 example shown above, 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 has changed to Set JETSHOWPLAN = ON. Click the button again to enable the feature.
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.
For example:
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
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. You will have the opportunity to select the required folder.
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
The JET ShowPlan Manager reads from & writes to various sections of the registry in the following hives:
HKEY_CLASSES_ROOT (HKCR)
HKEY_LOCAL_MACHINE (HKLM)
HKEY_CURRENT_USER (HKCU)
Like any registry changes, you should make sure you understand what changes are being made.
Please read the attached PDF file in detail before running this application
4. Acknowledgements
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
Attachments
Last edited:
