Performance and N+1 Queries: Explained, Spotted, and Solved

Today, we’ll dive into N+1 queries⁠—what they are, how to spot them, why they have such an impact, and how to solve them.

Why It Matters

When you run into performance issues in your app that affect user experience, or when things break, you'll probably look at the timeline of the requests in your APM. You might see a large part of the timeline being used by a single, repeated query.

N+1 queries can be quite impactful but are usually easy to spot. In this article, we’ll go over ways to find, fix and prevent slowdowns caused by N+1 queries.

The N+1 Antipattern In Short

The N+1 query antipattern happens when a query is executed for every result of a previous query.

The query count is N + 1, with N being the number of queries for every result of the initial query.
If that initial query has one result, N+1 = 2.

If it has 1000 results, N+1 = 1001 queries.

An N+1 Query Isn't That Hard to Spot (with AppSignal 😉)

We’ve written about N+1 before, but today, we wanted to write a post that is language agnostic because we’ve just released a way to find N+1 queries more easily. So, if a query is slow because of an N+1 query, it will stand out even more.

We were already tracking N+1 queries in the event timeline, but we've added two improvements that help you find these opportunities for improvement more quickly.

First, the performance issues overview now includes an N+1 label. This indicates that there are queries that might need optimization. Second, we've added a box with a warning and a short explanation of what N+1 queries are.

Let’s go back and look at what causes an N+1 query.

What Is Lazy Loading, and Which ORMs Default to It?

Loading relationships from a database can roughly be split into two approaches. First, lazy loading will only load data from your database when needed. Then, there’s eager loading, which loads all data right away. We’ll show you what that means.

If you use an ORM, this is where you set lazy or eager loading. Looking at different languages we are familiar with:

• In Elixir, things are set explicitly with Ecto (not an ORM really).
• In Node, TypeORM, the most popular ORM, doesn't do lazy loading by default.
• In Ruby, ActiveRecord by default uses lazy loading. This makes sense because, in Ruby on Rails, things are often implicit: convention over configuration. But it also means the N+1 anti-pattern is something you can easily stumble into when using ActiveRecord.

How It All Starts: When Fast, Easy and Lazy Are Nice

Let's dive in with a specific example, where we can see the pattern emerge and understand its impact. Because we like Stroopwafels, the example will be an imaginary Cookie webshop. In this imaginary webshop example, each cookie can have any number of toppings.

We will start with the case where a page shows all cookies, and allows you to navigate to a cookie detail page to see all the available toppings.

When we lazy load the data on the overview page, we loop over every cookie and only load their toppings later, at the point where we need them. On a detail page, you will load one cookie, and all of the toppings it has. Your ORM will do 2 queries to show these. To keep the explanation language-agnostic, we’ll just show the resulting queries.

SELECT "cookies".* FROM "cookies" WHERE "cookies"."id" = 1 LIMIT 1
SELECT "toppings".* FROM "toppings" WHERE "toppings"."cookie_id" = 1

When a Thing Starts Going Bad

When you want to not only list all cookies, but ALSO show all the toppings for each cookie, AND you lazy load, N+1 rears it’s ugly head. Because we lazy load, a query is executed for every result of a previous query.

This case returns all 3 cookies from the database and each of their toppings. This leads to 4 queries. Again, we just show the queries to make this applicable across languages:

SELECT "cookies".* FROM "cookies"
SELECT "toppings".* FROM "toppings" WHERE "toppings"."cookie_id" = 1
SELECT "toppings".* FROM "toppings" WHERE "toppings"."cookie_id" = 2
SELECT "toppings".* FROM "toppings" WHERE "toppings"."cookie_id" = 3

How Much Is N+1?

We started with rendering a view for 1 cookie and its toppings. All was well, and it led to 2 queries. The second case rendering a view with all cookies and all toppings led to 4 queries.

Looking at the first query, this is executed by the explicit call to cookie.all in the controller, which finds all cookies. Queries 2 to 4 are lazily executed while we loop through all the cookies.

This results in the number of queries being N+1.

• N here is the number of cookies (yum), and the plus one is the first (explicit) query that fetched all the cookies.
• It does one query, plus one, for each of the results in the first query.
• Because we have 3 cookies here, N = 3 and it leads to N + 1 = 3 + 1 = 4 queries.

With 3 cookies, this probably won’t lead to any performance issues, but if we had 1000, we can predict it would lead to 1001 queries. Ouch. Perhaps there IS such a thing as too many stroopwafels.

How Much Difference Does It Make?

OK, we ate all of the cookies by now. But we timed how much time that took. I mean, how long the queries took 😉. We used a Ruby on Rails with ActiveRecord setup for this example, but the differences are negligible for any language. When we have 3 cookies, eager eating loading was 12% faster than lazy loading. With 10 cookies, the difference was already almost 60%. If we had 1000 cookies, the difference would be close to 80%, 58 ms, against a whopping 290 ms in our example.

How to Solve It: Eagerness

The way to solve this is to use eager loading. By preloading the toppings, when we show all cookies and their toppings on a page again, the query count drops back to 2, even if the number of cookies increases to 1000. The query count is 2 because the second query depends on the data from the first one to know which toppings to fetch.

SELECT "cookies".* FROM "cookies"
SELECT "toppings".* FROM "toppings" WHERE "toppings"."cookie_id" IN (1, 2, 3)

Now What?

Depending on your state of mind, grab some cookies. Or find out more about AppSignal and setup an account. If you ask for stroopwafels, and we will send you some. If you are already using AppSignal, go and see whether you can solve some N+1 issues.