Remove N+1 queries in your Ruby on Rails app

Introduction

Ever wondered why your page loads slower than it should when you do simple data representation or iteration over a bunch of data? One answer might be that you have the N+1 problem which slows your page significantly. But what is that N+1 problem and how to resolve it? That is what we will take a look at.

Example setup

For this post we will be using this database structure @gist and this seed data @gist.

N+1 problem

N+1 query problem is a situation when you are making an extra call(s) to the database when you want to get specific associated data over and over again. For example in ruby:

irb(main):001:0> Item.all.each { |item| item.category.title }  
  Item Load (0.9ms)  SELECT "items".* FROM "items"
  Category Load (0.1ms)  SELECT  "categories".* FROM "categories" WHERE "categories"."id" = ? LIMIT 1  [["id", 1]]
  Category Load (0.0ms)  SELECT  "categories".* FROM "categories" WHERE "categories"."id" = ? LIMIT 1  [["id", 1]]
  Category Load (0.0ms)  SELECT  "categories".* FROM "categories" WHERE "categories"."id" = ? LIMIT 1  [["id", 1]]
  Category Load (0.0ms)  SELECT  "categories".* FROM "categories" WHERE "categories"."id" = ? LIMIT 1  [["id", 2]]
  Category Load (0.0ms)  SELECT  "categories".* FROM "categories" WHERE "categories"."id" = ? LIMIT 1  [["id", 2]]
  Category Load (0.0ms)  SELECT  "categories".* FROM "categories" WHERE "categories"."id" = ? LIMIT 1  [["id", 2]]

This example gets all items, then iterates each of those records and tries to get items category from the database. Imagine this happening for 50 or 100 items, and when the category model would be fatter.

Common solutions

The most common solution is to use eager loading methods:

preload

Preload is default case for #includes method – it creates two separate queries, one for the main query and other for associated data. That means we can’t add #where({ categories: { title: “Fruits” } }), it will raise an error.

irb(main):001:0> Item.preload(:category).all.each { |item| item.category.title }  
  Item Load (0.9ms)  SELECT "items".* FROM "items"
  Category Load (0.1ms)  SELECT "categories".* FROM "categories" WHERE "categories"."id" IN (1, 2)

An example will load all items with a preloaded category.

includes

Instead of #preload, #includes chooses to make one query based on the situation – if you add associated #where clause. This will make more complex SQL query:

irb(main):001:0> Item.includes(:category).where(categories: { title: 'Fruits' }).each { |item| item.category.title }  
  SQL (0.2ms)  SELECT "items"."id" AS t0_r0, "items"."title" AS t0_r1, "items"."description" AS t0_r2, "items"."price" AS t0_r3, "items"."category_id" AS t0_r4, "categories"."id" AS t1_r0, "categories"."title" AS t1_r1, "categories"."description" AS t1_r2 FROM "items" LEFT OUTER JOIN "categories" ON "categories"."id" = "items"."category_id" WHERE "categories"."title" = ?  [["title", "Fruits"]]

The example will load only items with the preloaded category which title is Fruits.
You can also force #includes to make one query with #references(using LEFT OUTER JOIN) method:

irb(main):001:0> Item.includes(:category).references(:categories).each { |item| item.category.title }  
  SQL (0.1ms)  SELECT "items"."id" AS t0_r0, "items"."title" AS t0_r1, "items"."description" AS t0_r2, "items"."price" AS t0_r3, "items"."category_id" AS t0_r4, "categories"."id" AS t1_r0, "categories"."title" AS t1_r1, "categories"."description" AS t1_r2 FROM "items" LEFT OUTER JOIN "categories" ON "categories"."id" = "items"."category_id"

eager_load

This method is the same as a combination of #includes and #references as it makes one query with LEFT OUTER JOIN:

irb(main):001:0> Item.eager_load(:category).each { |item| item.category.title }  
  SQL (0.1ms)  SELECT "items"."id" AS t0_r0, "items"."title" AS t0_r1, "items"."description" AS t0_r2, "items"."price" AS t0_r3, "items"."category_id" AS t0_r4, "categories"."id" AS t1_r0, "categories"."title" AS t1_r1, "categories"."description" AS t1_r2 FROM "items" LEFT OUTER JOIN "categories" ON "categories"."id" = "items"."category_id"

These are most common N+1 occurrences and they quite easy to notice if you take a look in Rails console logs but there some that don’t look like this or are harder to notice.

Other solutions

These solutions/workarounds I have gathered fixing project that had a lot of N+1 issues or many equal queries.

Count/sum/max/etc. with a dynamic condition

A lot of places I saw this or similar code:

def specific_date_wrong(date)  
  Item.where("DATE(expire_at) = ?", date).count
end

and then called in a single page for the whole month, so 30 queries to get the count for each day. Dumb, if you ask me. This can be fixed with simple grouping:

def specific_date_right(date)  
  @specific_date ||= Item.group('DATE(expire_at)').count
  @specific_date[date] || 0
end

it will create instance variable with hash where keys are dates and values are occurrences. All that with one query.

Join multiple queries into one

There might be queries that can’t be grouped but can be joined in SQL level. When it comes to complicated things, ActiveRecord isn’t the thing you should be counting on. Instead, use plain SQL to get needed things from a database (be careful, if in future you decide to change database type, this might not work anymore).
Silly example of what I mean:

Item.where(validated: true).count
  Item.where(validated: false).count
  Item.sum(:count_1)
  Item.sum(:count_2)
  Item.sum(:count_3)
  Item.sum(:count_4)

which can be written in one query:

Item.select("count(case when validated = 't' then 1 else null end) as valid, count(case when validated = 'f' then 1 else null end) as invalid")  
  .select("sum(count_1) as total_1, sum(count_2) as total_2, sum(count_3) as total_3, sum(count_4) as total_4")
  .take

It is a bit more complicated or messier, but that is how SQL rolls. Calling #attributes method on previous will show that it has attributes we aliased.

Bullet

There is a gem called bullet that helps to find N+1 queries in your application during development mode by showing you notifications in the browser. Bullet will tell you when you need to eager load something or on the other hand – when you don’t need to. You can find more about bullet at github.

Summing it up

ActiveRecord is easy to learn tool for making Rails applications but it still requires you to know some things before writing fast pages. These examples are what I have gathered. Share your thoughts and examples which you are using day-to-day programming on Ruby.

ActiveRecord bullet gem N+1 queries RoR ruby on rails solutions