N+1 query issue in Rails

Let’s get to know the issue and its probable solution with preload, includes and eager_load in Rails

There is a popular issue in the Rails framework related to query known as (n+1) query issue, which occurs while accessing associated data. To resolve the issue Rails provides a few cases and we can use them according to our needs. First of all, let’s distinguish our sections of discussion as below,

  • What’s (n+1) query issue with associated data in Rails?
  • How can we resolve this issue?

Before moving forward let me create one scenario where let’s consider a User table has an association of one to many with Post table so that one user object can have multiple posts. Now I want to render data for posts as per some particular user i.e. let’s consider I want to show name and description for all the posts of the users.

What’s (n+1) query issue with associated data in Rails?

With the scenario set where the User has_many association with the Post model, to render the associated data we can write code as below,

users = User.allusers.each do |user|
user.posts.each do |post|
puts post.name
end
end

So in the backend query fire will be 1 SQL query to load user and rest n number of SQL query to load associated posts data as below,

This shows the famous (n+1) query issue in Rails. If it’s an issue then there must be some solution over it, right? Let’s look for a probable solution over it!

How can we resolve this issue?

The simple solution over this (n+1) number of queries generation could be to convert these queries to a limited number of queries. To be more precise, one query to load user data and another one to load post data. The probable solution includes the use of includes or preload.

users = User.includes(:posts).all
# other way
# User.preload(:posts).all
users.each do |user|
user.posts.each do |post|
puts post.name
end
end

Now the query will be fired as one to get user data and another to get associated post data as below,

Let’s say, for some reason your requirement is to force these two queries to a single query. For such a requirement, we can use eager_load or includes+references.

users = User.eager_load(:posts).all
# other way
# User.includes(:posts).references(:posts).all
users.each do |user|
user.posts.each do |post|
puts post.name
end
end

For this, a single query will be fired to get user as well as associated posts data as below,

There is a lot more to these methods other than just solving (n+1) query issues. Let’s discuss those stuff in depth in some other blog!