The N+1 Query Problem
SQL: lesson 10 of 15
One query for the list, then one more for every single row.
Lesson 10 of 15 · 5 min
The N+1 Query Problem
Step 1 of 11
You fetch a list, then loop over it and query once per item. One query becomes N+1.
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 partsYour turn
What does this print?
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))Mini quiz
1 / 3