Contexto y CódigoSoftware development and artificial intelligence
Español

Your app is not slow: it runs 300 queries

Leonardo Gurgitano4 minLeer en español

Close-up of many small connections on a machine, in low light.
Close-up of many small connections on a machine, in low light.

One screen in your application takes four seconds to load. You look at the server: the processor is idle, there is memory to spare. You look at the database: every query takes two milliseconds, none of them is slow.

Everything is fine, and yet it takes four seconds.

What is happening, almost certainly, is that the screen is running three hundred two-millisecond queries. None of them is slow. Together they are six hundred milliseconds of database, plus three hundred round trips over the network, plus three hundred times building and tearing down a result.

This problem has a name, it shows up in every language and every framework, and once you know how to recognise it you will see it everywhere.

What the problem looks like

You want to show the latest twenty articles with the name of their author. You write the obvious thing:

const articles = await db.articles.findLatest(20);

for (const article of articles) {
  article.author = await db.authors.findById(article.author_id);
}

It reads well. It does what it says. And it runs twenty-one queries: one to fetch the articles, and one more per article to fetch its author.

With twenty articles you barely notice. The day the screen shows two hundred, that is two hundred and one queries and the page takes three seconds.

It is called the N+1 problem: one initial query plus one per result.

Why it is so hard to see

Because the code that produces it is the most natural code to write. Nobody writes a loop with queries inside on purpose: they write “for each article, get me its author”, which is exactly how you think about the problem.

And because very often the query is not visible. With a tool that maps objects to tables, this is the same thing:

for (const article of articles) {
  console.log(article.author.name);   // ← there is a query here
}

That .author looks like a field on the object. It is a database query dressed up as a property access. It is called lazy loading, and it is convenient right up until you are inside a loop.

How to spot it in two minutes

You do not need special tools. Count the queries.

Almost every data access library can log each query. Turn it on in development and load the screen:

// Example with Prisma; the equivalent exists everywhere
const db = new PrismaClient({ log: ['query'] });

Then look at the number. The question that gives it away: does the number of queries grow when there is more data on screen? If showing twenty articles runs twenty-one queries and showing forty runs forty-one, there it is.

A healthy screen runs a fixed number of queries, no matter how many items it displays.

How it is fixed

By fetching everything together. There are two ways, depending on what you have.

If you use a mapping tool, there is almost always an option to include the related data in the same query:

// Prisma
const articles = await db.article.findMany({
  take: 20,
  include: { author: true },      // ← a single query
});

Other tools call it something else but it is the same thing: includes in Rails, Include in Entity Framework, joinedload in SQLAlchemy, select_related in Django.

If you write SQL by hand, a JOIN:

SELECT a.*, au.name AS author_name
FROM   articles a
JOIN   authors au ON au.id = a.author_id
ORDER  BY a.published_at DESC
LIMIT  20;

From twenty-one queries down to one.

When the JOIN does not help

There is one case where fetching everything together makes things worse: when each article has many related items. An article with fifty comments, in a JOIN, is repeated fifty times in the result. With twenty articles that is a thousand rows to display twenty things.

There the answer is two queries, not twenty-one:

const articles = await db.articles.findLatest(20);
const ids = articles.map((a) => a.id);

// A single query for ALL the comments of ALL the articles
const comments = await db.comments.findByArticles(ids);

// And they are grouped in memory, which is extremely fast
const byArticle = new Map();
for (const c of comments) {
  if (!byArticle.has(c.article_id)) byArticle.set(c.article_id, []);
  byArticle.get(c.article_id).push(c);
}

Two queries for any number of articles. This pattern is called batch loading, and it is what the mapping tools do internally when you ask them to include a to-many relation.

How to stop it coming back

The problem always reappears, because the code that causes it is the natural code. Two habits that help:

When reviewing code, look for queries inside loops. It is the most reliable signal. Any await to the database inside a for or a map deserves a second look.

Set a limit in development. Many frameworks let you warn when a request goes over a certain number of queries:

if (queries > 25) {
  console.warn(`⚠ ${queries} queries in ${route}`);
}

Twenty-five is arbitrary. The point is not the exact number: it is that the problem tells you about itself while you are writing the code, instead of you finding out when a user complains that the screen is slow.

Comments