Even experienced engineers sometimes overlook N-1 problems,
so this article will show you how to improve them.

1. What’s N-1 problems
2. Install gem that alerts you if N-1 problems exists
3. Check if N-1 problems are detected
4. Improve the code specifically

1. What’s N-1 problems

First, a brief explanation for those who are not familiar with the N-1 problem.
Assume that there are two tables, projects and tasks, and that the Project model and Task model have a 1 : N relationship.
A project model and a task model have a 1 : N relationship, and a single project contains multiple tasks.

If you try to display information on 5 tasks by looping through the project and related tasks arrays with “each”, you will get an N-1 problem, and the information on 5 tasks will be displayed.
If the N-1 problems occurs, SQL will be issued to retrieve the 5 tasks.
As a countermeasure, if the tasks associated with the project are cached in advance, the
If you cache the tasks associated with a project in advance, you can issue SQL to retrieve 5 tasks in one SQL session.
In other words, four unnecessary SQL are reduced and performance is improved.
This pre-caching is called eager loading.

2. Install gem that alerts you if N-1 problems exists

As a concrete measure, we will use bullet, a gem that points out N-1 problems.
It is easy to use, just install the gem and it will output to the console log.
Add it to the development group in the Gemfile and bundle install.

group :development do
  # add below
  gem 'bullet' 
end

After installation, run the bundle exec command in the Rails root directory.
This command will set up the bullet in the development environment.

$ bundle exec rails g bullet:install
...
# It asked me if I wanted to enable it in the test environment, 
# I chose n
Would you like to enable bullet in test environment? (y/n) n

3. Check if N-1 problems are detected

After starting Rails, check the console log.
If you see the following statement in the console, it means that the N-1 problem has been detected and you need to take countermeasures.

USE eager loading detected
  Project => [:tasks]
  Add to your query: .includes([:tasks])
Call stack
  /xxxxxx/project_manager/backend/app/controllers/console/projects_controller.rb:11:in `block in index'
  /xxxxxx/project_manager/backend/app/controllers/console/projects_controller.rb:10:in `index'

4. Improve the code specifically

Once you know where the N-1 problem is occurring, take action.
I will explain each of several cases.
No matter which method you use, the point of eliminating the N-1 problem is the same.

4-1. Use eager_load if you extract by the condition of related tables

if you want to join tasks as related tables, and extract them by conditions of related tables, use eager_load.

# Extract projects by related task states are "running"
Project.eager_load(:tasks).where(tasks: { state: "running" })

4-2. Use preload if you don’t extract by the condition of related tables

Contrary to the above, preload is improve N-1 problems without join to related tables.
If the related tasks table is huge, joining large tables will consume DB memory, but this can be suppressed.

# Extract by project states are "finished"
Project.preload(:tasks).where(state: "finished")

4-3. Use includes if you want to joins table or not depending on the conditions.

Then there are includes that are used to extract or not extract based on the condition of related tables.
The convenient behavior is to determine if a table join is required and then execute eager_load or preload.
Both of the following codes use includes, but internally the process determines whether or not a table join is required and then branches.

# Same behavior as eager_load
Project.includes(:tasks).where(tasks: { state: "running" })

# Same behavior as preload
Project.includes(:tasks).where(state: "finished")

If you notice anything about the article, please comment.