Español

Grep AI-Written Data Fetching for the N+1 Query

There's a one-line bug making your API fifty times slower.

It passes review because each line looks reasonable. You fetch a list of things, then loop over them to enrich each one — and somewhere in that loop is a database call. Fetch 50 orders, then query the customer for each order: that's 1 + 50 = 51 round trips where 1 or 2 would do. This is the N+1 query problem, and AI assistants produce it constantly because writing "query inside the loop" is the most obvious way to express "get the related data."

Why AI writes N+1 so often

The N+1 pattern is the locally simplest code. Ask for "attach each post's author" and the natural completion is a loop with a lookup inside:

posts = db.query("SELECT * FROM posts WHERE blog_id = ?", blog_id)  # 1 query
for post in posts:
    # +1 query per post — this is the N
    post.author = db.query("SELECT * FROM users WHERE id = ?", post.author_id)

Each line is correct in isolation, so nothing looks wrong in the diff. The cost only appears at scale: with 500 posts it's 501 queries, each with its own network round trip, and the endpoint that was snappy on your 10-row test database melts on real data.

Grep before you merge

Make it a review reflex. Before merging AI-written data-fetching code, scan for a database or network call sitting inside a loop.

# rough heuristic: a query/find/fetch call near loop bodies
grep -rnE '(for |forEach|map\()' src | ...   # then eyeball the loop bodies
grep -rnE '\.(query|find|fetch|get|select)\(' src

No regex catches every case, so the real check is reading each loop and asking: does this loop body talk to the database or another service? If yes, that's your N+1.

Fix it with one round trip

Replace the per-row lookup with a single query that pulls the related data at once — a JOIN, a WHERE id IN (...), or your ORM's eager-loading hook:

# SQL: one query with a join
rows = db.query("""
  SELECT posts.*, users.name AS author_name
  FROM posts JOIN users ON users.id = posts.author_id
  WHERE posts.blog_id = ?""", blog_id)

# ORMs: eager-load the relation
# SQLAlchemy
posts = session.query(Post).options(selectinload(Post.author)).all()
# Django
posts = Post.objects.select_related("author").all()
# Prisma
posts = await prisma.post.find_many(include={"author": True})

One round trip instead of hundreds. The endpoint stops falling over under real data, and you caught it in review instead of in a production incident.

Power moves

• Log query counts in dev. Tools like Django Debug Toolbar, nplusone, or Laravel Telescope count queries per request and scream when a page fires hundreds. Wire one in so N+1 is visible before review.
• Test at realistic row counts. N+1 is invisible on a 10-row fixture. Seed enough data that a bad loop shows up as latency in tests.
• Prefer IN over N lookups. When you can't join, collect the IDs and fetch them in one WHERE id IN (...) query, then stitch in memory.
• Watch for hidden N+1 in serializers and GraphQL resolvers. A field resolver that loads a relation per object is the same bug wearing a different hat; batch it with a DataLoader.

Resources

• What is the N+1 query problem — Prisma docs
• SQLAlchemy — relationship loading techniques (eager loading)
• Django — select_related and prefetch_related
• GraphQL DataLoader — batching to solve N+1