The scenario: Your API loads 100 users and their orders. It works locally — but becomes extremely slow in production. What could be causing it?
One of the most common (and sneaky) causes: the N+1 Query Problem. 🐢
What Is It?
Suppose the application needs to display 100 users along with their orders. You'd expect the database to fetch everything together in one efficient round-trip. But here's what often actually happens:
1️⃣ The application sends one query to fetch all 100 users.
2️⃣ It then sends one additional query per user to fetch that user's orders.
Result:
- ✅ 1 query to fetch the users
- ❌ 100 additional queries — one per user — to fetch their orders
That's 101 database queries for a single API request.
This is the textbook N+1 Query Problem:
1 query fetches the parent records, then N additional queries fetch the related records
individually — one per parent row.
With 100 users, this might go unnoticed in a local test with a fast local DB and no network latency. With 10,000 users, the same code path triggers 10,001 queries — and it becomes catastrophic in production, especially over a real network to a real database.
Why It Hurts
Every additional query introduces:
- Network round-trip time (multiplied by N)
- Database connection pool usage (queries competing for limited connections)
- Query-processing/parsing overhead per call
- More load on the database server
- Significantly higher API response time — often the actual root cause of "works locally, slow in prod," since local dev DBs have near-zero latency per query while production round-trips add up fast.
Why Does It Happen?
Most commonly with an ORM using lazy loading. The application loads the users first (one query). Later, when code accesses user.getOrders() inside a loop, the ORM silently and transparently issues another database query per user at that exact point — invisible in the code (it just looks like a normal getter call), which is exactly why it's easy to miss in code review and easy to reproduce only under realistic data volumes.
How to Fix It
🔗 1. Fetch related data together (join fetch)
Use a join or fetch strategy that retrieves the parent (users) and the related child data (orders) together, in a single query, instead of triggering a separate query per parent row.
📦 2. Use batch fetching
Instead of loading orders one user at a time, retrieve the orders for multiple users in one batch query (e.g., WHERE user_id IN (1, 2, 3, ..., 100)). This can turn 100 queries into a small handful — often reducing hundreds of queries down to just a couple.
🎯 3. Fetch only the required fields (projections)
If the API only needs a user's name, order number, and status, don't load the complete User,Order, and every related entity graph. Project only the fields actually needed for the response — less data over the wire, less mapping overhead, and often avoids triggering lazy-loaded associations entirely. — less data over the wire, less mapping overhead, and often avoids triggering lazy-loaded associations entirely.
📄 4. Add pagination
Never load thousands of users (and their orders) in a single request. Retrieve a bounded page and only the data needed for that page — this caps the worst-case query count and payload size regardless of how large the underlying table grows.
How Do You Detect the N+1 Problem?
-
Enable database query logging (e.g., Hibernate's
show-sql/format_sql, or the database's own slow-query log) and actually read what gets generated for a given endpoint. - Monitor the number of queries per API request — a sudden spike proportional to the result set size (e.g., queries scale linearly with the number of users returned) is the classic signature.
- Use an APM tool (e.g., New Relic, Datadog, Elastic APM) — most surface a per-request query count and flag "N+1 detected" patterns automatically.
-
Look for repeated queries with different IDs in the log — the same
SELECT ... FROM orders WHERE user_id = ?template repeated 100 times with different parameter values is the smoking gun. - Test using realistic data volumes — this bug is invisible with 3 test users locally and becomes an incident with 10,000 users in production; load/integration tests need production-like data sizes to catch it before release.
Spring Boot / JPA: Which Fix Should You Prefer?
A common follow-up question: "Which approach do you prefer in Spring Boot — fetch joins, entity graphs, batch fetching, or DTO projections?"
There isn't a single universally-correct answer — it depends on the access pattern — but here's how to reason about the trade-offs:
| Approach | How it works | Best when | Watch out for |
|---|---|---|---|
Fetch Join (JOIN FETCH in JPQL) |
Single SQL query joins parent + child in one round-trip | You always need the child collection alongside the parent for this query | Fetching multiple collections with JOIN FETCH in one query causes a Cartesian product explosion (rows multiply); JPA restricts fetching more than one List-type collection this way |
Entity Graph (@EntityGraph) |
Declaratively specifies which associations to eagerly fetch, per query/method, without changing the entity's default fetch type globally | You want eager fetching for specific use cases but keep lazy loading as the sane default elsewhere | Still one query per graph, but multiple collections in the same graph can hit the same Cartesian-product issue as fetch joins |
Batch Fetching (@BatchSize, or hibernate.default_batch_fetch_size) |
Lazy loading still triggers on access, but Hibernate batches the next N pending lazy loads into one IN (...) query instead of one-by-one |
You have unavoidable lazy access patterns (e.g., generic repository code used in many contexts) and want a broad, low-effort mitigation | Doesn't eliminate extra queries entirely, just batches them — still more than a single join, though far better than true N+1 |
| DTO Projections (JPQL constructor expressions, Spring Data interface projections, or a mapper) | Query selects only the exact fields needed directly into a DTO — no entity graph traversal at all | Read-only / API-response use cases where you don't need full entity objects or their lazy associations | Loses JPA entity features (dirty checking, lazy nav) — but for a read API, you don't want those anyway |
Default preference: start with DTO projections for read-heavy API endpoints — they sidestep the N+1 problem entirely because there's no entity graph to lazily traverse in the first place, and they also naturally satisfy "fetch only the required fields" (fix #3) at the same time. Reserve fetch joins / entity graphs for cases where you genuinely need full entity objects (e.g., you're about to modify and save them), and use batch fetching as a safety net for lazy-loaded associations you can't easily eliminate across a large, shared codebase.
🔗 Related
-
Designing APIs for Massive Datasets— takes pagination (fix #4) and field projection (fix #3) further for very large datasets: cursor-based pagination, sparse fields, DB-level filtering, caching, and compression. -
Caching Strategies— caching read-heavy query results to avoid re-paying the query cost at all.
Top comments (0)