Queries & SQL

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
Anthony Passeri, MBA
••6 min read
Access Query Optimization: Speed Up Slow Queries

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:

  1. Open the table in Design View
  2. Click the field you want to index
  3. 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:

  1. Open the Windows Registry (regedit)
  2. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\<version>\Access Connectivity Engine\Engines\Debug
  3. Set JETSHOWPLAN to ON

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.

Schedule a Free Call →

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.

A

Written by

Anthony Passeri, MBA

Access Expert and Editor

Bridging the gap between Access and the Future

Explore Topics

#queries#performance#indexing#optimization#SQL

Keep reading

More on Queries & SQL