Show Plan - Run Faster

I have recently been running a series of speed tests to compare the efficiency of different approaches to the same task. These tests are located in various locations across this forum or can all be found on my website at:http://www.mendipdatasystems.co.uk/speed-comparison-tests/4594424200

NOTE: An extended version of this article is available at: http://www.mendipdatasystems.co.uk/show-plan-go-faster/4594460516

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. If/ElseIf/End If vs Select Case vs Nested IIf vs Switch vs Lookup Table

Some of these speed tests have resulted in unexpected outcomes that went against long held beliefs by several experienced developers.

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 little known ShowPlan feature which is available with both the JET database engine (up to A2003) and the newer ACE engine (A2007 onwards). The ShowPlan option prints the query's plan to a text file so you can review and, if possible, improve the design.

Using the forum advanced search showed just 17 mentions of ShowPlan & most were no more than a brief comment or a link to an excellent explanation of the ShowPlan feature by Susan Haskins:
https://www.techrepublic.com/article/use-microsoft-jets-showplan-to-write-more-efficient-queries/

Because this feature is little documented, I am going to explain in some depth.

The following quote is taken from Susan Haskins' article (which I strongly recommend reading in full) :

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

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.

Click to expand...

Although the article was written in 2003, it only needs minor alterations to use with current versions of Access.

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: Access 2010:
NOTE for other versions of Access from 2007 onwards, replace the 14.0 with the Access version number

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

Once you have found the correct Engines key for your version, open it & create a new key Debug with a string value JETSHOWPLAN (all in upper case) and set the value to ON

The ShowPlan feature is now enabled and the registry editor can now be closed.
If you have Access open, close and reopen it to make use of the ShowPlan feature

From now on, it 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.
Each new query will add additional text to that file in turn.
The file can be opened in any text editor such as Notepad

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:



The query execution plans are identical!
The HAVING file shown above is the complete file
The WHERE file contained additional information related to the use of the index used on the DOB field

In the real 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



The INSERT test used a different approach which was far more efficient and therefore MUCH faster

Attached is a zip file containing the showplan.out files for each of the 3 tests for anyone who is interested

IMPORTANT:
Whilst the registry JETSHOWPLAN key is ON, additional time is needed to create the text file whilst running each query/test.
Typically in my tests, 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, as also stated in Susan Haskins' article, it is strongly recommended that the ShowPlan feature is only used for testing during development work.
When not required, change the JETSHOWPLAN key value to OFF.

Hi,


Just curious... What would the showplan show if you had any aggregation in the HAVING clause?

RefreshCache is different from DoEvents.
Where is also different from Having clause.

arnelgp said:

RefreshCache is different from DoEvents.
Where is also different from Having clause.

Click to expand...

Yes Arnel
I think you have either not read or may have missed the point of these articles.

Although the code being compared is different, the tests are designed to compare different ways of achieving the same outcomes
e.g. WHERE vs HAVING ; If...ElseIf ...End If vs Select Case etc

The comparison between using DoEvents & RefreshCache was done as part of a test into ways of pausing code long enough to allow a task to complete but without causing a significant slowdown of the code

If you believe the tests are not fair, I challenge you to provide better versions.
You may well be able to provide examples of queries where the use of WHERE gives different results to the use of HAVING.
But for all the tests I did, I checked and the output results are always the same