Faster Rails: Eliminating N+1 queries

· 9 Aug 2017 · Semaphore Engineering Blog

Faster Rails: Eliminating N+1 queries

This article is part of our Faster Rails series. Check out the previous article about index creation on large tables.

Rails does not scale well – this argument is often used to downplay the worth of the language and the framework. Yet, many businesses from small startups to platforms with millions of users use it as the backbone of their operations. A good question to ask ourselves is whether Rails simply can't scale, or if the issue is hidden somewhere deeper.

Rails is easy is to learn, but to achieve mastery we need to invest just as much time as we would invest in any other framework or language. We can't expect that our issues will magically disappear just because we switched to a faster language. A slow algorithm is as slow in C++ as in Ruby. Bad architecture cripples our productivity just as badly in Haskell as in Ruby.

From my experience, when developers start complaining that Rails can't scale, they are hitting the usual conundrum – the application worked just fine with a small number of records/requests, but now that our business is bigger, our code is struggling to keep up.

One of the usual suspects of increasingly slow performance are N+1 queries.

Understanding N+1 Database Queries

Let's observe an example, with two connected models:

class Branch < ActiveRecord::Base
  has_many :builds
end

class Build < ActiveRecord::Base
  belongs_to :branch
end

If we wanted to list the latest builds, we could write the following code:

builds = Build.order(:finished_at).limit(10)

builds.each do |build|
  puts "#{build.branch.name} build number #{build.number}"
end

The above code works, but it makes far too many independent database queries:

Build Load (1.7ms) SELECT "builds".* FROM "builds" ORDER BY "builds"."finished_at" ASC LIMIT 10

Branch Load (0.4ms) SELECT "branches".* FROM "branches" WHERE "branches"."id" = $1 LIMIT 1 [["id", 11]]
Branch Load (0.8ms) SELECT "branches".* FROM "branches" WHERE "branches"."id" = $1 LIMIT 1 [["id", 13]]
Branch Load (0.3ms) SELECT "branches".* FROM "branches" WHERE "branches"."id" = $1 LIMIT 1 [["id", 15]]
Branch Load (0.3ms) SELECT "branches".* FROM "branches" WHERE "branches"."id" = $1 LIMIT 1 [["id", 17]]
Branch Load (0.2ms) SELECT "branches".* FROM "branches" WHERE "branches"."id" = $1 LIMIT 1 [["id", 19]]
Branch Load (0.3ms) SELECT "branches".* FROM "branches" WHERE "branches"."id" = $1 LIMIT 1 [["id", 111]]
Branch Load (0.3ms) SELECT "branches".* FROM "branches" WHERE "branches"."id" = $1 LIMIT 1 [["id", 113]]
Branch Load (0.3ms) SELECT "branches".* FROM "branches" WHERE "branches"."id" = $1 LIMIT 1 [["id", 115]]
Branch Load (0.3ms) SELECT "branches".* FROM "branches" WHERE "branches"."id" = $1 LIMIT 1 [["id", 117]]
Branch Load (0.3ms) SELECT "branches".* FROM "branches" WHERE "branches"."id" = $1 LIMIT 1 [["id", 119]]

That is 11 independent database queries. One query for loading the builds, and N queries for loading the branch in each iteration, hence the name N+1 queries. This loading approach is very inefficient. If we have 1000 builds, we'll need to connect to the database 1001 times. Remote database connections are not free, and each connection introduces a heavy performance penalty.

Eager Loading in Rails

To improve the performance of the previous example, we need to reduce the number of independent database queries. In Rails, this is done by eager loading associated relations, or, in other words, collecting related data with only one query.

This is very easy in Rails. In our example, all we need to do is attach .includes(:branches) to our existing database query:

builds = Build.order(:finished_at).includes(:branches).limit(10)

builds.each do |build|
  puts "#{build.branch.name} build number #{build.number}"
end

This time, the query count is much better. We only used two queries to load all the data into memory — one for loading the builds, and another one for loading the associated branches:

Build Load (0.5ms) SELECT "builds".* FROM "builds" ORDER BY "builds"."finished_at" ASC LIMIT 10
Branch Load (0.5ms) SELECT "branches".* FROM "branches" WHERE "branches"."id" IN (11, 13, 15, 17, 19, 111, 113, 115, 117, 119)

Now, even with 10,000 builds, our application would only use 2 database queries.

For comparison, the time necessary to load and display 1000 builds is 923.6 milliseconds without eager loading, and only 8.3 milliseconds with eager loading. That is almost 110 times faster. A huge difference.

Eliminating N+1 Queries with the Bullet Gem

The first step toward eliminating N+1 queries is to make sure that your engineering team understands the issue, and is aware of newly introduced queries that can be optimized.

Reviewing pull requests and sharing knowledge is a good way to start, but even with great attention to detail, errors can still slip into production code. To combat this, we always strive to automate and let the CI step notify us about potential issues. Bullet is an excellent tool for this purpose. It tracks and reports inefficient queries.

To set up Bullet on your Rails project, first add it to your Gemfile:

gem "bullet"

Then, add the following in your app/environments/test.rb to enable Bullet in test mode:

config.after_initialize do
  Bullet.enable = true
  Bullet.bullet_logger = true
end

Finally, wrap your RSpec in a bullet wrapper to catch and log N+1 queries:

# spec/spec_helper.rb

RSpec.configure do |config|
  if Bullet.enable?
    config.before(:each) { Bullet.start_request }
    config.after(:each)  { Bullet.end_request }
  end
end

With the above setup, every N+1 query will be logged in the log/bullet.log log file. For example, our example with builds and branches will produce:

USE eager loading detected
  Build => [:branch]
  Add to your finder: :includes => [:branch]

On Semaphore, we like to display our log/bullet.log in our after jobs.

Bullet on Semaphore

Aggressive N+1 Prevention

Pushing the previous setup even further, we can tell Bullet to raise an exception, and fail our continuous integration build on Semaphore, when it encounters a slow query in the tests.

# config/environments/test.rb

config.after_initialize do
  Bullet.enable = true
  Bullet.bullet_logger = true
  Bullet.raise = true # raise an error if an n+1 query occurs
end

This will produce red builds if someone accidentally introduces an N+1 query in the code. By raising an exception and failing your builds, we can almost guarantee that no N+1 query will enter production code.

Eliminating N+1 Queries From Large Projects

Unfortunately, on larger projects that already have multiple slow queries, the introduction of the aggressive N+1 strategy from the previous section is not viable. This would break all CI builds until all the issues are fixed.

The best approach in this case is to collect a list of problematic queries, blacklist them, and run bullet only on clean and new code. This way, you can enable aggressive bullet inspections immediately and stop the introduction of new slow queries.

As a next step, you should strive to eliminate slow queries from your blacklist, one by one, making your project faster and faster.

How Do You Deal With N+1 Queries?

I would love to hear some of your tips on dealing with N+1 queries. Does your organization have a strong policy for eliminating them, or do you solve performance issues as they emerge?

Feel free to share tips in the comments below. Also, if you found this article useful, you can share it so others can find it as well.

At Semaphore, we're on a mission to make continuous integration fast and easy. If your Rails test suite takes ages to finish, you can now automatically parallelize it with our new feature, Semaphore Boosters, and cut its runtime down to just a few minutes. Spend less time on testing, and focus on shipping.

Happy building!

comments powered by Disqus
Newsletter

Occasional lightweight product and blog updates. Unsubscribe at any time.

© 2009-2017 Rendered Text. All rights reserved. Terms of Service, Privacy policy, Security.