liveBook · Manning

N + 1 query problem

As explained in Grokking Software Architecture

A database performance problem in which one query retrieves a collection and an additional query retrieves related data for each item.

An endpoint can feel quick with test data, then bog down as users and records multiply. The N + 1 problem happens when one query fetches a collection and another query fetches related data for each item: 50 orders plus 50 user lookups means 51 database calls. section 6.8.1

The trap often hides behind ORM property access. In a loop, reading order.User.Name can trigger a separate query for each order when lazy loading is enabled. With 1,000 users, fetching each user's orders this way can mean thousands of network round-trips, high database CPU, and slow responses. section 6.8.1 section 8.3.2

Avoid the trap by making data access deliberate:

  • Fetch related records in a batch instead of triggering a query for each item. section 6.8.1 section 8.3.2
  • Use eager loading when the response needs related data; for example, Entity Framework can use Include(o => o.User) to fetch orders and users together. section 6.8.1 section 8.10
  • Treat property access that reaches external data as I/O, not as a harmless in-memory lookup. section 8.3.2
Figure 6.13 The N+1 Performance Trap: Fetching related child records inside a loop cripples your database with excessive network chatter. The architectural fix is to batch those requests into a single trip using Eager Loading or a DataLoader.

The figure contrasts fetching child records inside a loop with batching requests through eager loading or a DataLoader. section 6.8.1

How Grokking Software Architecture covers it

How deep it goes
Defines the query pattern, traces lazy loading through loops, and names eager loading as a fix; defers deeper ORM treatment.
Examples
Uses 50 orders producing 51 calls, 1,000 users triggering thousands of round-trips, and an Entity Framework Include example.
What it assumes
Introduces an ORM as a translator between objects and relational tables, while deferring deeper ORM treatment.
What you can do after
Apply eager loading or DataLoader batching to fetch related records without issuing a query for every item.
How it connects
Connects object-relational mappers, lazy loading, eager loading, REST, GraphQL, and database query batching.

Also explained in

Compare how 7 books explain N + 1 query problem →

Related concepts

This explainer was generated by Manning's knowledge graph from the text of Grokking Software Architecture. The section links point to the parts of the book it draws on.