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.

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.

class Post < ActiveRecord::Base
has_many :comments
endclass 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

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 %>

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

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

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

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.

I do software engineering