Not too long ago I was introduced to Ruby on Rails through my time as a Flatiron student. To those familiar with the Ruby framework, Rails makes creating complex MVC web applications very simple. It wasn't long before I started developing fairly complicated apps with Rails. However, after gaining a fair amount of users on my Heroku hosted app MeMix, I ran into some big problems. My application kept crashing. It hadn't been crashing before, and after some diagnostics with the New Relic analytics tool, the problem became clear - slow database loading times. Heroku will automatically crash your app if your load time takes more than 30 seconds. I clearly had a big problem with my database queries, something that is referred to as N+1 queries.
The Problem
A big cog in the Rails machine is something called Active Record. Let's say we had two models that are associated with each other: (I actually made a Github repo with this sample so you can easily try it yourself)
class User < ActiveRecord::Base
has_many :posts
end
class Post < ActiveRecord::Base
belongs_to :user
end
With Active Record, a typical and easy way to access a list of a user's posts, and what I learned at bootcamp, would be to write User.posts
. But what if we want to iterate over a list of Users and then iterate over each user's posts? We could write something like:
User.all.each do |user|
user.posts.each do |post|
puts post.content
end
end
This will work just fine. However for each user we are querying the database for their posts. Which means our query complexity has become N+1. N being the number of users, since for each user we make a request to the database for its associated posts, and plus one query for getting all users.
This is totally fine if you have a small database. However, once your database grows, and if you have complicated associations, it will start to slow down.
The Solution
ActiveRecord has a method called includes. Basically it allows you to load a model's associations in a single query. So in our example before, you would write:
User.includes(:posts).all.each do |user|
user.posts.each do |post|
post.content
end
end
Here's the difference between the queries when looking at the database output in the console:
Bad Query:
User.bad_query
User Load (0.2ms) SELECT "users".* FROM "users"
Post Load (0.1ms) SELECT "posts".* FROM "posts" WHERE "posts"."user_id" = ? [["user_id", 1]]
hello
Post Load (0.1ms) SELECT "posts".* FROM "posts" WHERE "posts"."user_id" = ? [["user_id", 2]]
hello
Good Query:
User.good_query
User Load (0.2ms) SELECT "users".* FROM "users"
Post Load (0.2ms) SELECT "posts".* FROM "posts" WHERE "posts"."user_id" IN (?, ?) [[nil, 1], [nil, 2]]
hello
hello
Can you spot the extra db call in the bad query? This is just with two users, but its easy to imagine a complicated db structure causing major speed issues. The cool thing about includes is that you can chain on as many associations as you want - User.includes(:posts, :followers, :likes).all
.
Sometimes, however, using this handy active record method could slow down your app if the database query is very complicated (e.g. nested associations, many-to-many, etc). It can be hard to know when it is more efficient to use it. However, there is a gem called Bullet that is designed to help with this exact issue. It will tell you when your query chains are inefficient and what to do about it.
After implementing this technique on my slow Rails app, the average load time went down from 10-15 seconds to 2-3 seconds. So if you're experiencing slow load times, please consider checking your query methods, and look for N+1 queries. I wish I knew this when I built my first Rails app.
Top comments (3)
Nice gotch, this might be usefull for you as well github.com/varvet/pundit
Great post. To the point with clear and concise examples. The Rails community needs more of these.
Thanks David! Pleased that you enjoyed it.