Find, Resolve, and Prevent N+1 Query Issues in Rails.

This blog is a part of our "Guide to Rails Performance." To gain a deeper understanding of techniques for optimizing Rails code, Please explore our earlier blogs of this series:

1. Difference between Includes and Joins in Ruby on Rails

Introduction:

When you're building a web application with Ruby on Rails, it's essential to make it fast and responsive. One common issue that can slow down your app is called "N+1 queries." Imagine you're trying to show a list of students and the colleges they attend. Without careful coding, this can lead to your app making too many trips to the database, making it slow for users.

Let's look at an example. Suppose you want to display the first ten students and their colleges. Here's what the code might look like:

students = Student.limit(10)

students.each do |student|
  puts "#{student.college.name} build number #{student.name}"
end

This code works, but it's not efficient. It sends multiple queries to the database:

One query to get the students. And then, for each student, another query to get their college.

Student Load (0.3ms) SELECT "students".* FROM "students" LIMIT ? [["LIMIT", 10]]
College Load (0.2ms) SELECT "colleges".* FROM "colleges" WHERE "colleges"."id" = ? LIMIT ? [["id", 1], ["LIMIT", 1]]
College Load (0.2ms) SELECT "colleges".* FROM "colleges" WHERE "colleges"."id" = ? LIMIT ? [["id", 1], ["LIMIT", 1]]
College Load (0.2ms) SELECT "colleges".* FROM "colleges" WHERE "colleges"."id" = ? LIMIT ? [["id", 1], ["LIMIT", 1]]
College Load (0.2ms) SELECT "colleges".* FROM "colleges" WHERE "colleges"."id" = ? LIMIT ? [["id", 1], ["LIMIT", 1]]
College Load (0.2ms) SELECT "colleges".* FROM "colleges" WHERE "colleges"."id" = ? LIMIT ? [["id", 1], ["LIMIT", 1]]
College Load (0.2ms) SELECT "colleges".* FROM "colleges" WHERE "colleges"."id" = ? LIMIT ? [["id", 1], ["LIMIT", 1]]
College Load (0.2ms) SELECT "colleges".* FROM "colleges" WHERE "colleges"."id" = ? LIMIT ? [["id", 2], ["LIMIT", 1]]
College Load (0.1ms) SELECT "colleges".* FROM "colleges" WHERE "colleges"."id" = ? LIMIT ? [["id", 2], ["LIMIT", 1]]
College Load (0.2ms) SELECT "colleges".* FROM "colleges" WHERE "colleges"."id" = ? LIMIT ? [["id", 2], ["LIMIT", 1]]
College Load (0.1ms) SELECT "colleges".* FROM "colleges" WHERE "colleges"."id" = ? LIMIT ? [["id", 2], ["LIMIT", 1]]

Even for just ten students, this results in eleven separate database queries. In a real-world scenario, you might need to fetch thousands of records, and that can severely slow down your app.

Identifying N+1 Query Problems

The first step in addressing N+1 query issues is to identify them. Thankfully, Rails provides a powerful tool called the Bullet gem that can help you pinpoint these problems in your code.

Using the Bullet Gem To get started, add the Bullet gem to your Gemfile:

gem 'bullet'

Run bundle install to install the gem. Next, configure it in your development environment by adding the following to your config/environments/development.rb file:

config.after_initialize do
  Bullet.enable = true
  Bullet.alert = true
  Bullet.bullet_logger = true
  Bullet.console = true
end

With Bullet enabled, it will alert you to N+1 query issues when you run your Rails application in development mode. Keep an eye out for these alerts as you interact with your application.

Fixing N+1 Queries

Once you've identified N+1 query problems, it's essential to fix them. Here are some common techniques to address these issues:

Eager Loading

One of the most effective ways to resolve N+1 queries is by using eager loading. ActiveRecord provides methods like includes, joins, and preload to fetch associated records in a single query rather than issuing N separate queries. For example:

students = Student.includes(:college).limit(10)

students.each do |student|
  puts "#{student.college.name} build number #{student.name}"
end

This time we'll use one query to fetch the students and another for fetching the associated colleges.

Student Load (0.4ms) SELECT "students".* FROM "students"
College Load (0.4ms) SELECT "colleges".* FROM "colleges" WHERE "colleges"."id" IN (?, ?, ?, ?) [["id", 1], ["id", 2], ["id", 3], ["id", 4]]

Preventing N+1 Queries

While fixing N+1 queries is crucial, it's equally important to prevent them from happening in the first place. Here are some best practices for preventing N+1 query issues:

Use joins or includes.

Whenever you're searching for data and you know you'll also need related information, make sure to use "joins" or "includes" to retrieve all the needed data at once.For guidance on when to use "joins" or "includes," you can refer to this blog post: Difference between Includes and Joins in Ruby on Rails

By detecting, resolving, and proactively avoiding N+1 query concerns in your Rails application, you can boost its speed and create a more enjoyable experience for your users. If you found this blog helpful, please consider sharing it with others.