N+1 won’t be a problem anymore

Every developer sooner or later faces problem N+1. ActiveRecord (Rails default ORM) supports loading associations via includes to bypass N+1.

Unfortunately, often, not all the data that we need can be declared in the form of standard associations. Let’s look at a few examples.

Example 1. Number of orders per user

Suppose we have models:

class User < ActiveRecord::Base
  has_many :orders
end

class Order < ActiveRecord::Base
  belongs_to :user
end

Our task is to display on the page a list of users and the total number of orders for each user. Our first decision can be.

<table>
  <tr> 
    <td> User ID </td>
    <td> Orders count </td>
  </tr>
  <%- User.all.each do |user| %>
    <tr>
      <td> <%= user.id %> </td>
      <td> <%= user.orders.count </td>
    </tr>
  <% end %>
</table>

It is not difficult to see that now we have N + 1 problems, since for each user we load the number of his orders.

This problem can be fixed using the built-in tools as follows:

<table>
  <tr> 
    <td> User ID </td>
    <td> Orders count </td>
  </tr>
  <%- User.includes(:orders).all.each do |user| %>
    <tr>
      <td> <%= user.id %> </td>
      <td> <%= user.orders.size </td>
    </tr>
  <% end %>
</table>

We used includesto load all orders for each user with one common request, and also replaced count on the sizeto avoid executing the request COUNT(*).

Despite the fact that this solution gets rid of N + 1, but unfortunately, it is not the most worthwhile, since we load all objects Order, and then in memory we count their number. Ideally, we would like to load only the quantity for the best result.

Example 2: User’s last order

Suppose we have all the same user and order models.

class User < ActiveRecord::Base
  has_many :orders
end

class Order < ActiveRecord::Base
  belongs_to :user
end

Our task is to display information for each user and for their last order. Unfortunately, ActiveRecord does not provide the ability to create an association for the format we need, so the following code could be our solution:

users = User.all

recent_order_per_user = 
  Order.where(id: Order.where(user: users).group(:user_id).maximum(:id))
       .index_by(&:user_id)
      
users.each do |user|
  p "User ID = #{user.id}"
  p "Last order ID = #{recent_order_per_user[user.id]}"
end

This solution, despite the optimal data loading, is not the most convenient, since separately preloaded data has to be “pulled” to the place of their use. Having a complex chain of method calls, this approach greatly worsens the readability and maintainability of the code.

Solution

Gem N1Loader was created to fill in a missing feature in complex association support to avoid N+1 problems.

Let’s see how we could use N1Loader to solve the N+1 problem in the examples above.

Solution for example 1.

class User < ActiveRecord::Base
  include N1Loader::Loadable
  
  n1_loader :orders_count do |users|
    orders_count_per_user = Order.where(user: users).group(:user_id).count
    
    users.each { |user| fulfill(user, orders_count_per_user[user.id])
  end
end

class Order < ActiveRecord::Base
  belongs_to :user
end

With this implementation, preload orders_count for many users is not difficult:

<table>
  <tr> 
    <td> User ID </td>
    <td> Orders count </td>
  </tr>
  <%- User.includes(:orders_count).all.each do |user| %>
    <tr>
      <td> <%= user.id %> </td>
      <td> <%= user.orders_count </td>
    </tr>
  <% end %>
</table>

Solution for Example 2

class User < ActiveRecord::Base
  include N1Loader::Loadable
  
  n1_loader :recent_order do |users|
    recent_order_per_user = 
      Order
       .where(id: Order.where(user: users).group(:user_id).maximum(:id))
       .index_by(&:user_id)
    
    users.each { |user| fulfill(user, recent_order_per_user[user.id]) }
  end
end

class Order < ActiveRecord::Base
  belongs_to :user
end

and directly using this method:

User.all.includes(:recent_order).each do |user|
  p "User ID = #{user.id}"
  p "Last order ID = #{user.recent_order}"
end

Total

N1Loader helps to avoid N+1 problems easier than ever. The gem has many cool features such as argument support, integration with ArLazyPreload and many more.

I recommend that you read and try it in your projects. Appreciate any feedback and input!

Unfamiliar with gems database consistency and factory traceto help improve your code? Welcome!

Thank you for your attention!