Explain N+1 Query Problem in Databases

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.

The N+1 query problem is one of the most common and most damaging performance bugs in applications that use an ORM. The name describes the shape: one query fetches a list of N parent rows, and then, as the code touches each parent's related data, it fires one additional query per parent — N more queries. A page showing 100 orders with their customers quietly issues 101 queries instead of one or two, and because each is fast in isolation, the problem hides in development and only surfaces as latency under real data volumes.

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

Key Concepts

It arises because lazy loading makes the extra queries invisible in the code. With a lazily-mapped association, writing for (Order o : orders) { print(o.getCustomer().getName()); } looks like plain object navigation, but each getCustomer() triggers a separate SELECT the first time it is accessed. The ORM is doing exactly what it was told; the loop just doesn't reveal the database round trips. The fix is to fetch the related data together with the parents in a single (or a constant number of) queries: a JOIN FETCH or entity graph in JPA/Hibernate, eager loading or @EntityGraph annotations, includes/prefetch in other ORMs, or in raw SQL a join or a single WHERE id IN (...) batch lookup. The goal is to turn N+1 queries into one or two regardless of N.

for (Order o : orders) { print(o.getCustomer().getName()); }getCustomer()SELECTJOIN FETCH@EntityGraph

The skills interviewers probe are detection and prevention. You catch N+1 by watching the query log or an APM trace and noticing a burst of near-identical queries differing only by id, or by counting queries in a test. Prevention is about being deliberate with fetch strategy — defaulting associations to lazy to avoid over-fetching, then explicitly fetching what a given use case needs — rather than letting object navigation silently dictate the database access pattern.