Follow @makagon

Today I would like to show you how easily spot a problem and improve performance of your app.

If you didn't hear about N+1 query problem you can read about it even on official Rails guides website.

We're so get used to nice features of ActiveRecord that we don't care about resulting SQL that being generated by ORM. We don't count how many queries we do and how complex they are.

Let's make sure we understand the problem of N+1 query. As usually I suggest to do that by simple example

We have a blog with models: Post, Category, CategoryPost.

class Post < ActiveRecord::Base
  has_many :category_posts, dependent: :destroy
  has_many :categories, through: :category_posts
end

class Category < ActiveRecord::Base
  has_many :category_posts, dependent: :destroy
  has_many :posts, through: :category_posts
end

class CategoryPost < ActiveRecord::Base
  belongs_to :category
  belongs_to :post
end

Really basic stuff: relation many-to-many. Post has many categories and categories can have many posts. It's possible to do those relations through additional model CategoryPost.

Since we have those relations it's really easy to get all post's categories: post.categories. Let's check SQL that ActiveRecord generated for us:

SELECT `categories`.* FROM `categories` INNER JOIN `category_posts` ON `categories`.`id` = `category_posts`.`category_id` WHERE `category_posts`.`post_id` = 10

Let's go further and implement home page of our blog where we want to show 10 last posts. Controller would look like this:

class PostsController < ApplicationController
  def index
    @posts = Post.order(created_at: :desc).paginate(page: params[:page], per_page: 10)
  end
end

In view we show list of categories for each post:

<% @posts.each do |post| %>
  ...
  <% post.categories.each do |category| %>
    <%=link_to category.title, category_path(category.slug) %>
  <% end %>
  ...
<% end %>

It's so simple with ActiveRecord! But wait, let's check which queries we send to DB:

Looks terrible. We do JOIN for each post. If we have 100 posts - we would get 100 queries to table of categories. That's what we call N+1 query problem.

Rails have simple solution for such problem - use includes. If you know that you will need all categories for each post - use includes(:categories) and Rails will optimize all queries automatically.

So let's change controller's code:

def index
  @posts = Post.includes(:categories).order(created_at: :desc).paginate(page: params[:page], per_page: 10)
end

And look how queries changed:

Now we have 3 queries. And it doesn't depend on number of posts we're going to show. First of all we fetch last 10 posts, then category_posts, and only after that, when we know which categories we need, we fetch categories.

Keep an eye on Rails' log and on DB queries that app sends. Use includes to solve N+1 query problem. It's smart enough to optimize queries following ideas of eager loading. It will try to fetch required data by minimum number of queries.

Want to add couple links which will be useful to read:

1. Rails guides - it's always good to know your tool.

2. If you don't know difference between LEFT JOIN and INNER JOIN - add to bookmarks this page with nice description of JOIN's in MySQL.

3. To catch all N+1's in app, some teams use gems. For example bullet gem is good enough for that.

4. Good article from Arkency which describes difference between includes, preload and eager_load.

Follow @makagon