All topics
13 / 66
performance

N+1 Query Problem

Spot and eliminate the pattern of "1 query for the list + N queries for each item's related rows" that quietly destroys performance.

One query fetches a list. Then, as the code walks that list, one more query fires per item. One hundred orders become one hundred and one queries.

Asking the waiter to bring each ingredient separately, then making 50 trips. Or: order the dish; everything comes together.

Key Concepts

1
That is the N+1 problem, and it is the most common serious performance bug in ORM code.
2
Why it hides. Nothing in the source looks like a query:
3
    for (Order o : orders) {
        print(o.getCustomer().getName());   // a SELECT, every iteration
    }
4
getCustomer() reads like following a reference. With a lazily-mapped association it is a database round trip the first time it is touched.
getCustomer()
5
The ORM is doing exactly what it was configured to do. The loop simply does not show it.
6
Why it survives to production. Each query is genuinely fast — a primary-key lookup, perhaps 0.4 ms. With 5 test rows the page costs 2 ms and looks fine.
7
    development:  5 rows     ->   6 queries  ->    2 ms
    production:   500 rows   -> 501 queries  ->  200 ms
    plus 20 ms network latency per call      -> 10 seconds
8
The problem is not the query cost, it is the round trips. Latency multiplies by N, and only production has a real N.
9
The fix is to fetch together. In JPA or Hibernate, JOIN FETCH or an entity graph:
JOIN FETCH
10
    SELECT o FROM Order o JOIN FETCH o.customer WHERE o.status = 'OPEN'
11
Other ORMs name it differently — includes in Rails, prefetch_related in Django, Include in Entity Framework — and in raw SQL it is a join, or a second query with WHERE id IN (...).
includesprefetch_relatedIncludeWHERE id IN (...)
12
Two queries regardless of N is fine. The goal is a constant number of queries, not one.
13
Do not fix it by making everything eager. That trades one problem for the opposite: every screen now loads the whole object graph, including the parts it never shows.
14
Keep associations lazy by default, then fetch explicitly what each use case needs. Fetch strategy belongs to the query, not to the mapping.
15
How to detect it. Turn on query logging in development and read the log for a single page load. N+1 looks unmistakable: a burst of identical statements differing only in the id.
16
Better, assert it in a test. Count the queries a service call issues and fail if the count exceeds a fixed number. That catches a regression the moment somebody adds an innocent-looking getX() inside a loop, which is how these come back.
getX()
17
What the interviewer is probing.1. "What is the N+1 problem?" Probing: the definition. Stalls: "Too many queries." *Moves up:* one query for a list and then one per row for its related data, so a hundred rows become a hundred and one round trips.
18
2. "How do you detect it?" Probing: the method. Stalls: "Read the code." Moves up: query logging or an APM trace showing many near-identical queries per request — ORMs hide it in the code.
19
3. "How do you fix it?" Probing: the techniques. Stalls: "Cache it." Moves up: eager loading with a join, or a batched second query by id — both turn N+1 into one or two queries.
20
4. "When is a join the wrong fix?" Probing: the nuance. Stalls: "Never." Moves up: when it multiplies rows badly across several one-to-many relations — two batched queries beat a cartesian explosion.
Use cases:N+1ORMFetching