Stop 08 · ORMs & Query BuildersTopics · 9/10

Query Optimization

In short

Your API is fast on your laptop and crawls in production. Very often the ORM is quietly firing hundreds of tiny queries. Learn to spot it and fix it.

Identifying and fixing slow ORM-generated queries — often caused by the classic 'N+1 query problem' where an ORM issues far more queries than necessary.

The blob rides a scooter with one big bag of parcels while a tired twin makes many tiny trips.

Think of it like this

Ten trips for ten parcels

A delivery boy who rides back to the restaurant for every single parcel will take all evening. One who packs all ten into one bag finishes in a single trip. ORMs can make the same mistake: one query per row, instead of one query for all of them.

Watch it happen

The N+1 problem and its fix

API

Postgres

SELECT * FROM posts

1/6Load 50 posts for the feed. That's one query.

The big ideas

What you need to know

Spot N+1

Log every query in development. A long run of near-identical SELECTs means a loop is querying.

Load relations together

Use include in Prisma, relations in TypeORM or populate in Mongoose to batch them.

Select only what you need

Fetch the columns the screen uses. Pulling big text columns for a list page wastes time.

Index and EXPLAIN

Postgres doesn't index foreign key columns automatically. Check slow queries with EXPLAIN and add what's missing.

See it in code

Walk through the code

1// N+1: 1 query for posts, then 1 more per post2const posts = await prisma.post.findMany({ take: 50 });3for (const post of posts) {4  const author = await prisma.user.findUnique({ where: { id: post.authorId } });5  console.log(post.title, author?.name);6}7 8// Fixed: authors load together, and only the fields we show9const feed = await prisma.post.findMany({10  take: 50,11  select: {12    id: true,13    title: true,14    author: { select: { name: true } },15  },16});

1/3One query loads 50 posts.

Turn on new PrismaClient({ log: ['query'] }) and count the queries before and after the fix.

Save yourself the pain

Common mistakes

Oops

Calling the database inside a loop over results.

Do this instead

Fetch related data up front with include or a join, or collect the ids and query once with IN.

Oops

Blaming the ORM and rewriting everything in raw SQL.

Do this instead

Most slowness is N+1, missing indexes or over-fetching. Fix those first; use raw SQL only for the rare hard query.

Your mission

Hunt an N+1

~25 min

Quick check

A page lists 20 orders with each customer's name, and your logs show 21 queries. What's going on?

Go deeper

1Deliberately trigger an N+1 query problem, then fix it with eager loading/includes.
2Log your ORM's generated SQL and review it for a real endpoint.
3Learn when dropping to a raw query is the right call for a performance-critical path.