UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Jet Showplan Manager, Any Version    
 
   
isladogs
post Dec 18 2018, 03:59 PM
Post#1


UtterAccess VIP
Posts: 1,561
Joined: 4-June 18
From: Somerset, UK


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 effiicient queries

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

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 part of the application is identical to the Access/Windows/Office365 Checker utility also published here

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:

Attached File  JetShowPlanManager1.PNG ( 19.9K )Number of downloads: 12


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:
CODE
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:
Attached File  JetShowPlanManager5.PNG ( 40.46K )Number of downloads: 10


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 switched ON
It is therefore strongly recommended that JETSHOWPLAN is switched OFF when it isn’t required for optimising query execution.

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 can select the default folder or 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
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 UtterAccess 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

Attached File  JetShowPlanManager2010_v2.4.zip ( 491.63K )Number of downloads: 31

Attached File  JetShowPlanManager2007_v2.4.zip ( 491.14K )Number of downloads: 5

Attached File  JetShowPlanManagerHelp.zip ( 399.44K )Number of downloads: 6

--------------------
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    24th August 2019 - 09:51 AM