Skip to content

Query Optimization and the N+1 Problem

Query optimization begins with evidence: slow-query logs, request traces, query counts, execution plans, and representative data. Object-level intuition alone cannot reveal indexes chosen, rows examined, network transfer, locks, or database resource pressure.

Recognizing N+1

N+1 occurs when one query loads a collection of parent records and then one additional query is issued for each parent's related data. It often hides behind lazy association traversal, serialization, mapping, or view rendering.

select orders ...             -- 1 query
select items where order=?    -- repeated N times

Count statements in an integration test or trace. A small development data set can make the latency invisible while the query count still grows linearly.

Select a fetch strategy per use case

  • a fetch join can load a bounded relationship in one query;
  • an entity graph makes selected associations explicit;
  • a projection retrieves only fields required by the response;
  • batch or subselect fetching reduces round trips while retaining lazy loading;
  • a dedicated aggregate query can compute counts or summaries without entities.

Blanket eager fetching merely moves the problem. Joining several to-many relationships can multiply result rows and memory. Collection fetch joins also interact poorly with pagination; commonly paginate parent IDs first and fetch details in a second bounded query.

Index and plan reasoning

An index should match selective predicates and useful ordering, but every index adds write, storage, and maintenance cost. Inspect the actual or safely sampled execution plan with realistic parameter distributions. Avoid wrapping indexed columns in expressions unless the database has a suitable expression index.

Fetching fewer rows and columns is usually more robust than relying on an application cache to hide waste. Optimize transaction scope as well: remote calls or large mappings while locks are held increase contention.

Regression protection

  • assert a query-count ceiling for critical repository use cases;
  • test with enough related rows to expose multiplication;
  • inspect generated SQL and parameter binding;
  • benchmark representative cardinalities and deep pagination;
  • monitor database time separately from application mapping time;
  • recheck plans after schema, statistics, or distribution changes.

See the official Hibernate fetching guidance.