Faster Rails: Eliminating N+1 queries

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.

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

Bullet is an excellent tool to track and report 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]