« HE:labs
HE:labs

N + 1 Queries in Rails

Postado por Flavia Fortes em 07/05/2014

On my last post I described what I've done to fix a server time out caused by performance issues that occurred in one of our applications. As I promised, I'm coming back to talk about a very important point on the refactoring process.

So, today's topic are those nasty n + 1 queries that occur when we have associations. For example:

1 #model
2 class Post < ActiveRecord::Base
3   belongs_to :user
4 end
1 #controller
2 class PostsController < ApplicationController
3 
4   def index
5     @posts = Post.limit(10)
6   end
7 end
1 #view
2  - @posts.each do |post|
3     = post.user.name

In the code above, it's being executed not only 1 query to find 10 posts (1), but actually 1 query to find 10 posts and + 10 queries to find the creator of each post. (N + 1) This situation is very common, because the code looks good at first sight, but behind the scenes it really doesn't. Take a look:

 1 Started GET "/posts" for 127.0.0.1 at 2014-05-06 16:34:59 -0300
 2 Processing by PostsController#index as HTML
 3   User Load (0.8ms)  SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 1]]
 4    (1.2ms)  SELECT COUNT(count_column) FROM (SELECT 1 AS count_column FROM "posts" LIMIT 10) subquery_for_count
 5   Post Load (38.6ms)  SELECT "posts".* FROM "posts" LIMIT 10
 6   User Load (1.2ms)  SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 1]]
 7   User Load (1.0ms)  SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 2]]
 8   User Load (0.7ms)  SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 3]]
 9   User Load (0.7ms)  SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 4]]
10   User Load (0.9ms)  SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 5]]
11   User Load (1.2ms)  SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 6]]
12   User Load (0.8ms)  SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 7]]
13   User Load (0.7ms)  SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 8]]
14   User Load (0.6ms)  SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 9]]
15   User Load (0.6ms)  SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 10]]
16   Rendered posts/index.html.haml within layouts/application (65.9ms)
17 Completed 200 OK in 80ms (Views: 28.6ms | ActiveRecord: 49.3ms)

So, how do we fix that? With Eager Loading Associations

In our controller we specify which associations we want to load at the same time posts are being loaded from the database. We use the includes method, like this:

1 #controller
2 class PostsController < ApplicationController
3 
4   def index
5     @posts = Post.includes(:user).limit(10)
6   end
7 end

And, that's it. No more N + 1 Queries! See for yourself:

1 Started GET "/posts" for 127.0.0.1 at 2014-05-06 16:37:50 -0300
2 Processing by PostsController#index as HTML
3   User Load (1.5ms)  SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 1]]
4    (2.3ms)  SELECT COUNT(count_column) FROM (SELECT 1 AS count_column FROM "posts" LIMIT 10) subquery_for_count
5   Post Load (39.6ms)  SELECT "posts".* FROM "posts" LIMIT 10
6   User Load (42.4ms)  SELECT "users".* FROM "users" WHERE "users"."id" IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
7   Rendered posts/index.html.haml within layouts/application (120.6ms)
8 Completed 200 OK in 161ms (Views: 39.7ms | ActiveRecord: 102.0ms)

Ok, but I bet there are many n + 1 queries in your application and you have no idea that it's going on. Instead of looking for them on the app's logs, let me introduce you an easier way to discover them, the Bullet Gem. Add this to your development group on Gemfile:

1 #Gemfile
2 group :development do
3   gem 'bullet'
4 end

Configure it as stated on Bullet's README and everytime you have a n + 1 query it will warn you. :)

Whenever you feel that your app is getting slower, check out the queries! Solved that, there are still 2 important topics remaining: Select versus Pluck and Ruby Memoization. Both I'm looking forward to explain for you next time!

Hope you liked it! Hugs!

Compartilhe

Sabia que nosso blog agora está no Medium? Confira Aqui!