The (Silver) Bullet for the N+1 Problem
Rails is one of the popular choices when it comes to building your Minimum Viable Products(MVP). You can bootstrap, prototype, and release an application with such ease that Rails is many developers default choce for an MVP.
Usually while developing these prototypes, most developers don’t consider the performance indexes, which really shouldn’t be a concern at that time, anyway. But when the application needs to be scaled and optimized, these performance indexes comes into play. Then the same developersn need to focus on how the application can be refactored for speed and performance.
One potential area of improvement is the queries your application sends to the database. Reduce the number of queries, increase your application’s performance.
Most Rails applications have data distributed over many models with associations between them, using ORMs for access. ORMs can help you to address the impedance mismatch between relational databases and object oriented models, hopefully making your life simpler. But not knowing about some of their pitfalls can decrease your application performance dramatically. One such pitfall is the select N+1 problem.
What is the N+1 Query Problem ?
This problem occurs when the code needs to load the children of a parent-child relationship (the “many” in the “one-to-many”). Most ORMs have lazy-loading enabled by default, so queries are issued for the parent record, and then one query for EACH child record. As you can expect, doing N+1 queries instead of a single query will flood your database with queries, which is something we can and should avoid.
Consider a simple blog application which has many articles published by different authors:
#Articles model
class Article < ActiveRecord::Base
belongs_to :author
end
#Authors model
class Author < ActiveRecord::Base
has_many :posts
end
We want to list the 5 most recent articles on the sidebar of the article, along with their title and author’s name.
This could be achieved using the following
#In our controller
@recent_articles = Article.order(published_at: :desc).limit(5)
#in our view file
@recent_articles.each do |article|
Title: <%= article.title %>
Author:<%= article.author.name %>
end
The above code will send 6 (5+1) queries to the database, 1 to fetch the 5 recent articles and then 5 for their corresponding authors. In the above case, since we are limiting the number of queries to 5, we won’t see this issue affecting our application performance much. However, for queries with larger limit, this could be fatal.
Each query has quite a bit of overhead. It is much faster to issue 1 query which returns 100 results than to issue 100 queries which each return 1 result. This is particularly true if your database is on a different machine which is, say, 1-2ms away on the network. Here, issuing 100 queries serially has a minimum cost of 100-200ms, even if they can be satisfied instantly by MySQL.
Solution – Eager Loading
Eager loading is the mechanism for loading the associated records of the objects returned by Model.find
using as few queries as possible. In the above example, if we use eager loading to fetch the data, the details of the author will be loaded along with the articles.
In Rails, ActiveRecord has a method called includes
, which ensures that all the associated datas specified are loaded with the minimum number of queries.
Let’s refactor our code to eager load associations.
#In our controller
#Using includes(:authors) will include authors model.
@recent_articles = Article.order(published_at: :desc).includes(:authors).limit(5)
#in our view file
@recent_articles.each do |article|
Title: <%= article.title %>
Author:<%= article.author.name %>
end
How Eager Loading Can Prevent the N+1 Query Problem ?
Eager loading is the solution to the N+1 query problem, making sure you don’t end up running unnecessary queries while looping through an object.
The queries in our example go from
Article Load (0.9ms) SELECT 'articles'.* FROM 'articles'
Author Load (0.4ms) SELECT 'authors'.* FROM 'authors' WHERE 'authors'.'id' = ? ORDER BY 'authors'.'id' ASC LIMIT 1 [["id", 1]]
Author Load (0.3ms) SELECT 'authors'.* FROM 'authors' WHERE 'authors'.'id' = ? ORDER BY 'authors'.'id' ASC LIMIT 1 [["id", 2]]
Author Load (0.4ms) SELECT 'authors'.* FROM 'authors' WHERE 'authors'.'id' = ? ORDER BY 'authors'.'id' ASC LIMIT 1 [["id", 3]]
Author Load (0.3ms) SELECT 'authors'.* FROM 'authors' WHERE 'authors'.'id' = ? ORDER BY 'authors'.'id' ASC LIMIT 1 [["id", 4]]
Author Load (0.4ms) SELECT 'authors'.* FROM 'authors' WHERE 'authors'.'id' = ? ORDER BY 'authors'.'id' ASC LIMIT 1 [["id", 5]]
to
Article Load (0.4ms) SELECT 'articles'.* FROM 'articles'
Author Load (0.4ms) SELECT 'authors'.* FROM 'authors' WHERE 'authors'.'id' IN (1,2,3,4,5)
Less queries for great good. :)
The Bullet Gem
Bullet is a gem, written by Richard Huang, that helps reduce the number of queries an application makes. It was first posted in 2009 as a plugin, an it is still a pretty useful gem to monitor your application for performance improvements. Bullet helps you monitor the queries your application is making to the database and notifies you of any N+1 scenarios. Interestingly, it also notifies you of any unused eager loads.
Bullet has many ways to notify you of N+1 query problems: Growl notifications, JavaScript alerts by default, and even using XMPP. Additionally, it saves out to bullet.log the exact line and stack trace of what caused the alert. If you want it to, it can also write to the application log.
Usage and Configuration
Add Bullet to your Gemfile and run bundle install
.
group :development do
gem 'bullet'
end
The gem should only be used in the development environment, as you won’t want the users of your application getting alerts about the N+1 querry problems.
The next thing to do is configure the way Bullet notifies you.
General Configuration
Bullet should be enabled in the application, just adding the bullet gem won’t notify you of bad queries. Configuration is done in the config/environments/development.rb.
config.after_initialize do
#Enable bullet in your application
Bullet.enable = true
end
Notify Via Javascript Alerts
Bullet can be configured to notify the developer using a simple javascript alert. An alert box pops up while loading the pages that run the N+1 queries. To configure javascript alerts, add the following code to the configuration block above
Bullet.alert = true
Notify via Browser Console
If you don’t like alert boxes popping up all over your application, you could use the browsers console to notify you of the N+1 queries by adding
Bullet.console = true
Notify via Rails Logs
Bullet can also append a notification to your Rails log about your bad queries. Thus, if you are using some analyzer tools for your logs, you could add the following
Bullet.rails_logger = true
Log to a File
If you need the queries to be logged to a file, Bullet lets you do that by
Bullet.bullet_logger = true
which creates a log file named bullet.log with all your bad queries.
Notify via Growl Notifications
If you prefer growl notifications, you can enable the growl support using
Bullet.growl = true
Conclusion
That wraps up my whirlwind tour of the N+1 query problem and the Bullet gem. If you have association in your Rails app, you should start using these tricks today.
Resources
Rubygems : Bullet gem
Github : Source Code
RailsCast : Tutorial