RubyThe N+1 Dilemma - Bullet or Prosopite?
Abiodun Olowode
Recently, we started profiling some parts of our application, to identify bottlenecks and hot paths. One of the culprits was, as you must have already guessed, N+1 queries.
What are N+1 Queries?
N+1 queries occur when a query is executed for each of the results of a previous query. This means that, if a query is made (1 query) and the result of that query contains N items, and we proceed to make a query for each of those items, we eventually end up making N+1 queries. This is a common problem found in frameworks utilizing ORM (Object-Relational Mapping), eg. Ruby on Rails. With an ORM, we interact directly with an object and its data in the same language an app is written, as opposed to via raw sql queries; this gives rise to the possibility of making more queries than needed to fetch the same data that could have been retrieved when executing the primary SQL query.
Shooting Down N+1s with Bullet, or not?
One of the most common gems in the Ruby community to help detect N+1s is the Bullet gem. The Bullet gem not only detects them but also suggests how to eliminate them using eager loading. It is also able to provide information about positions where eager loading is used but unnecessary.
A quick example of how Bullet works is shown below.
Fetch all books in a database and print their corresponding library name (a library has many books).
1
2
3
4
5
6
def index
books = Book.all
books.each do |book|
puts book.library.name
end
endVisiting this route causes Bullet to output the following:
This happens because the below queries took place:
As seen above, we make 1 query to fetch all the books in the database, and then for every book fetched, we proceed to ask the database for the library of that book. This is way too many trips to the database if you ask me; however, Bullet suggests a fix for this and that would be updating the index method to the following:
1
2
3
4
5
6
def index
books = Book.includes([:library])
books.each do |book|
puts book.library.name
end
endThis fixes the issue and only the following queries are run:
Easy-peasy right? Well, before you celebrate, take a look at something interesting we found:
1
2
3
4
5
6
def index
libraries = Library.all
libraries.each do |library|
puts library.trending?
end
endIn the Library and Book model:
1
2
3
4
5
6
7
8
9
10
11
12
class Library < ApplicationRecord
has_many :books
def trending?
books.interesting?.any?
end
end
class Book < ApplicationRecord
belongs_to :library
scope :interesting?, -> { where("title like ?", "%ruby%") }
endOn visiting this route, at this point, the following SQL queries are performed:
As seen above, for each of the libraries that were returned from the first query, we make a corresponding query. That’s an N+1 right there, but Bullet does not complain because it does not detect it. On realizing this, we decided to search for an alternative that could detect cases like this, cause it was obvious that our N+1 bullets were missing their targets.
Prosopite - The “bullet” that doesn’t miss?
Prosopite is a ruby gem that seeks to auto-detect Rails N+1 queries. In the owner’s own words, “The need for prosopite emerged after dealing with various false positives/negatives using the bullet gem”. As seen previously, we encountered a false negative - the indication that you have no N+1s, when in reality you do. On the installation of this gem, we encounter the following error:
Prosopite is able to detect and inform us of this N+1. However, prosopite does not suggest how this can be fixed, like in the case of the bullet gem. It does not tell you where to add “include” statements, hence, the onus is on you to figure it out. This should not be a hard task as the stack trace is available to figure out where the N+1 occurred.
Below is the result from one of the points in our application (non-production environment with way less data) where an N+1 was fixed after detection by prosopite:
BEFORE FIX:
AFTER FIX:
The time spent executing SQL as seen above was reduced by two-thirds and this, of course, translates into lesser queries for same amount of data and happier databases. With several developers working on different parts of an application and lacking the full knowledge about the other domains, there is a likelihood that N+1s would be introduced, many times, unknowingly. It is therefore very important to have a tool that identifies these and identifies them correctly.
In my opinion, trading the suggestion of how to fix an N+1 is nothing compared to knowing that it does exist. With a proper understanding of what N+1s are and how they come about, fixing them should not be impossible. Sometimes, we might have to rethink the logic leading to the queries or carry out some checks in memory as opposed to several database calls; whatever method is chosen is highly dependent on the situation at hand. The most important thing is that we are able to identify them and fix them in a performant manner.