I’m trying to understand how teams deal with N+1 queries in practice.
The pattern I mean: a page loads 20 orders and, for each one, fetches the customer on its own.
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 -- 1 query
SELECT * FROM customers WHERE id = ? -- x 20
That’s 21 queries instead of 1 or 2. If each round trip is around 2 ms (an assumption), it’s about 42 ms against 2 to 4 ms with a join or a batched IN query. With 200 rows it’s 201 queries, so the cost grows with page size.
What I know so far: a join or a batched query fixes it, but a join that loads a collection multiplies rows, and combining it with pagination can force some ORMs to paginate in memory. Eager loading everywhere can also load data you don’t use.
My questions:
- Do you assert query counts in tests, and if so with what tooling?
- Do you rely on ORM statistics or debug logging, or on production monitoring?
- At what point do you consider it worth fixing: any time you see it, or only after measuring?
- Have you found cases where the “fix” made things worse?
If I’ve got something wrong here, please correct me.