Blog:

Eliminating N+1 Queries and Bullet Gem

Avatar
Charles Martinez
December 13, 2022

Eliminating N+1 Queries and Bullet Gem

A lot of software engineers would argue that Ruby on Rails doesn’t scale and is relatively slower and would not be a good option if you are planning to build an application that would expect thousands of users. But still in the year 2022, a lot of known companies like Github, AirBnB still use Ruby on Rails for their applications. So where does this argument that Ruby on Rails doesn’t scale come from?

Usually it comes from the performance side of the application. When developing the application, the app is just expected to handle a slow amount of user traffic, But what happens if the application reaches 100,000 users and the user traffic grows significantly? The application becomes slow and most of the time the main culprit is slow database queries due to N + 1 Queries.

The N+1 query happens when a query is executed for every result of a previous query. Let’s say we have these models in our Rails application.


class Post
  has_many :comments
end 

class Comment
  belongs_to :post
end

And let’s say we wanted to query all of comments of a post


<% @posts.each do |post| %>
  <% post.comments.each do |comment| %>
    <% comment.body %>
   <% end %
<% end %>

And for example that we would have 5 post records with 1 comment record each. Then it would result in 6 Queries (1 Query to Load the Posts, and 5 queries for loading each comment of each Post). Imagine having 1,000 or more posts with hundreds of comments. That would result in 1001+ Database Queries which will lead to slow performance of the application.


Post Load (0.4ms) SELECT "posts".* FROM "posts"

Comment Load (0.8ms) SELECT "comments".* FROM "comments" where "comments"."post_id" = $1 [["post_id", 1]]
Comment Load (0.8ms) SELECT "comments".* FROM "comments" where "comments"."post_id" = $1 [["post_id", 2]]
Comment Load (0.8ms) SELECT "comments".* FROM "comments" where "comments"."post_id" = $1 [["post_id", 3]]
Comment Load (0.8ms) SELECT "comments".* FROM "comments" where "comments"."post_id" = $1 [["post_id", 4]]
Comment Load (0.8ms) SELECT "comments".* FROM "comments" where "comments"."post_id" = $1 [["post_id", 5]]

One tool that can help identify these N+1 queries is the Bullet Gem. By just installing this gem (Follow installation here https://github.com/flyerhzm/bullet) you will be notified and you can identify easily where these N+1 queries are coming from.

Once you’ve identified these N+1 Queries, you can finally avoid them by eager loading which will reduce the number of independent database queries. To do so, You would need to use the ActiveRecord includes method e.g


<% @posts.includes(:comments).each do |post| %>
  <% post.comments.each do |comment| %>
    <% comment.body %>
   <% end %
<% end %>

This would result (By using the sample above) to only 2 database queries


Post Load (0.4ms) SELECT "posts".* FROM "posts"
Comment Load (0.8ms) SELECT "comments".* FROM "comments" where "comments"."post_id" IN (1, 2, 3, 4, 5)

This would reduce the number of database queries and will increase page load speed and will significantly improve the performance of your Rails application specially in cases that you are querying a lot of data from your database.

That’s it! With just using the Bullet Gem and applying eager loading on your N+1 queries, that would help it improve your application’s performance.