Why Your Access Queries Are Slow (And How to Fix Them)

Quick answer: a query that used to run instantly and now takes 10-60 seconds almost always comes down to one of four things: a missing index on a filtered or joined column, a function wrapped around that column in the WHERE clause (which blocks the index entirely), a domain function like `DLookup` re-scanning a table on every row, or a query pulling far more rows than the screen actually needs. Fix those four in order and most "slow query" tickets close without touching the database's overall architecture.

This post is specifically about query-level troubleshooting — indexing, joins, WHERE clause design, and how to see what Jet/ACE is actually doing under the hood. If the whole database feels sluggish (not just one query), including multi-user contention, form load times, and file bloat, start with our broader MS Access database slow fix guide instead — this post is the deep-dive that guide links out to.

Query symptom, cause, and fix

• Symptom: A query took under a second, now takes 10-60 seconds after data grew. Cause: Missing index on a filtered or joined column. Fix: Add an index on that specific column.
• Symptom: A query using `DLookup` or `DSum` inside a loop crawls once the table passes a few thousand rows. Cause: Domain functions re-scan the source table on every call. Fix: Replace with a JOIN or a precomputed subquery.
• Symptom: A query filtering on `Format()`, `Left()`, or a calculated date grinds to a halt even though the underlying field is indexed. Cause: Non-sargable WHERE clause — the function wraps the indexed column so the index cannot be used. Fix: Rewrite the criteria to compare raw indexed values (e.g., a date range instead of `Format([Date],"yyyy-mm")`).
• Symptom: A report joining four or more tables times out during month-end close. Cause: No pre-aggregation before the join; Jet is doing the aggregation work at query time across the full join. Fix: Pre-aggregate the transactional table into a staging query, then join to dimensions.
• Symptom: The query is fast in isolation but slow when it runs from a form or report. Cause: The query re-executes on every UI event (each keystroke in a search box, each record navigation) instead of once. Fix: Filter in the RecordSource directly, or cache the result and filter client-side for small sets.
• Symptom: The query returns correct data, but the query design window itself is slow to open or save. Cause: Bloated or fragmented query definitions after years of edits. Fix: Compact & repair, and occasionally rebuild the query from scratch rather than editing an old one repeatedly.

Identifying slow queries before you touch anything

Guessing which fix to apply is how databases end up with ten unused indexes and a query that is still slow. Look for these signals first:

• Queries that were sub-second now run 10-60 seconds after data growth — almost always an indexing problem.
• Forms tied to a saved query open slowly or go intermittently unresponsive — the query is likely pulling more rows than the form displays.
• Users see lock waits or "not responding" during peak shared usage — worth cross-checking against [database locking](/blog/access-database-locking) before assuming it is a query issue at all.
• Reports fail to finish inside operational windows (month-end, dispatch, audit prep) — usually a join or aggregation problem, not indexing.
• Remote users see exaggerated delays that local users do not — chatty linked-table traffic, not the query itself.

See what Jet/ACE actually did, not what you assume it did

Most Access developers tune queries by trial and error because they never look at the execution plan. Access has a built-in option for this: File/Tools → Options → Object Designers → Query Design → "Show query plan." With it enabled, running a query writes a plan file showing whether Jet/ACE used an index, a Rushmore bitmap, or a full table scan for each step. On older Jet 4.0-based files, the equivalent is a registry toggle (`JETSHOWPLAN` under the Jet Engines\Debug key) that writes a `SHOWPLAN.OUT` file next to the database. Either way, this tells you definitively whether an index you added is actually being used — Performance Analyzer only offers generic recommendations, ShowPlan shows the real execution path.

Common performance issues

1. Missing indexes

Problem: Queries scan entire tables instead of seeking through an index.

• Add indexes to columns used in `WHERE`, `JOIN`, and `ORDER BY` paths that matter in production — not every column, just the ones actually filtered or joined on.
• Index every foreign key used in joins; unindexed FK joins are the single most common source of avoidable table scans.
• Use composite indexes when queries consistently filter on the same combination of fields, in the same order.
• Remove redundant or low-selectivity indexes — they slow writes without helping reads, and an index on a field with only two or three distinct values rarely earns its keep.

Real example: A parts-lookup query filtering on `PartNumber` with no index averaged around 7 seconds on a 90,000-row table. A single index on `PartNumber` brought that to roughly 0.3 seconds — a ~20x improvement from one change.

2. Non-sargable WHERE clauses

Problem: Wrapping an indexed column in a function forces Jet/ACE to evaluate the function on every row, which defeats the index even though one exists.

• Rewrite date-formatted criteria (`Format([OrderDate],"yyyy-mm") = "2026-08"`) as a raw range comparison (`OrderDate >= #8/1/2026# AND OrderDate < #9/1/2026#`).
• Push display/formatting logic to the UI layer instead of filtering on formatted text.
• Avoid calculations on indexed columns inside `WHERE` or `JOIN` conditions — calculate on the literal side of the comparison instead.
• For recurring complex filters, maintain a helper column that preserves a sargable predicate rather than repeating the same wrapped expression in every query.

Real example: A monthly sales report filtered with `Format([OrderDate],"yyyy-mm")` took 22 seconds on 140,000 rows despite an index existing on `OrderDate`. Switching to a direct date-range comparison dropped it to under 2 seconds — same index, same data, just a sargable predicate.

3. Domain functions in large rowsets

Problem: `DLookup`, `DSum`, and similar domain functions re-scan their source table for every row they are called on.

• Avoid domain functions in queries or loops running against more than a few hundred rows — replace with a JOIN or a subquery instead.
• Stage heavy per-row calculations into a temp or staging table for recurring reports rather than recalculating them live each time.
• Reserve domain functions for genuinely small lookups (a handful of rows) where the convenience outweighs the cost.

4. Inefficient joins

Problem: Joining large tables without selective, indexed keys.

• Ensure both sides of a join use compatible, indexed data types — a text-to-number mismatch silently forces a scan.
• Use explicit `JOIN` syntax so join intent is readable and debuggable months later.
• Eliminate accidental cartesian joins caused by a missing join predicate — these are easy to introduce when a query is edited by multiple people over time.
• Pre-aggregate large transactional tables before joining to dimension tables when the report grain allows it.

5. Returning too much data

Problem: Selecting every column and every row when the UI only needs a filtered slice.

• Avoid `SELECT *` in saved queries feeding forms and reports; project only the columns actually used downstream.
• Filter at the earliest possible stage so downstream joins process fewer rows.
• Use `TOP` with a deterministic `ORDER BY` when the UI only needs the most recent or highest-priority records.
• Apply `DISTINCT` only when deduplication is genuinely required — it forces an expensive sort you may not need.

Benchmark before and after — do not guess

A single timed run is noisy. Averaging several runs gives you a number worth trusting when you are deciding whether a fix actually helped:

Public Sub BenchmarkQuery(queryName As String, iterations As Integer)
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim startTime As Single
    Dim i As Integer

    Set db = CurrentDb
    startTime = Timer

    For i = 1 To iterations
        Set rs = db.OpenRecordset(queryName, dbOpenSnapshot)
        rs.MoveLast
        rs.Close
    Next i

    Debug.Print queryName & ": avg " & _
        Format((Timer - startTime) / iterations, "0.000") & " sec over " & iterations & " runs"
    Set db = Nothing
End Sub

Run it against the query before you add an index, note the average, make the change, run it again. If the number does not move, you fixed the wrong thing — check ShowPlan before adding a second index blindly.

Optimization techniques

Indexing strategy

Primary keys: always define a stable, unique primary key; numeric keys keep joins lightweight, and wide text keys increase both index size and join cost.

Foreign keys: index every foreign key used in joins or lookups, enforce referential integrity to prevent orphan rows that inflate query complexity, and keep FK/PK data types aligned to avoid implicit conversion penalties.

Frequently queried fields: index high-value filter fields used repeatedly in production, and reassess the index set after any major report or feature addition — indexes that made sense a year ago may no longer match how the database is actually queried.

Query design checklist

1. Select specific fields — avoid `SELECT *`.
2. Filter early — apply `WHERE` before the query reaches a join wherever possible.
3. Limit results with `TOP` and a deterministic `ORDER BY` when appropriate.
4. Replace nested queries with JOINs where the logic allows it.
5. Use parameter queries for flexible, reusable filtering.
6. Validate data types across joins to prevent implicit conversion scans.
7. Separate OLTP-style transactional queries from reporting queries rather than forcing one query to serve both.

Parameter queries

Parameter queries give you consistent execution behavior across different criteria values, avoid duplicating query text for every variation, lower SQL-injection risk in VBA-built SQL strings, and make troubleshooting easier because the parameter intent is explicit in the query definition rather than buried in string concatenation.

Advanced optimization

Query Performance Analyzer — and its limits

Database Tools → Analyze Performance will flag missing indexes and suggest generic fixes, but it does not know your actual query patterns. Validate every recommendation against real usage before implementing it, make changes in a copy, and benchmark before/after with the timing approach above rather than trusting the tool's suggestions blindly.

Compact and repair

Compact the database on a controlled schedule, especially after heavy deletes or imports. Review index health periodically and remove indexes no hot query actually uses — every unused index is pure write overhead. For the full compacting process and common errors, see our compact and repair guide.

Split database

Splitting reduces remote form and report "chattiness," lowers network round trips in multi-user environments, and improves concurrency since each user runs a local front-end against a shared back-end. It does not, by itself, fix an unindexed query — the two fixes are independent and you typically need both.

Monitoring performance

Tools: Performance Analyzer, query timer settings in query properties, SQL view for reviewing execution logic, Event Viewer for errors, and lightweight VBA timing logs like the benchmark function above for ongoing tracking.

Metrics worth watching: query execution time, records processed versus records actually needed, network round trips, lock conflicts, form open time tied to its RecordSource query, and report completion time at peak load.

Quick fixes, in priority order

1. Add the missing index — this is the fix that resolves the most tickets.
2. Rewrite non-sargable criteria so the index you already have can actually be used.
3. Replace domain functions (`DLookup`, `DSum`) in loops with a JOIN.
4. Limit result sets with `TOP` or a tighter `WHERE`.
5. Remove unnecessary joins and simplify the query shape.
6. Compact the database to rebuild internal structures.

Frequently asked questions

Why are my Access queries suddenly slow?

Almost always a threshold problem, not a sudden one — the table grew past the point where a missing index started mattering, or a function-wrapped WHERE clause that was always non-sargable finally has enough rows to feel slow.

What is a non-sargable query in Access?

A query where the WHERE clause wraps an indexed column in a function (like `Format()` or `Left()`), which forces Jet/ACE to evaluate that function row by row instead of seeking through the index — even though the index exists.

Is `DLookup` bad for performance in Access?

Not inherently, but it re-scans its source table on every call. It is fine for occasional lookups against small tables; it becomes a serious bottleneck when called in a loop against a table with more than a few hundred rows, where a JOIN will almost always outperform it.

How do I know which field to index?

Look at your slowest query's WHERE, JOIN, and ORDER BY clauses — index the fields that appear there, especially foreign keys. Enabling Show Query Plan confirms whether Jet/ACE is actually using the index once you add it.

Does Access have a query execution plan like SQL Server?

Yes, though it is less visible by default. Enabling "Show query plan" under Object Designers in Access Options (or the legacy `JETSHOWPLAN` registry setting on older Jet files) writes a text file showing whether each step used an index, a Rushmore bitmap, or a full table scan.

Can too many joins slow down an Access query?

Yes, particularly when joins are stacked without pre-aggregation. Four or more table joins against large transactional tables is a common cause of month-end reports timing out — pre-aggregating before the join is usually the fix, not removing tables from the report.

Do parameter queries actually improve performance?

They improve consistency and reusability more than raw speed, but they indirectly help performance by avoiding string-concatenated SQL rebuilt on every call and by making it easier to reuse a single, well-indexed query object across multiple forms and reports.

When to seek professional help

If your queries are still slow after indexing, rewriting non-sargable criteria, and cleaning up domain functions, stop tuning blindly and check whether the architecture itself has outgrown Access. High concurrency, fast-growing row counts, or lock waits that dominate over actual query time are signs the fix is no longer query-level. Our Access database development services can redesign queries, stage aggregates, and plan a SQL Server migration without throwing away your forms — see Access-to-SQL Server migration for that path, or Access performance optimization services if you want the full diagnosis and fix handled end to end. If the slowness shows up everywhere, not just in specific queries, start instead with our complete database slow fix guide.