Mendip Data Systems

Database applications for businesses and schools

LogoTransparent

Show Plan . . . Run Faster !

Return to Home Page

I have recently been running a series of Speed Comparison Tests to compare the efficiency of different approaches to the same task

 

Currently 5 tests are available though more are planned as time permits

1. Handling nulls: Trim / Len / Nz        

2. CurrentDB vs DBEngine(0)(0)

3. DoEvents vs DBIdle.RefreshCache    

4. HAVING vs WHERE  

5. Conditional Updates:  If /ElseIf/End If vs Select Case vs Nested IIF vs Switch vs Lookup Table                        

 

Several of these speed tests have 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.

 

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 treading this article which was written back in 2003 by Susan Haskins:

Use Microsoft Jet's ShowPlan to write more efficient queries

 

The following quote is taken from that article:

 

 

Colin Riddington      Mendip Data Systems        Updated 08/12/2018

Return to Top
* Required

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

Return to Access Articles

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 written in 2003, there is very little information available elsewhere online.

 

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

 

Create the registry key

 

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 2010:   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines

 

For other versions of Access from 2007 onwards, replace the 14.0 with the Access version number e.g. 16.0 for Access 2016:

 

For 32-bit Access in 64-bit Windows use e.g.

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

JetShowPlan1 JetShowPlan2

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

JetShowPlan3

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. If Access is open, close and reopen it to make use of the feature.

 

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:

ShowPlanOut1

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:

ShowPlan - HAVING ShowPlan - 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 several times through every combination of birth dates and months.

Each ‘pass’ creates 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

AvgResults Having vs Where - 6B

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.

UPDATE 08/12/2018:

 

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  

JetShowPlanManager

Difficulty level :   Advanced