Go Back   Access World Forums > Microsoft Access Reference > Code Repository

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-03-2018, 08:15 AM   #1
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,438
Thanks: 106
Thanked 2,531 Times in 2,324 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
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/s...sts/4594424200

NOTE: An extended version of this article is available at: http://www.mendipdatasystems.co.uk/s...ter/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) :

Quote:
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\En gines\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 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:
PHP Code:
HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\JET\4.0\Engines 
Access 2010:
PHP Code:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines 
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.
PHP Code:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\REGISTRY\MACHINE\Software\Wow6432Node\Microsoft\Office\16.0\Access Connectivity Engine\Engines 
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.
Attached Images
File Type: png AvgResults Having vs Where - 6B.PNG (12.8 KB, 271 views)
File Type: png ShowPlan - HAVING&WHERE.PNG (64.5 KB, 278 views)
Attached Files
File Type: zip ShowPlan.zip (8.4 KB, 57 views)

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by isladogs; 11-03-2018 at 12:48 PM.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
Galaxiom (11-03-2018)
Old 11-03-2018, 08:54 AM   #2
theDBguy
I’m here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,040
Thanks: 36
Thanked 722 Times in 705 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Show Plan - Run Faster

Hi,


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

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 11-03-2018, 09:11 AM   #3
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 7,195
Thanks: 57
Thanked 2,311 Times in 2,217 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Show Plan - Run Faster

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

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 11-03-2018, 09:40 AM   #4
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,438
Thanks: 106
Thanked 2,531 Times in 2,324 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Show Plan - Run Faster

Quote:
Originally Posted by arnelgp View Post
RefreshCache is different from DoEvents.
Where is also different from Having clause.
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

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by isladogs; 11-03-2018 at 11:51 AM.
isladogs is offline   Reply With Quote
Reply

Tags
jet showplan , query optimisation

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Seating plan prmitchell Reports 1 08-17-2014 10:33 PM
Looking for Best Plan of Attack cfp76 General 5 05-06-2013 02:19 PM
Trying to plan out a db. Plan is attached. Advice welcome. Colin@Toyota Theory and practice of database design 1 10-31-2007 12:09 PM
DTS Execution Plan Karma SQL Server 2 08-11-2006 03:53 AM
Plan B gblack Modules & VBA 3 09-09-2005 12:49 PM




All times are GMT -8. The time now is 10:25 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World