SQL Reference ยท Transactions and Performance
SQL N+1 query problem
Concept guide to the N+1 query problem: one query for a list plus one per row. See how joins, GROUP BY and batched IN queries replace the loop.
The N+1 problem happens when application code runs one query to fetch a list of N rows, then runs one more query for each of those rows to fetch related data: 1 + N queries in total. Each query is cheap on its own, but each also pays for a network round trip, parsing, planning and locking, so 200 small queries are often much slower than one slightly bigger one.
The problem lives in the application loop, not in SQL itself, and ORMs create it silently through "lazy loading". The cure is to ask the database for all the data at once and let it do the matching with a join, an aggregate or a single IN list.
-- Query 1: the list SELECT id, first_name FROM customers; -- Then, in a loop, once per customer (N more queries): SELECT COUNT(*) FROM orders WHERE customer_id = ?;
The loop, replaced by a join
One LEFT JOIN with GROUP BY answers the same question for every customer in a single round trip. LEFT JOIN keeps customers without orders, which the loop version also reports (with 0).
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
A plain join repeats the parent columns on every child row. Your code groups them back by author. That is still one query and far cheaper than one per author.
Batching with IN
Sometimes you already have the parent rows (from a cache or another service) and need the children for exactly those ids. Send all ids in one query instead of looping.
Press Run (or Ctrl + Enter) to see the result here.
Very long IN lists have limits (SQLite's default is 32,766 host parameters; SQL Server about 2,100 parameters; Oracle 1,000 expressions in a list), so batch in chunks of a few hundred or use a join to a temporary table.
Nested results in one query
When you want one row per parent with the children packed inside, aggregate them: GROUP_CONCAT/STRING_AGG for text, or a JSON aggregate for structured data.
Press Run (or Ctrl + Enter) to see the result here.
-- PostgreSQL SELECT a.name, jsonb_agg(b.title ORDER BY b.title) AS titles FROM authors a JOIN books b ON b.author_id = a.id GROUP BY a.id, a.name; -- MySQL SELECT a.name, JSON_ARRAYAGG(b.title) AS titles FROM authors a JOIN books b ON b.author_id = a.id GROUP BY a.id, a.name;
How to spot it
- A page that gets slower in proportion to the number of rows it shows.
- A query log with the same statement repeated dozens of times, differing only in one parameter.
- ORM code that touches a relation (
order.customer.name) inside a loop.
Most ORMs offer "eager loading" (include, with, select_related, JOIN FETCH) that issues a join or one extra IN query for all rows. Use it deliberately, and check the log to confirm the count of queries.
Usage notes
- Not always wrong. For a handful of rows on a local database, the loop is harmless. The cost grows with N and with network distance (a database in another data center turns 1 ms queries into 20 ms ones).
- Joins can multiply rows. Joining two one-to-many relations at once produces a row for every combination. Aggregate each side separately or use two queries.
- Aggregates in subqueries can avoid duplicate rows: join to a pre-aggregated derived table.
- Pagination. Fetch the page of parent ids first (with
LIMIT), then join the children for just those ids. - Writes have the same problem. Insert many rows with one multi-row
INSERTorINSERT ... SELECTinstead of one statement per row; see INSERT.