Rails N+1 queries and eager loading

#rails #sql #beginners #performance

What is the N+1 query problem?

The N+1 query problem is one of the common performance antipatterns in ORMs. It happens when a query is executed on each result of the previous query, in other words, when an application gets data from the database and then loop through the result of that data.

This works just fine if your application is small enough and there are only a small number of requests and queries, but if you want to keep your application scalable, you have to eliminate the N+1 problem from your application.

Here's an example with Rails:

# app/models/post.rb
class Post < ApplicationRecord
  belongs_to :user
end

# app/models/user.rb
class User < ApplicationRecord
  has_many :posts
end

Let's print the title and user's username for all the posts.

Post.all.each do |post|
  puts "#{post.title} was written by #{post.user.username}"
end

ActiveRecord executed the queries below:

Post Load (0.4ms)  SELECT "posts".* FROM "posts"
  User Load (0.2ms)  SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 2], ["LIMIT", 1]]
  User Load (0.2ms)  SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 2], ["LIMIT", 1]]
  User Load (0.3ms)  SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 4], ["LIMIT", 1]]
  User Load (0.2ms)  SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 4], ["LIMIT", 1]]
  User Load (0.2ms)  SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 4], ["LIMIT", 1]]
  User Load (0.2ms)  SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 4], ["LIMIT", 1]]

It first retrieved all the Post objects and then user for each post. There are 6 posts in the database in this example, so 7 queries in total were executed.

What if there are 10,000 posts in the database? The number of database connections is limited and you might hit query timeout.

Solve the N+1 problem with "eager loading"

As opposed to lazy loading, eager loading is the process whereby a query loads a resource as soon as the code is executed. It also loads related entities as part of the query.

In Rails, you can use includes method for eager loading.

The includes method specifies relationships to be included in the result set.

Let's rewrite the example using the includes method:

Post.includes(:user).each do |post|
  puts "#{post.title} was written by #{post.user.username}"
end

Post Load (103.7ms)  SELECT "posts".* FROM "posts"
  User Load (32.1ms)  SELECT "users".* FROM "users" WHERE "users"."id" IN (?, ?, ?, ?, ?)  [["id", 2], ["id", 4]]

Now we got the same output by only 2 queries.

What happened is Post.includes(:user) told ActiveRecord to retrieve the corresponding user records from the database immediately after the initial request for all posts. Since the records of users were already in the memory, post.user.username could be retrieved by only one query. Now, even if we have 10,000 posts in our database, we can execute the example code above by just 2 queries! This is a huge difference.

Find the N+1 query problem with the Bullet Gem

Even if you review your code carefully trying to eliminate N+1 queries from your application, errors can slip into production code. The Bullet Gem is a great tool to automate the process.

The Bullet gem is designed to help you increase your application's performance by reducing the number of queries it makes.
It will watch your queries while you develop your application and notify you when you should add eager loading (N+1 queries), when you're using eager loading that isn't necessary and when you should use counter cache.
>- Bullet Gem README https://github.com/flyerhzm/bullet

As your application gets bigger, you need to pay more attention to its speed and performance. Eager loading in Rails is really easy to implement but makes a big difference.