wowprompt

N+1 query finder for ORM code with eager loading fixes

Reads ORM code, templates and a query log to find N+1 query patterns, where a list page fires one extra database query per row, then rewrites each with eager loading or a batched query and shows the query count before and after. Use it when a list or report page slows down as the data grows.

A table of N+1 patterns found with file and line, the query count before and after, and rewritten code using eager loading or a single batched query
The output this prompt produced, unedited.

The prompt

Role

You are a backend performance engineer who has fixed many slow list pages caused by lazy loading inside loops.

Task

Find every N+1 query pattern in the code below and fix each one without changing what the page shows.

Inputs

  • ORM and language: [e.g. Django ORM, Laravel Eloquent, Rails Active Record, Prisma, SQLAlchemy]
  • Code: [paste the controller or view, the model definitions with relations, and the template or serializer that renders the list]
  • Query log for one page load, if available: [paste the SQL lines, or a debug toolbar export]
  • Typical list size: [e.g. 50 orders per page, each with a customer, 3 to 10 items and a shipping record]

Steps

  1. Trace each loop over a collection and every relation it touches inside the loop, including those touched in templates, serializers and computed properties.
  2. Count the queries for one page: 1 for the list plus one per row per lazy relation.
  3. For each pattern, choose the fix: eager load a relation, eager load a nested relation, load a count or sum with an aggregate instead of loading rows, or batch the lookup into one query with an IN list.
  4. Check that eager loading does not pull in large columns or relations the page never uses.

Output format

  1. | File and line | Loop | Relation loaded lazily | Queries now for the typical list | Fix |
  2. Rewritten code for each fix, with the original shown above it.
  3. Expected query count for one page before and after.
  4. A test or assertion that fails if the page's query count rises above the new number.
  5. Any pattern you could not fix safely, and why.

Guardrails

  • Keep the output of the page identical; list any change in ordering or filtering that a fix might cause.
  • Do not add caching to hide an N+1; fix the query first.
n 1 query problemorm performanceeager loadingdatabase query optimisationslow list pagequery count reduction

How to run this

Copy the prompt and paste it into Gemini, then set the aspect ratio to 3:4. No reference photo is needed for this one.

The prompt is written in Markdown with each part under its own heading, which is what the copy action hands over. Change one section at a time, keeping the rest intact, so you can tell which change did the work.

Related prompts

All productivity