Performance bugs (N+1)

Accidentally running a database query inside a for-loop.

The idea

An N+1 Query Bug happens when your code fetches a list of N items (1 query), and then loops through those items, running another query for each one (N queries). If you have 100 users, that's 101 database queries!

Network latency makes this deadly. If every query takes 10ms, 101 queries take over a full second. We fix this by fetching the related data in bulk ahead of time (batching or JOINing), resulting in just 2 queries total.

We need to load 20 users and their profile pictures.

How it works (Fixing N+1)

# BAD: The N+1 Query
users = db.query("SELECT * FROM users LIMIT 20") # 1 query
for u in users:
    # Executes 20 times over the network!
    pic = db.query(f"SELECT * FROM pictures WHERE user_id={u.id}")
    u.picture = pic

# GOOD: Batched Query (IN clause)
users = db.query("SELECT * FROM users LIMIT 20") # Query 1
user_ids = [u.id for u in users]

# Executes exactly ONCE for all 20 users
pics = db.query(f"SELECT * FROM pictures WHERE user_id IN {user_ids}") # Query 2

# Link them up in memory (instant)
pic_map = {p.user_id: p for p in pics}
for u in users: u.picture = pic_map[u.id]