How to find and fix N+1 queries

An N+1 query is one query to load a list, then one more query for each item in it. With ten products in your test database the page feels instant. With ten thousand in production it sends ten thousand and one queries, and the page times out on the day you get traffic. Reviews miss it because the loop and the query usually sit in different places: the loop in a component, the query inside a helper.

What it looks like

Prisma: one query for orders, then one per order

const orders = await prisma.order.findMany({ where: { shopId } });
for (const order of orders) {
  order.customer = await prisma.customer.findUnique({ where: { id: order.customerId } });
}

Prisma: two queries in total, whatever the number of orders

const orders = await prisma.order.findMany({
  where: { shopId },
  include: { customer: true },
});

When there is no relation to include, collect the IDs and load them in one query with where: { id: { in: ids } }, then match them up in memory.

The same fix in other frameworks

Django

# ForeignKey / OneToOne: a join
orders = Order.objects.filter(shop=shop).select_related("customer")
# ManyToMany / reverse ForeignKey: one extra query for all rows
orders = Order.objects.filter(shop=shop).prefetch_related("items")

Rails

orders = Order.where(shop: shop).includes(:customer, :items)

Laravel

$orders = Order::where('shop_id', $shopId)->with(['customer', 'items'])->get();

Plain SQL

select o.*, c.name as customer_name
from orders o
join customers c on c.id = o.customer_id
where o.shop_id = $1;

Catch it with a test

Test it: Turn on query logging in development (log: ['query'] in Prisma, django.db.backends at DEBUG, the Rails log, or DB::listen in Laravel) and load the page. If the same query repeats with only the ID changing, you have an N+1.

Test it: Seed 50 rows and assert the number of queries in a test. Django has assertNumQueries; in Rails the bullet gem raises on N+1 in tests. The count must stay the same with 5 rows and with 50.

How Zelsis catches this

Zelsis flags database calls made inside a loop over query results in each pull request, so the slow page is caught before it merges instead of on the day you get traffic.

DB-PERF-01ORM query inside a loop (N+1)

Check your own repo

Public repositories scan free. Paste a GitHub URL on the dashboard and every finding comes with the file, the line and a fix.

Scan a repository free