Skip to content
←Data & Databases

// Data & Databases · 08

N+1 Queries

One query per rowleads toOne query for the set

Loading a list, then querying again for every item in it.

How it fits together

N+1 Queries structureBeforeAfterOrders endpointloops over ordersOrders DBordersorder_linesLines query x NWHERE order_id = 1WHERE order_id = 2WHERE order_id = 3... one per orderOrders endpointone query for ordersone batch for linesOrders DBordersorder_lines1 SELECT orders2 per order2 queries total
uses / callsfails / error path· numbered steps replay in order
  • Orders endpoint uses / calls Orders DB (1 SELECT orders)
  • Orders endpoint fails / error path Lines query x N (2 per order)
  • Orders endpoint uses / calls Orders DB (2 queries total)

Spot it

  • One page load fires 200 SELECTs
  • Response time grows with page size
  • Logs show the same query with different IDs

It costs

Loading up front fetches more at once, and wide joins can multiply rows.

In code

public sealed record OrderSummary(Guid Id, string CustomerName, int LineCount, decimal Total);​public sealed class OrderQueries(AppDbContext db){    public Task<List<OrderSummary>> RecentAsync(int take, CancellationToken ct) =>        db.Orders            .OrderByDescending(o => o.PlacedAt)            .Take(take)            .Select(o => new OrderSummary(                o.Id,                o.Customer.Name,                o.Lines.Count(),                o.Lines.Sum(l => l.Quantity * l.UnitPrice)))            .ToListAsync(ct);}
Go deeperThe full explanation and what to ask for · 1 min readExpand

N+1 hides in loops: one query fetches N parents, then code touches a navigation property or calls a repository per item, issuing N more. Invisible in development with ten rows, painful in production with a thousand.

Lazy-loading proxies in ORMs are a common cause; turn them off and load explicitly. Joining several collections in one query causes a cartesian explosion, so split it into one query per collection or, better, project only the fields the page needs. With raw SQL, fetch children in one batched query (= ANY(@ids) in PostgreSQL, IN (...) in SQL Server) and stitch them in memory.

Each extra query is a network round trip, so latency grows with the data. The cost often surfaces as database load far from the code that causes it, which is why query logging or tracing per request finds it faster than reading code.

Ask for it

  • eager loading
  • lazy loading
  • split query
  • projection
  • cartesian explosion
  • batched lookup (IN list)

The orders endpoint runs one query for orders, then one per order for its lines and customer. Turn on query logging to confirm the N+1, fix it with a projection (or eager loading, split into one query per table), and add a test that the query count stays constant.

Connects to