AEU7: Optimising Queries & the JET ShowPlan (Colin Riddington)

The session was presented by Colin Riddington of Mendip Data Systems. All Access developers need a good understanding of how to make their code and queries run as efficiently as possible. The focus of this session was on ways of optimising the query execution plans created by Access when queries are run.

During the session, a series of speed comparison tests were performed showing the effect of each suggested change on the query execution.

In addition, the session demonstrated two little known features that can help explain how Access runs queries:
a) JET ShowPlan – used to view the query execution plans. See my article: JET ShowPlan Manager
b) ISAMStats – used to determine the number of Disk Reads / Writes / Locks when queries are run

The final part of the session involved a quiz on query optimisation!

TIMES
0:00 Intro by Colin Riddington
0:34 Outline of main presentation - comparative speed tests & use of available tools
2:23 AIM: Compare & analyse speed tests done on 11 different versions of the same query
3:23 Example database used for speed tests starting with a deliberately badly designed query and gradually optimising step-by-step
5:47 Query A - OuterJoin with DLookup - time taken approx 37.5 s
6:31 Query B - Outer Joins (Many-to-one) - approx 2.1 s
7:18 Query C - Outer Joins (One-to many) - approx 1.71 s
8:24 Query D - Inner Joins - Nz(Count) - approx 1.56 s (statistical variation - always repeat tests)
9:47 Query E - IIf(Count) - approx 1.32 s
10:22 Query F - Simple Count - approx 1.31 s
10:41 Query G - Indexed Order By - approx 1.23 s
11:33 Query H - WHERE vs HAVING - approx 1.05 s
12:45 Query I - Indexed Filter Fields - approx 0.89 s
13:50 Query J - First vs Group By - approx 0.84 s
15:30 Query K - Stacked Queries - approx 0.84 s (again)
16:20 Overall, time dropped to about 1/40 of original time
16:44 Results of running tests multiple times to reduce statistical variation
17:25 Report of average test results
18:00 Question (Tom W) - why is the first iteration of repeated tests slower? Answer: Access determines the most efficient query execution plan in first run then uses that thereafter
19:20 Question (Alan C) - how is the code formatting done? Answer - use of rich text
19:45 Question (Luke C? ) - why is WHERE faster than HAVING? Answer - HAVING filters after count; WHERE before (provided indexing used)
23:45 Use JET ShowPlan Manager add-in to simplify process - run Access as administrator
25:26 Demo of JET ShowPlan add-in - enable JET ShowPlan feature - then restart Access
30:25 Discussion of execution plan for Query A (very inefficient as use of DLookup means EVERY record is scanned one at a time)
33:50 Execution plan for query B - much shorter - stightly more efficient
34:37 Execution plan for query J - very efficient - use of indexing & Rushmore technology
35:51 Quick review of other execution plans &
38:50 ISAMStats - hidden undocumented feature - almost entirely unknown - measures disk activity during query execution - but 'deactivated' in Access 2013
40:38 DEMO of ISAM Stats code in Access 2010 - code taken from Access 2000 Developers Handbook
43:35 Running ISAMStats code in Access 365 triggers error 3251
44:45 Useful links related to this topic
47:50 Quiz Time - series of questions - which query is faster - A or B - and why. Group discussion
53:10 Tom W - talked about predicate pushdown issue for multiple layers of stacked queries in SQL Server - possible future topic?
1:02:06 Luke C - saved query faster than SQL for form/report record source
1:04:30 Chris A - suggested speed tests on non-equi joins vs subqueries - Chris to follow up
1:06:41 Tom W - shared quote by Mary Chipman on SQL Server vs Access
1:07:39 Next month - Alessandro Gimaldi - Cataloguing and searching emails with Access
1:09:07 See you next month

[LearnAccessByCrystal]

outstanding presentation, Colin, thank you. I will be surely reviewing it again the the future when I use JET ShowPlan. ISAMStats was very very interesting too. I've seen properties for them and it's nice to know what they are. I have 2007 loaded also, so I shall explore ...

Alessandro's presentation next month sounds interesting -- assume he's linking to email tables (anything special about the linking)?. Access is much faster and more powerful than Outlook's extremely limited searching capabilities -- sounds very handy. Alessandro, do you know how to link to local Thunderbird email boxes?