Troubleshooting Query Performance
Presentation Video
George Young has posted a video of the presentation to the Denver Area Access User Group's YouTube page:
Slide Deck
TroubleshootingQueryPerformance.pdf
Further Reading
Over the past year and a half, I've written several articles related to troubleshooting query performance in Access. Let me know in the comments if there are any topics you would like to see covered more in-depth.
Overview
Tuning Access Query Performance
The four best tools/techniques for tuning Access query performance: JetShowPlan, Process Monitor, TraceSQLMode, and SQL Profiler.
Jet Show Plan
JetShowPlan: A Primer
You may be familiar with JetShowPlan, but I guarantee you’ve never read an article about it quite like this one.
Signal vs. Noise: ShowPlan.out Edition
Reading showplan.out files can be intimidating for new and experienced Access developers alike. This simple rule will help get you started.
JetShowPlan Manager
The quickest and easiest way to get started analyzing Microsoft Access query performance is with IslaDogs’ JetShowPlan Manager application.
3 Ways to Find Showplan.out
Finding Jet ShowPlan’s showplan.out file can be deceptively difficult. Here are three approaches to make it easy. At least one is guaranteed to work.
ODBC Trace SQL Mode
Making Sense of SQLOUT.TXT
You’ve enabled ODBC Trace SQL Mode and created a sqlout.txt file. But do you know what to do with it now? Let’s explore.
Toggle ODBC TraceSQLMode with an AutoHotKey Script
Traditional methods of enabling and disabling ODBC TraceSQLMode are a pain in the neck. This AutoHotKey script makes it as easy as pushing a button.
3 Ways to Find sqlout.txt
Finding ODBC TraceSQLMode’s sqlout.txt file can be deceptively difficult. Here are three approaches to make it easy. At least one is guaranteed to work.
XEvent Profiler
How Access Reads Data From SQL Server
How does Microsoft Access pull data from SQL Server? You’ll be amazed when we pull back the curtain to expose how Access interacts with SQL Server.
Playing Telephone with SQL Server
You won’t believe how Access and SQL Server actually talk to each other. You’re going to need to see this for yourself.
ProcMon
Using ProcMon to Troubleshoot Registry Calls
Finding the correct registry keys for JetShowPlan and ODBC TraceSqlMode can be tricky. Let ProcMon take the guesswork out of the process.
DISTINCT vs. GROUP BY: Microsoft Access Speed Test
The GROUP BY and DISTINCT clauses can both be used to generate identical results. But which one is faster in Access? And--more importantly--why?
WireShark
Troubleshooting Access Hanging When Switching to Design View
How I used two low-level tools--ProcMon and WireShark--to figure out why Access was taking forever to switch to design view in forms and reports.
Past Presentations
If you enjoyed this presentation, you may want to check out some of my past presentations. Each of these has accompanying videos of the presentation available on YouTube (links to YouTube included in articles below):
Advanced Combo Box Techniques
A list of resources and further reading to support my presentation on Advanced Combo Box Techniques.
ArrowKeyNav Presentation
A list of resources and further reading to support my presentation on Navigating Continuous Forms using WithEvents.
DevCon 2021
Links and resources from my talk today at Access DevCon Vienna 2021, twinBASIC: The New Kid on the Block.
Future Presentations
I'll also be presenting at the following upcoming events:
Access DevCon Vienna 2022
Mark your calendars and register for Access DevCon Vienna! The event will be held virtually via MS Teams on April 28 and 29, 2022, from 10 AM - 4 PM EDT.
Access Europe – Mike Wolfe (Building Ribbon Interfaces in Code) – Europe – AccessUserGroups.org
UPDATE [2022-03-18]: Added link to a recording of the presentation on YouTube (thanks for the quick turnaround George!).