Improving Rails Performance | Part 3 | N+1

In Part 1 and Part 2 we learned how to measure performance. With that knowledge in hand, we can now tackle one of the most common performance plagues found in a rails app.

For those who aren’t familiar with the N +1 Problem. It is a common anti-pattern. In which unoptimized code results in excessive, duplicate or unnecessary SQL Queries that negatively impact performance.

In rails its simplest form looks something like this:

(1..100).each do |id|
 User.find(id)
 //…
end

Did you see it? The code looks simple enough, what could go wrong?

Let’s break it down and assume that User is an active model record. If we were to run through this loop once, it would run User.find(1), which would result in the following SQL is generated and executed.

SELECT "users".* FROM "users" WHERE AND "users"."id" = 1

If we follow the example above and then do this for each number between 1 and 100. Then a similar piece of SQL is run for each id. (See below)

SELECT "users".* FROM "users" WHERE AND "users"."id" = 1
SELECT "users".* FROM "users" WHERE AND "users"."id" = 2
SELECT "users".* FROM "users" WHERE AND "users"."id" = 3
SELECT "users".* FROM "users" WHERE AND "users"."id" = 4
SELECT "users".* FROM "users" WHERE AND "users"."id" = 5
SELECT "users".* FROM "users" WHERE AND "users"."id" = 6
SELECT "users".* FROM "users" WHERE AND "users"."id" = 7
//… until id = 100

The problem with this approach is that each query comes with overhead. This overhead affects performance and escalates with the amount of data we are trying to load. Plainly put running a query once that returns 100 results is faster than running 100 queries with 1 result.

Batching

The first way to fix this problem is easy. We can batch our queries to load all the data we want up front. This involves using where() method instead of find().

With that strategy, we can rewrite:

(1..100).each do |id|
 User.find(id)
 //…
end

Like this:

User.where(id: [1..100]).each do |user|
 //…
end

The generated query is much more straightforward.

SELECT "users".* FROM "users" WHERE ("users"."id" BETWEEN 1 AND 100)

Keep in mind that the query generated can vary depending on the version of rails, and the flavor of SQL your using.

The query below is a variation of the same optimized query.

SELECT "users".* FROM "users" WHERE "users".id" IN (1,2,3,4,5,6,7,8, …. 98, 99, 100)

Don’t worry about which variation of the query is generated. The outcome is still the same, regardless of the length of query the outcome is still the same 1 Query for 100 Results.

With this small change, we eliminated the “N” from the N+1 Problem.

The where() method is where you should start your thinking when considering optimization. To avoid N+1 issues, you generally want to think about all the data you need up front and start determining the most effective queries.

Eager Loading

Batching is a simple fix but it’s far from a catch-all. You can still run into N+1 issues especially once you start working with models that have relationships with other models. For example, let’s say each User has 1 Firm. What would happen if we tried to access the Firm associated with a User model? Reusing the same batching code, we used above, and add a line where we access each user’s Firm.

Like this:

User.where(id: [1..100]).each do |user|
 user.firm
 //…
end

Will result in this SQL Output:

User Load (6.7ms) SELECT "users".* FROM "users" WHERE ("users"."id" BETWEEN 1 AND 100)
Firm Load (1.9ms) SELECT "firms".* FROM "firms" WHERE "firms"."id" = $1 LIMIT 1 [["id", 1]]
Firm Load (2.2ms) SELECT "firms".* FROM "firms" WHERE "firms"."id" = $1 LIMIT 1 [["id", 1]]
Firm Load (3.9ms) SELECT "firms".* FROM "firms" WHERE "firms"."id" = $1 LIMIT 1 [["id", 1]]
Firm Load (4.6ms) SELECT "firms".* FROM "firms" WHERE "firms"."id" = $1 LIMIT 1 [["id", 2]]
Firm Load (1.8ms) SELECT "firms".* FROM "firms" WHERE "firms"."id" = $1 LIMIT 1 [["id", 2]]
Firm Load (5.3ms) SELECT "firms".* FROM "firms" WHERE "firms"."id" = $1 LIMIT 1 [["id", 2]]
Firm Load (0.3ms) SELECT "firms".* FROM "firms" WHERE "firms"."id" = $1 LIMIT 1 [["id", 2]]
Firm Load (0.3ms) SELECT "firms".* FROM "firms" WHERE "firms"."id" = $1 LIMIT 1 [["id", 2]]
Firm Load (0.3ms) SELECT "firms".* FROM "firms" WHERE "firms"."id" = $1 LIMIT 1 [["id", 3]]
Firm Load (0.3ms) SELECT "firms".* FROM "firms" WHERE "firms"."id" = $1 LIMIT 1 [["id", 3]]
Firm Load (0.3ms) SELECT "firms".* FROM "firms" WHERE "firms"."id" = $1 LIMIT 1 [["id", 3]]
//… until end of loop

Our first line is what we expect, from batching our users.

SELECT "users".* FROM "users" WHERE ("users"."id" BETWEEN 1 AND 100)

However, since we are now calling each Firm individually for each User, we introduce the N+1 problem again. In this case, the problem is worse than you might think. Not only are we loading each firm one at a time, but the same Firm can belong to different users. Which results in repeated loaded and reloading of a Firm into memory unnecessarily. Thus, taking up precious resources and impacting our performance.

So how do we fix this?

Luckily every ActiveRecord comes with a handy includes() method. This method tells Rails to preload the association of an ActiveRecord.

It’s called eager loading, and it allows us to access a Firm associated with a User without needing to reload the same Firm.

Let’s write our previous query and add the includes(). Keep in mind that the includes() method needs an argument passed to it. The arguments you would pass to it would be the same symbols you use to describe an association in a Models. The same ones you use via belongs_to, has_many, has_one, and so on.

In this example, we are assuming the User model has a belongs_to :firm in the User model.

User.where(id: [1..100]).includes(:firm).each do |user|
 user.firm
end

Output

User Load (6.7ms) SELECT "users".* FROM "users" WHERE ("users"."id" BETWEEN 1 AND 100)
Firm Load (2.2ms) SELECT "firms".* FROM "firms" WHERE "firms"."id" IN (1, 2, 3, 4, 5)

You can see once we run it this time, N+1 is one and is replaced by a similar query to that of User. Rails is smart and depending on the need sometimes you’ll see a separate call, and other times Rails might use a JOIN, resulting in just 1 query, instead of two. Either way, our code is optimized.

Our example above looks pretty simplistic, but the includes() method is surprisingly versatile. You can nest associations, and much like the movie Inception, you can go many levels deep.

You could eagerly load an association a few levels deep. For example, if you wanted to load a model that is only associated with a Firm. You could still use the same includes() method and eager load nested associations and avoid the N+1. This topic gets complex fast, so for more information about eager loading, I recommend using the “Ruby on Rails Guide” it’s an excellent introduction into the using the includes() method.

However, the last thing you might consider when it comes to using eager loading is that there is memory cost in preloading hundreds or thousands of records. Most of the time you’ll be fine, but any site that generates or stores ton of data has to worry about its memory consumption.

In those cases, you’ll want to try a more selective approach by using select() method. This method allows us to load just the data you want selectively, but I’ll save that for a different article. You can read more about it here

Detecting the Problem

Learning to detect the performance problems in your apps is an essential skill for any developer. The most important thing for me is to pay attention to my logs while I’m testing. As you saw in our examples above, a telltale sign of the N+1 is the repeating or duplicate or similar SQL.

They are easy to spot because you’ll find them bundled together, or nested as a set of repeated queries. Pay particular attention to your logs when writing and testing a new endpoint and use benchmarks to ensure you’re getting a performance increase.

You can also use the “Bullet” gem. It does an excellent job of pointing out the N+1’s your app. It will also give you recommendations on where to use includes, and where to its best to remove them. It’s a valuable gem but it is not a catch-all, and you should always work on building up your ability.

For more about the Bullet Gem go to https://github.com/flyerhzm/bullet