Access Query Optimization: Speed Up Slow Queries
Slow Access queries are often fixable without touching your data. Learn the indexing, join, and design techniques that make the biggest difference in query performance.
A query that ran fine last year is now taking 30 seconds. Your users are complaining. You have not changed anything — but the table has grown from 10,000 rows to 400,000.
This is one of the most common pain points in mature Access databases. The good news: most slow queries are fixable, and the fixes are usually straightforward once you know where to look.
Start with the Query Design View
Before optimizing anything, understand what the query is actually doing. Open it in Design View and look at:
- How many tables are joined
- Whether you are joining on indexed fields
- Whether you are filtering on indexed fields
- Whether you are using calculated fields in WHERE clauses
- Whether you are using wildcards at the start of LIKE expressions
Each of these is a potential performance killer. We will go through them one by one.
Index the Fields You Filter and Join On
This is the single most impactful optimization in most Access databases.
When Access runs a query with a WHERE clause like WHERE CustomerID = 1042, it has two options: scan every row in the table, or use an index to jump directly to matching rows. Without an index, it scans. With an index, it jumps.
How to add an index:
- Open the table in Design View
- Click the field you want to index
- In the Field Properties pane at the bottom, set Indexed to "Yes (Duplicates OK)" for most fields, or "Yes (No Duplicates)" for unique fields like CustomerID
Fields that should almost always be indexed:
- Primary keys (Access indexes these automatically)
- Foreign keys (CustomerID in an Orders table, for example)
- Fields you frequently filter on (Status, DateCreated, Region)
- Fields you join on between tables
Fields that do not need indexes:
- Fields you rarely query
- Boolean (Yes/No) fields — the selectivity is too low to help
- Memo fields — Access cannot index these
Fix Your JOIN Order and Conditions
Access's query optimizer is decent but not perfect. The order in which you join tables can affect performance, especially in complex multi-table queries.
General rule: join on primary key / foreign key pairs, not on calculated or concatenated values.
Bad:
WHERE Left(Orders.CustomerCode, 3) = Left(Customers.Code, 3)
Good:
WHERE Orders.CustomerID = Customers.CustomerID
The first example forces Access to compute a value for every row before it can compare. The second uses indexed integer fields directly.
Avoid Wildcards at the Start of LIKE
This one surprises developers who are used to SQL Server's full-text search.
-- Slow: leading wildcard forces a full table scan
WHERE CompanyName LIKE '*Smith*'
-- Fast: trailing wildcard can use an index
WHERE CompanyName LIKE 'Smith*'
A leading wildcard (*Smith* or *Smith) means Access cannot use the index — it has to check every row. A trailing wildcard (Smith*) can still use the index to find the starting point.
If you genuinely need to search for a string anywhere in a field, consider whether that field belongs in a separate lookup table, or whether this is a sign that your data model needs normalization.
Do Not Calculate in WHERE Clauses
Calculated expressions in WHERE clauses prevent index use.
Bad:
WHERE Year([OrderDate]) = 2026
Good:
WHERE OrderDate >= #1/1/2026# AND OrderDate < #1/1/2027#
The first version computes Year() for every row. The second compares raw date values, which Access can evaluate against an index on OrderDate.
The same applies to string functions:
-- Slow
WHERE UCase(LastName) = 'JONES'
-- Fast (store data consistently and query directly)
WHERE LastName = 'Jones'
Limit Columns in SELECT
Selecting more columns than you need forces Access to read more data from disk.
-- Slow (reads every column)
SELECT * FROM Orders WHERE CustomerID = 42
-- Fast (reads only what you need)
SELECT OrderID, OrderDate, TotalAmount FROM Orders WHERE CustomerID = 42
This matters most when your table has many columns, Memo fields, or OLE Object fields. Memo and OLE fields are stored separately from the main table data — selecting them forces additional disk reads.
Use Totals Queries Carefully
Totals queries (GROUP BY, COUNT, SUM) are inherently more expensive than simple SELECT queries because Access has to aggregate all matching rows before returning results.
To speed them up:
- Filter before aggregating when possible — use a WHERE clause to reduce the row count before GROUP BY runs
- Index the fields in your GROUP BY clause
- Avoid grouping on calculated expressions
If a totals query is used as a subquery or as the record source for a form, consider whether a saved query or a temporary table would perform better.
The Jet ShowPlan Tool
Access has a hidden query analysis tool called ShowPlan. It writes a log file showing how the Jet engine plans to execute your query — which indexes it will use, which tables it will scan, and in what order.
To enable it:
- Open the Windows Registry (regedit)
- Navigate to
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\<version>\Access Connectivity Engine\Engines\Debug - Set
JETSHOWPLANtoON
After running your query, look for showplan.out in your Windows temp folder. It is not pretty, but it tells you exactly what Access is doing — and what indexes it is ignoring.
Compact and Repair Regularly
As your database grows and records are added and deleted, the file becomes fragmented. Access's query optimizer uses statistics about table sizes and data distribution to make decisions — and those statistics can become stale.
Compact and Repair (Database Tools > Compact and Repair Database) refreshes these statistics and defragments the file. For heavily used databases, run it weekly or monthly.
When Optimization Is Not Enough
Sometimes a query is slow because the underlying data model is wrong for the query you are trying to run. Deeply nested subqueries, queries that join five or more tables, or queries that aggregate millions of rows are signs that you may have outgrown Access's query engine.
At that point, the options are:
- Move the data to SQL Server and use linked tables or pass-through queries. SQL Server's query optimizer is dramatically more capable than Jet.
- Pre-aggregate data into summary tables that your reports query instead of the raw transaction tables.
- Redesign the query to break it into steps, storing intermediate results in temporary tables.
If you are regularly hitting these limits, it is worth evaluating whether a migration to SQL Server or Power Platform makes sense for your database. The performance ceiling in Access is real, and optimization can only take you so far.
A Quick Optimization Checklist
Before you declare a query unfixable, work through this list:
- Are all JOIN fields indexed in both tables?
- Are all WHERE clause fields indexed?
- Are you using calculated expressions in WHERE clauses?
- Are you using leading wildcards in LIKE?
- Are you selecting columns you do not need?
- Have you run Compact and Repair recently?
- Is the query joining more than four tables?
- Is the underlying table larger than 500,000 rows?
The first six items are quick wins. The last two are signals that you may need a different approach.
Query performance is one of the most visible signs of a database that is ready for the next step. If you are spending significant time optimizing queries that were fast two years ago, it is worth having a conversation about what the right long-term platform looks like.
Work With Access Evolved
If you found this article useful, Access Evolved specializes in exactly this kind of work — from hands-on Microsoft Access support to full database modernization projects.
Dealing with slow queries in your Access database? Schedule a free call and get an expert performance review.
Thinking about migrating?
Ready to move beyond Microsoft Access?
Access is a great starting point, but growing businesses often hit its limits. Anthony Passeri specializes in migrating Access databases to modern platforms — Power Platform, Power Apps, SQL Server, Azure, and more — so you keep your data and workflows without the headaches.
Written by
Anthony Passeri, MBA
Access Expert and Editor
Bridging the gap between Access and the Future
Explore Topics
Keep reading
More on Queries & SQL
Writing Efficient SELECT Queries in Microsoft Access
Most Access users never move beyond SELECT *. Here is how to write targeted, indexed queries that run in milliseconds — even on large tables.
Subqueries in Access SQL: Queries Within Queries
Subqueries let you use the result of one query inside another, enabling complex filtering and calculations that are impossible with simple JOINs.
Totals Queries in Access: GROUP BY, COUNT, SUM, and More
Totals queries aggregate your data into summaries — counts, sums, averages, and more. Learn how to build them correctly and avoid the common mistakes.