Solving N+1 problem using includes
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:
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
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
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
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:
Rails guides - it's always good to know your tool.
If you don't know difference between
INNER JOIN- add to bookmarks this page with nice description of JOIN's in MySQL.
To catch all N+1's in app, some teams use gems. For example bullet gem is good enough for that.
Good article from Arkency which describes difference between