First Published 5 Dec 2018 Last Updated 18 Aug 2023 Difficulty level : Advanced
Section Links:
Introduction
JET ShowPlan
Create Registry Key
Use JET ShowPlan
JET ShowPlan Manager
Further Reading
Feedback
1. Introduction
Return To Top
Over the past few years I have done a series of Speed Comparison Tests to compare the efficiency of different approaches to the same tasks.
Currently there are 15 sets of speed tests available. More may be added as time permits:
a) Handling nulls: Trim / Len / Nz
b) CurrentDB vs DBEngine(0)(0)
c) DoEvents vs DBIdle.RefreshCache
d) HAVING vs WHERE
e) Conditional Updates: If/ElseIf/End If vs Select Case vs Nested IIf vs Switch vs Lookup Table
f) Query vs SQL vs QueryDef
g) Check Record Exists
h) Optimise Queries
i) SELECT DISTINCT vs GROUP BY
j) Regex Or Not
k) $ Or No $
l) Finding Unmatched Records
m) WHERE OR vs WHERE IN
n) Loop vs Recursion
o) Grouped Aggregate Queries
Several of these speed tests resulted in unexpected outcomes that went against long held beliefs by several experienced developers including myself.
During development work, all of us will want to ensure that tasks are completed as quickly as possible.
However, it is not always obvious that the current design is inefficient until performance slows to a crawl and clients start to complain.
2. JET ShowPlan
Return To Top
Where queries or VBA SQL statements are concerned, help is available by making use of the ShowPlan feature which is available with both the JET database engine (up to A2003) and the newer ACE engine (A2007 onwards). The JET ShowPlan option prints the query's plan to a text file so you can review and, if possible, improve the design.
For a detailed explanation of the ShowPlan feature, I strongly recommend reading this article which was written back in 2003 by Susan Haskins:
Use Microsoft Jet's ShowPlan to write more efficient queries
UPDATE 18 Aug 2023:
The original article is no longer available so I have updated the above link to an archived version on the Wayback Machine website.
Alternatively, click here to view a PDF of the original article.
The following quote is taken from that article:
About query optimization
Regardless of how you state your query, Jet will run that query using the most efficient plan. In fact, if you use the query design grid, Access sometimes rearranges criteria expressions and references when you switch from Datasheet View back to the query design window. That's Jet's query optimization at work. Access rearranges things because your way isn't the most efficient way to run the query. You don't need to worry about these changes, because your query will return exactly the same results, it will just do so quicker.
Behind the scenes, a query has another version. The query plan is a set of instructions to the Jet engine that tell it how to execute a query. For a simple example, consider a query that retrieves all customers located in Alaska. One way to do this would be to examine every record and pull out the ones where the State field equals Alaska. 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 Alaska.
Jet creates this plan each time you compile the query (e.g., 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
About ShowPlan
The ShowPlan option was added to Jet 3.0 and produces a text file that contains the query's plan. (ShowPlan doesn't support subqueries).
You must enable it by adding a Debug key to the registry like so:
\\HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\JET\4.0\Engines\Debug
Under the new Debug key, add a string data type named JETSHOWPLAN (you must use all uppercase letters). Then, add the key value ON to enable the feature. If Access has been running in the background, you must close it and relaunch it for the function to work.
When ShowPlan is enabled, Jet creates a text file named SHOWPLAN.OUT (which might end up in your My Documents folder or the current default folder, depending on the version of Jet you're using) every time Jet compiles a query. You can then view this text file for clues to how Jet is running your queries. We recommend that you disable this feature by changing the key's value to OFF unless you're specifically using it. Jet appends the plan to an existing file and eventually, the process actually slows things down. Turn on the feature only when you need to review a specific query plan. Open the database, run the query, and then disable the feature.
Although Susan Haskins article was written back in 2003, when I wrote this article in 2019, there was very little information available elsewhere online.
However, new information has since been published - see Further Reading at the end of this article.
The purpose of this article is to provide updated information for use with current versions of Access and to relate the results of using the JET ShowPlan feature to specific examples used in my speed tests.
First you need to setup the feature in the registry
3. Create the registry key
Return To Top
WARNING: Editing the registry can have unforeseen consequences if done incorrectly.
Always create a backup of the registry before making changes . . . just in case anything goes wrong.
Click the Windows Start button, choose Run and type regedit. Click OK to open the registry editor
Use the treeview on the left to locate the appropriate key.
This will be different dependant on your version of Access. For example:
Access 2000/2002/2003:
HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\JET\4.0\Engines
Access 2007:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines
Access 2010:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines
Access 2013:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\15.0\Access Connectivity Engine\Engines
Access 2016/2019/2021/365:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\16.0\Access Connectivity Engine\Engines
For 32-bit Access in 64-bit Windows, a different path is required.
For example, in Access 2016 onwards you should use:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\REGISTRY\MACHINE\Software\Wow6432Node\Microsoft\Office\16.0\Access Connectivity Engine\Engines
Once you have located the Engines key, double click to select it, right click & select New … Key and name the key Debug
Now select the Debug key, right click and select New … String Value.
Enter JETSHOWPLAN (all in uppercase letters) as the value name.
Then, right-click this item, choose Modify, and enter ON in the Value Data control, as shown
Click OK to return to the editor and the completed key is as shown below.
The ShowPlan feature is now enabled and the registry editor can now be closed.
NOTE:
If Access is open, you need to close and reopen it to start using the JET ShowPlan feature.
4. Using JET ShowPlan
Return To Top
From now on, ShowPlan will run EACH time you run a query by ANY method
A text file showplan.out will be created in your default database folder e.g. My Documents.
The file can be opened in any text editor such as Notepad
Each query will add additional text to that file in turn.
For example, two very simple queries have been run in turn. In this case, neither query uses indexing:
For comparison, I have done simplified versions of the queries used for the HAVING vs WHERE speed tests.
For this purpose, records are only appended for birth dates commencing 01/01:
HAVING:
INSERT INTO tblData ( RecordCount, Gender, BirthDateMonth )
SELECT Count(tblPatients.PatientID) AS RecordCount, tblPatients.Gender, Left([DOB],5) AS BirthDateMonth
FROM tblPatients
GROUP BY tblPatients.Gender, Left([DOB],5), Month([DOB]), Day([DOB])
HAVING (((Month([DOB]))= 1) AND ((Day([DOB]))= 1));
WHERE:
INSERT INTO tblData ( RecordCount, Gender, BirthDateMonth )
SELECT Count(tblPatients.PatientID) AS RecordCount, tblPatients.Gender, Left([DOB],5) AS BirthDateMonth
FROM tblPatients
WHERE (((Month([DOB]))= 1 ) AND ((Day([DOB]))= 1 ))
GROUP BY tblPatients.Gender, Left([DOB],5), Month([DOB]), Day([DOB]);
The query execution part of both showplan.out files are shown below:
HAVING
|
WHERE
|
The query execution plans are identical!
The HAVING file shown above is the complete file
The WHERE file contained additional information related to the index used on the DOB field
In the full tests the code looped repeatedly through every combination of birth dates and months.
Each 'pass' created additional text in the showplan.out file resulting in a very long file
The HAVING/WHERE times in the full tests were almost identical.
The WHERE test was very slightly faster presumably due to the explicit use of indexing
The INSERT test used a different approach which was far more efficient and therefore MUCH faster
Attached are the showplan.out files for each of the 3 tests for anyone who is interested
Click to download: showplan.zip
IMPORTANT:
Whilst the registry JETSHOWPLAN key is ON, additional time is needed to create the text file whilst running each query/test.
Typically the times increased by about 14% in each case (about 10 seconds more for the HAVING/WHERE tests)
You will also end up with huge text files which may increasingly affect performance.
Hence, it is strongly recommended that the ShowPlan feature is only used for testing during development work.
When not required, change the JETSHOWPLAN key value in the registry to OFF.
5. JET ShowPlan Manager - UPDATE 8 Dec 2018
Return To Top
I have created a Jet ShowPlan Manager utility to manage the process of setting up and running the JET ShowPlan feature.
This will create the SHOWPLAN registry string value in the correct folder for the version of Access & Windows in use. It checks the Windows & Access versions and bitnesses as well as determining whether Office 365 is installed.
The application also allows you to toggle the SHOWPLAN registry key value ON and OFF
NOTE: As the SHOWPLAN registry string is located in the HKEY_LOCAL MACHINE hive, the application will only work if it is Run as an Administrator
There are two versions of this utility available - for Access 2010 or later / for Access 2007
For further information, click this link: Jet ShowPlan Manager
6. Further Reading - UPDATE 17 Dec 2021
Return To Top
Mike Wolfe, (recently appointed as Access MVP), has written an outstanding article called Jet Show Plan - A Primer on his excellent No Longer Set blog website.
In the article, Mike discusses the registry keys associated with the Jet ShowPlan feature, together with his use of AutoHotKey scripts to assist with setting these.
He also discusses ways of locating the ShowPlan.out file which can too often be surprisingly difficult.
However, in my opinion, by far the by far the most useful section of the article is his detailed explanation of what the contents of the ShowPlan.out file mean.
For example he explains which phrases assist with efficient query performance: (index, Rushmore) as well as phrases that indicate performance issues: (X-prod, scanning, temp, temporary).
You can use this information to help analyse your query structure in order to help make it run more efficiently ... i.e. go faster!
I STRONGLY recommend you read Mike's article.
7. Feedback
Return To Top
Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.
Do let me know if you find any errors or omissions.
Please also consider making a donation towards the costs of maintaining this website. Thank you
Colin Riddington Mendip Data Systems Last Updated 18 Aug 2023
|
Return to Example Databases Page
|
Return to Top
|