Rails Performance: 3 Tips For Removing N+1 Queries

You're here because your rails app is slow, and you either suspect or discovered it has n+1 queries causing performance issues. I've been there.

This article discusses what causes n+1 queries. It also covers 3 tips specific to rails apps for:

• How you can identify n+1 queries by checking your terminal output or by using the bullet gem
• Avoiding n+1 queries by eager loading associations (i.e. load all associations once instead of n+1 times)
• Avoiding n+1 queries by using .size instead of .count

Table of contents:

• What causes n+1 queries
• How you can identify n+1 queries
• How to eager load associations
• Using .size instead of .count

What causes n+1 queries?

N+1 queries are caused by code that loads children of a parent-child relationship (children are the many in a “one-to-many” association). Since most ORMs have lazy loading enabled by default, queries must be issued for the parent record (1 query), and for each child record (n queries).

Said another way, instead of loading all parent and child records at once, n+1 queries loads each record separately.

How you can identify n+1 queries

A. In your terminal:

If you are running your rails application in a local development environment, watch your terminal as your run the controller action (possibly by loading a web app page in your browser) you suspect causes n+1 queries.

Look out for terminal output that looks like the below:

...
Contact Load (1.1ms)  SELECT "contacts".* FROM "contacts" INNER JOIN "contacts_messages" ON "contacts"."id" = "contacts_messages"."contact_id" WHERE "contacts_messages"."message_id" = ? ORDER BY "contacts"."id" ASC LIMIT ?  [["message_id", 4], ["LIMIT", 1]]
  ↳ app/views/messages/_message.html.erb:4
...
Contact Load (0.1ms)  SELECT "contacts".* FROM "contacts" INNER JOIN "contacts_messages" ON "contacts"."id" = "contacts_messages"."contact_id" WHERE "contacts_messages"."message_id" = ? ORDER BY "contacts"."id" ASC LIMIT ?  [["message_id", 5], ["LIMIT", 1]]
  ↳ app/views/messages/_message.html.erb:4
...
Contact Load (0.2ms)  SELECT "contacts".* FROM "contacts" INNER JOIN "contacts_messages" ON "contacts"."id" = "contacts_messages"."contact_id" WHERE "contacts_messages"."message_id" = ? ORDER BY "contacts"."id" ASC LIMIT ?  [["message_id", 6], ["LIMIT", 1]]
  ↳ app/views/messages/_message.html.erb:4
...

As you can see above, a query is being made for Contact records (which have not been cached) multiple times. In the above instance, the contact records were children of a parent record, and they weren't eager loaded (more on eager loading below), causing queries for each child record to occur.

B. Using the bullet gem:

Bullet is designed to help you increase your application's performance by reducing queries made; it catches query performance issues in your code. It will watch your queries while you develop and notify you when you should add eager loading to avoid N+1 queries, when you're using eager loading that isn't needed, and when you should use counter cache.

If you decide to use it (which I recommend you do), use bullet in test, development, or another non-production mode.

I personally have configured bullet for development and testing; if BULLET=true rails test is run, my tests fail for n+1s, unused_eager_loading, and counter_cache. My bullet configuration is as follows:

Gemfile:

group :development, :test do
  gem 'bullet'
end

config/environments/development.rb:

config.after_initialize do
  Bullet.enable = true
  Bullet.rails_logger = true # logs violations
  Bullet.add_footer = true # adds footer to view with violation
end

config/environments/test.rb:

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

Appended to or required in test/test_helper.rb:

if ENV['BULLET']
  module MiniTestWithBullet
    def before_setup
      Bullet.start_request
      super if defined?(super)
    end

    def after_teardown
      super if defined?(super)
      Bullet.end_request
    end
  end

  class ActiveSupport::TestCase
    include MiniTestWithBullet
  end
end

How to eager load associations

You can avoid most n+1 queries in rails by simply eager loading associations. Eager loading allows you to load all of your associations (parent and children) once instead of n+1 times (which often happens with lazy loading, rails' default).

To eager load associations in rails, use the .includes method:

Message.includes(:contacts)
  .order(:scheduled_time)

# or

Blog.includes(articles: [{ comments: :guest }, :tags])
  .find(1)

As seen above, .includes allows nested association eager loading!

Using .size instead of .count

When calculating the number of child associations that exist, use .size instead of .count to avoid n+1 queries in rails.

Why?

When .count is called on an ActiveRecord::Relation, it executes a COUNT query on the database. .size, on the other hand, will call the .length method on an ActiveRecord::Relation if it is loaded (if it isn't loaded, it will call .count). See for yourself in the source code:

# File activerecord/lib/active_record/relation.rb, line 260
def size
  loaded? ? @records.length : count(:all)
end

Therefore, using .size instead of .count will avoid an extra database query each time it is called if your ActiveRecord::Relation is already (eager) loaded!

This is especially counter-intuitive, since .size, .count, and .length all behave the same when sent to an instance of Array in ruby. ¯\(ツ)/¯