Access Europe – Colin Riddington (Optimising Queries and the JET ShowPlan)

Topic Outline:

 All Access developers need a good understanding of how to make their code and queries run as efficiently as possible. The focus of today’s topic is on ways of optimising the query execution plans created by Access when queries are run.

The content will be based on that covered in my web article: https://www.isladogs.co.uk/speed-comparison-tests-8/

As my starting point, I used an article on Allen Browne‘s website devoted to various methods of improving query performance: http://allenbrowne.com/QueryPerfIssue.html

During the session, I will perform a series of speed tests showing the effect of each suggested change on the query execution.

I will also discuss two little known features built into Access that can help explain how Access runs queries:

  1. JET ShowPlan – used to view the query execution plans
  2. ISAMStats – used to determine the number of Disk Reads & Writes, Cache Reads, ReadAheadReads, LocksPlaced and LocksReleased when queries are run

 An example app will be available for download after the presentation together with the JET ShowPlan Manager utility which simplifies the use of this feature

Bio

Colin has been an Access developer for over 20 years starting with Access 97, with many years teaching in secondary schools including responsibility for data analysis and developing database apps for teachers in several schools. He lives in Somerset, UK.

Colin took early retirement from teaching in order to focus on database development work.

His website includes many Access articles, example apps, sample code and security challenges, together with commercial applications for businesses, schools and developers. His focus is on stretching the boundaries of what can be achieved using Access.

He was recently presented with the Microsoft Most Valuable Professional (MVP) Award. See https://mvp.microsoft.com/en-us/PublicProfile/5004835

Website:      https://www.isladogs.co.uk 

YouTube:    https://www.youtube.com/c/isladogsonaccess

 

Do join us

======================================================================

IMPORTANT NOTE

All sessions will be recorded and later uploaded to the AccessUserGroups channel on YouTubeAccessUserGroups.org – YouTube.

If you do not wish to be part of the recording, please ensure your webcam and microphone are both switched off.

======================================================================

We are planning to try out Zoom as our meeting application.

Please click the link below to download Zoom, if you don’t already have it.

Get Zoom

To join our meetings, please use the following link or phone number

Join Zoom Meeting

If you are asked:

Meeting ID: 924 3129 5683
Passcode: 661210

Dial by your location:
+1 669 900 9128 US (San Jose)
+1 253 215 8782 US (Tacoma)
+1 301 715 8592 US (Washington DC)
+1 312 626 6799 US (Chicago)
+1 346 248 7799 US (Houston)
+1 646 558 8656 US (New York)
+43 12 535 502 Austria
+32 1579 5132 Belgium
+359 3 257 1633 Bulgaria
+385 1300 0988 Croatia
+420 5 3889 0161 Czech Republic
+45 32 70 12 06 Denmark
+358 9 7252 2471 Finland
+33 1 7037 9729 France
+49 69 7104 9922 Germany
+30 211 198 4488 Greece
+36 1 779 9126 Hungary
+353 1 653 3898 Ireland
+39 069 480 6488 Italy
+352 2786 4277 Luxembourg
+356 2778 1288 Malta
+31 20 241 0288 Netherlands

+48 22 307 3488 Poland
+351 308 810 988 Portugal
+40 31 630 1088 Romania
+421 233 418 515 Slovakia
+386 1600 3102 Slovenia
+34 917 873 431 Spain
+41 43 210 71 08 Switzerland
+46 850 539 728 Sweden
+44 203 481 5240 United Kingdom
+44 208 080 6591 United Kingdom

Meeting ID: 924 3129 5683
Passcode: 661210
Find your local number: https://zoom.us/u/a4d0JdR88