An Easy Intro to N + 1 Queries in Ruby on Rails

To Put It Plainly…

N +1 queries are occurrences of bad backend performance. Real-world consequences range from poor loading times for users and exorbitant server usage fees. They typically manifest when using high-level abstracted Object Relational Mapping(ORMs) frameworks such as ActiveRecord which Ruby on Rails uses.

Normally, one does not encounter severe performance issues if their app is relatively small, since small apps tend to have a low volume of database queries the risks mentioned above are virtually negligible. However, this shouldn’t be an excuse to develop apps riddled with n + 1 queries, best practice is early prevention.

Key Ingredients to Make an n + 1 Query

1. Model Relationships

Ingredient numero uno compromises of a parent to child association. More specifically, a has_many/belongs_to relationship. This association is tied into the database and allows the ORM, ActiveRecord in this case, to create new post comment pairings or retrieve previously created pairings. ActiveRecord can really be a blessing.

An example of the aforementioned parent-child association

class Post < ActiveRecord::Base
has_many :comments
end
class Comment < ActiveRecord::Base
belongs_to :post
end

2. Database Query

Given the power of our ORM, we will want to display our relationship data to our users. Here is where we utilize ORM provided methods to execute database queries, otherwise known as SQL queries. An example of this occurrence is provided below.

class PublicController < ApplicationController
 def index
  @posts = Post.all
 end
end

all is ActiveRecord’s way of saying “hey database here is a request for all the posts that you currently have stored.” Once the request is complete, the data is sent to the next ingredient.

3. Rendering Data

I couldn’t find a decent phrase to name this ingredient so please bear with me. But essentially, n + 1 queries come about when trying to render data structures like arrays. Structures that could potentially hold large amounts of homogenous data. We do this through iteration, see below for the code example.

<% @posts.each do |post| %>
<h1><%= post.title %></h1>
 <% post.comments.each do |comment| %>
  <p><%= comment.body %></p> 
 <% end %>
<% end %>

The idea here is that we are receiving data from our index action in our PublicController aka the 2nd ingredient. Ultimately, we iterate over the arrays containing all the posts and each post has its own comments stored in its own array, in essence, we have a nested array therefore we iterate twice with the each iterator to display some basic HTML.

How to Identify if an n + 1 query exists?

First, identify that your three ingredients are present. Then inspect your server logs. If you notice significant repetitive SQL queries then chances are your app feature is encountering an n + 1 query.

redundant SQL queries are high indicators that one or more n + 1 queries exists

Optionally, if you do not trust your weathered eye to determine if an n + 1 query exists, I recommend using a nifty Ruby gem called Bullet. They have a wonderful walkthrough and very easy to use. Not only does it identify n + 1 queries for you but it also makes accurate suggestions as to how you should solve it. But how do you solve an n + 1 query?

Solving It

Mercifully, the solution is not overly complex. Use this analogy as a frame of reference, say you are grocery shopping (Happy Turkey Day!) would you prefer to carry all your selected products to the cashier at once or retrieve each item one at a time. Don’t be this person. That is an n + 1 query. The n represents the number of times it took you to get all the items, this number can be infinite since it depends on how many grocery items your momma tells you to get over the phone, and 1 represents the initial fetch or in code language, the initial database call from the controller so this line @posts = Post.all

To solve it we use a method provided by ActiveRecord called include its function is based on a concept known as eager loading. It looks like this.

class PublicController < ApplicationController
def index
  @posts = Post.includes(:comments)
 end
end

The includes method pre-loads the post instance with its related comments, it does this by utilizing memory space. The advantage is that you are no longer making several calls to the database since the association can be access from memory storage (yes it can also be accessed from database storage but then we encounter n + 1 again).

Wrap Up

N + 1 may seem like an obscure topic at first glance but the key takeaway is that it's a drag on backend performance. It might not affect your app’s performance very early on but as your app grows the hindrances of n + 1 can be very costly. Take care of it early on.