Presentations

Troubleshooting Query Performance

A recording of the presentation, a list of resources, and further reading for my presentation on Troubleshooting Query Performance.

Mike Wolfe

• 5 min read
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.
No Longer SetMike Wolfe

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.
No Longer SetMike Wolfe
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.
No Longer SetMike Wolfe
JetShowPlan Manager
The quickest and easiest way to get started analyzing Microsoft Access query performance is with IslaDogs’ JetShowPlan Manager application.
No Longer SetMike Wolfe
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.
No Longer SetMike Wolfe

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.
No Longer SetMike Wolfe
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.
No Longer SetMike Wolfe
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.
No Longer SetMike Wolfe

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.
No Longer SetMike Wolfe
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.
No Longer SetMike Wolfe

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.
No Longer SetMike Wolfe
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?
No Longer SetMike Wolfe

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.
No Longer SetMike Wolfe

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.
No Longer SetMike Wolfe
ArrowKeyNav Presentation
A list of resources and further reading to support my presentation on Navigating Continuous Forms using WithEvents.
No Longer SetMike Wolfe
DevCon 2021
Links and resources from my talk today at Access DevCon Vienna 2021, twinBASIC: The New Kid on the Block.
No Longer SetMike Wolfe

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.
No Longer SetMike Wolfe
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!).

Never miss an article.

Sign up to receive a Sunday morning email with links and recaps of the seven articles published that week.
(I'll never sell your email. Unsubscribe any time.)


All original code samples by Mike Wolfe are licensed under CC BY 4.0