The N+1 Query Problem

The Idea

You fetch a list, then loop over it and query once per item. One query becomes N+1. Each trip is cheap on its own and ruinous in aggregate, because latency and planning cost apply every time. Fix it by fetching the related rows in one go — a join, or a single IN (...).

Real-World Example

A plumber drives to the merchant for one fitting, comes back, discovers the next joint needs a washer, and drives out again. The parts are trivial; the driving is the whole day. A picking list written before leaving turns eight trips into one.

The Code

import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""CREATE TABLE job (id INT, addr TEXT);
  CREATE TABLE part (job_id INT, name TEXT);
  INSERT INTO job VALUES (1,'Mill Lane'), (2,'Quay St'), (3,'Fern Rd');
  INSERT INTO part VALUES (1,'elbow'), (2,'washer'), (3,'valve');""")
sent = []
db.set_trace_callback(sent.append)          # count statements sent to SQLite
for (job_id,) in db.execute("SELECT id FROM job").fetchall():   # 1 query
    db.execute("SELECT name FROM part WHERE job_id = ?", (job_id,))  # +1 each
print(len(sent))   # 4  -> the N+1 pattern
sent.clear()
db.execute("SELECT addr, name FROM job JOIN part ON part.job_id = job.id")
print(len(sent))   # 1  -> one trip, all the parts

import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""CREATE TABLE job (id INT);
CREATE TABLE part (job_id INT, name TEXT);
INSERT INTO job VALUES (1), (2), (3), (4), (5);""")
sent = []
db.set_trace_callback(sent.append)
for (job_id,) in db.execute("SELECT id FROM job").fetchall():
  db.execute("SELECT name FROM part WHERE job_id = ?", (job_id,))
print(len(sent))