ActiveRecord performance: N+1 queries
Reference: https://guides.rubyonrails.org/active_record_querying.html#eager-loading-associations
What is N+1 query?
The N+1 query problem is one of the common performance antipatterns in ORMs. It happens when an application gets data from the database and then loop through the result of that data. Let's look at the following example:
We have two model Post and User:
# app/models/post.rb
class Post < ApplicationRecord
belongs_to :user
end
# app/models/user.rb
class User < ApplicationRecord
has_many: :posts
end
Let's suppose I want to print out title
and user's
username for all the posts. I can try something like this:
Post.all.each do |post|
puts "#{post.title} - #{post.user.username}"
end
How does ActiveRecord execute the code above? If there are 7 posts in the database, in total 8 different queries would be executed:
Post Load (0.6ms) SELECT "posts".* FROM "posts"
User Load (0.5ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2 [["id", "fe8b7def-58fa-46d1-8356-1760ff3fddfa"], ["LIMIT", 1]]
User Load (0.4ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2 [["id", "78dd480d-da51-453a-b890-69f800420145"], ["LIMIT", 1]]
User Load (0.4ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2 [["id", "78dd480d-da51-453a-b890-69f800420145"], ["LIMIT", 1]]
User Load (0.3ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2 [["id", "78dd480d-da51-453a-b890-69f800420145"], ["LIMIT", 1]]
User Load (0.3ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2 [["id", "eecfe9a6-559b-4b84-9068-15bfbcd4dfbb"], ["LIMIT", 1]]
User Load (0.2ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2 [["id", "eecfe9a6-559b-4b84-9068-15bfbcd4dfbb"], ["LIMIT", 1]]
User Load (0.2ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2 [["id", "eecfe9a6-559b-4b84-9068-15bfbcd4dfbb"], ["LIMIT", 1]]
Imagine if we have thousands of records. It's nightmare!
Eager loading
Let's try includes
method:
Post.includes(:user).each do |post|
puts "#{post.title} - #{post.user.username}"
end
POST Load (0.5ms) SELECT "posts".* FROM "post"
User Load (1.4ms) SELECT "users".* FROM "users" WHERE "users"."id" IN ($1, $2, $3) [[nil, "fe8b7def-58fa-46d1-8356-1760ff3fddfa"], [nil, "78dd480d-da51-453a-b890-69f800420145"], [nil, "eecfe9a6-559b-4b84-9068-15bfbcd4dfbb"]]
All users are preloaded for each post and is temporarily cached. This allows looping through all posts and calling .user
without having to access the database multiple times => problem solved!
Prevent the N + 1 problem
To summary, in order to prevent the N+1 problem, we got two possible options:
- Use
gem Bullet
- Document: https://github.com/flyerhzm/bullet
- Use includes | preload | eager_load method
Thank you all for reading!