Скачать или смотреть 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.
The content was based on that covered in the web article: https://isladogs.co.uk/speed-comparis...

The starting point was an article on Allen Browne's website devoted to various methods of improving query performance: http://allenbrowne.com/QueryPerfIssue...

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!
The example app and the JET ShowPlan Manager add-in are both available to download from https://isladogs.co.uk/aeu-7/


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)
21:30 JET ShowPlan - requires new registry key - location depends on Access version & bitness - see https://isladogs.co.uk/jet-show-plan/
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
27:50 Text file showplan.out created in default database directory - shows the execution plan of each query run including code run when app starts
30:25 Discussion of execution plan for Query A (very inefficient as use of DLookup means EVERY record is scanned one at a time)
32:55 Use of free utility, Everything, to locate the showplan.out file if necessary
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 &
37:22 John C - mentioned code by Mike Wolfe to colour code execution plans for good / bad issues: https://nolongerset.com/signal-vs-noi...
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