Understanding and Avoiding N + 1 Queries

As a backend developer, you’re likely to feel like the best developer in the room when your server is fast, allowing you to fetch and use data in record time. It is a power you have, but the smallest errors with your logic and in how you handle the database can completely change that. One way this could happen is by unintentionally implementing something known as an N + 1 Query. In this article, you’ll find out what this is and how to avoid it in your applications.

The Dreaded N + 1 Problem

Imagine you’re writing a program where you have to query the database for users and their hobbies. For each user, you’ll have to return their hobbies. This may seem simple enough at first glance. You could just query the database for each user, and under each user, get their hobbies. I imagine the query may look something like:

SELECT * FROM users;
SELECT * FROM hobbies where user_id = ?;

This is an intuitive approach, and in plain English, it seems to make sense, but let’s zoom in on what you’re really doing here. You run a single query when you select all users, but when you select hobbies from each user, you’re running a large number of queries and tasking the database heavily. Imagine there are 200 users. For each user, you’re running a query to select the user’s hobbies. Adding to the first query to select all users, you run about 201 queries, hence the name N + 1 queries, since you’re running N queries plus the first query to select all users. At this point, the database becomes so slow because it is running so many queries at once. Imagine having to stuff your face with a large bowl of food at the same time!

This could lead to some serious risks. The server could become overloaded and crash, response would become painfully slow, and performance would become so bad with increasing load. Imagine you get a thousand plus more users, say 1800 users. That would be 2001 queries. For these reasons, N + 1 queries are best avoided. Unfortunately for many new developers, when testing with just a few users, it may be difficult to point out the magnitude of this problem, which may make it seem negligible when it really isn’t.

N + 1 Queries in ORMs

Unfortunately, today, N + 1 problems do not necessarily show up in just raw SQL. In some situations, they may hide in plain sight in ORMs.

ORM stands for Object Relational Mapping. ORMs are used to help systems written in different languages communicate with each other. Think of them like quite sophisticated dictionaries. They help to transfer data stored in relational databases into objects that are more easily interpreted and used in applications. To observe the N + 1 problem in ORMs, we would use Sequelize to demonstrate.

const users = await User.findAll();

for (const user of users) {
  const hobbies = await Hobby.findAll({
    where: { userId: user.id }
  });
}

This may look innocent enough until you look carefully. In this situation, you’re repeating the same mistake as before. For each user (using the for loop), you’re retrieving all the hobbies of the user where the user_id matches.

Solutions to N + 1 Queries

We now know what the N + 1 problem is, but how do we avoid and fix it when it shows up? There are a few practical ways to deal with this problem.

Using Batch Queries

Instead of fetching the users’ hobbies one by one, it is more efficient to run batch queries. This way, instead of the about 200 requests we ran initially, just two requests would accomplish the same task.

SELECT * FROM hobbies WHERE user_id IN (5,6,3,2,1);

Using JOINS to fetch data

Instead of multiple queries, you could also use JOINS to run a single query and fetch the data.

SELECT users.*, hobbies.*
FROM users
LEFT JOIN hobbies ON users.id = hobbies.user_id;

Eager Loading Approach for ORMs:

This is a fix for N + 1 queries in ORMs. Eager loading is you telling the ORM to fetch the related data together with the main query, so everything comes back in one or two queries, irrespective of how many records there are. Most ORMs handle this quite well with something like an include or prefetch option. For sequelize:

const users = await User.findAll({
  include: [{
    model: Hobby,
    as: 'hobbies'
  }]
});

You can, of course, read up on the documentation for your preferred ORM and implement it.

Conclusion

Applying the solutions listed above would help you avoid N + 1 problems in the database calls of your application. It would also certainly help to be very familiar with databases so you can write cleaner queries. Now that you can identify them and fix them, you’ll be sure to write your most efficient queries yet.