[RAILS] Christmas N + 1

Introduction

December 25th. The world is on Christmas day, but leave that out I have summarized the N + 1 problem, which seems to be known and vague, once again as a memorandum. (When I was writing, the date changed due to the difference in touch ...)

Development environment

Ruby 2.6.5 Rails 6.0.3.4 MySQL Visual Studio Code (GoogleChrome)

What is the N + 1 problem?

The N + 1 problem is a problem that ** tables use associations, which increases the number of tables to be connected and the number of times the database is accessed **. Issuing a lot of SQL puts a load on the database and causes a decrease in application performance.

Example of N + 1 problem

Here is an example of a personally created to-do list app. The ToDo list itself is the lists table, and the items in the list are the items table. Since one list has many items, the following one-to-many associations are formed.

lists table

lists.rb

class List < ApplicationRecord
  has_many :items
  validates :name, presence: true
end

items table

item.rb

class Item < ApplicationRecord
  belongs_to :list
  validates :name, presence: true
end

If you want to display the entire list, get it using the all method on the controller and describe it using the each method on the view side.

lists_controller.rb

class ListsController < ApplicationController
  def index
    @lists = List.all
  end
(abridgement)
end

Also, because we have an association, the items associated with the list can be obtained with list.items.

erb:index.html.erb

<% @lists.each do |list| %>　#List all lists
　<div class="list">
　　<div class="list-name">
List name:<%= list.name %>
　　</div>
　　<% list.items.each do |item| %>　#Display all items in each list in a list
　　　<div class="item-name">
　　　　<%= item.name %>
　　　</div>
　　<% end %>
　</div>
<% end %>

But when I run this code ... At the end of the year, when cats are so busy running ... The terminal is also busy.

Terminal

# @List.behavior of all
  List Load (0.2ms)  SELECT `lists`.* FROM `lists`

# list.items.Behavior of each
  Item Load (0.2ms)  SELECT `items`.* FROM `items` WHERE `items`.`list_id` = 1
  Item Load (0.2ms)  SELECT `items`.* FROM `items` WHERE `items`.`list_id` = 2
  Item Load (0.1ms)  SELECT `items`.* FROM `items` WHERE `items`.`list_id` = 3
  Item Load (0.1ms)  SELECT `items`.* FROM `items` WHERE `items`.`list_id` = 4
  Item Load (0.1ms)  SELECT `items`.* FROM `items` WHERE `items`.`list_id` = 5

Search for items associated with list by list.items.each ** The items table is accessed as many as the number of records in the lists table. ** ** In this way, the N + 1 problem is that SQL is issued more than necessary.

** ~ Why do you say "N + 1" ~ ** Each time SQL accesses the lists table, the items table is issued as many times as there are records in the lists table (5 times in the above).

In this way, for each access to the lists table 1 time, the related table is issuedN times, so it becomes 1 + N, which is called N + 1.

Countermeasures for N + 1 problem

In Rails

You can get all the data related to the model at once. Use the includes method. Specify the relation name ** defined in the association in the argument and define it. (The point is not the table name, but the related name)

Since we have described the association as has_many: items in the List model, the controller will be as follows.

lists_controller.rb(Fix)

class ListsController < ApplicationController
  def index
    @lists = List.all.includes(:items)
  end
(abridgement)
end

As a result ... the busy terminal

Terminal

# @List.behavior of all
  List Load (0.2ms)  SELECT `lists`.* FROM `lists`

# list.items.Behavior of each
  Item Load (0.3ms)  SELECT `items`.* FROM `items` WHERE `items`.`list_id` IN (1, 2, 3, 4, 5)

The information of the items table is also acquired at once, so the behavior of list.items.each is refreshing! !!

By the way,

Specify the ** association name defined in the association ** as an argument and define it. (The point is not the table name, but the related name)

This is a case of reminding me, but in the case of a one-to-many association When getting 1 from many people

(Example)

@items = Item.all.includes(:list)

It will be a description like that. This is because the association is described as belongs_to: list on the child model side.

At the end/impression

It was an N + 1 problem that was just an image of "there is a lot of SQL in the terminal", but I feel that I was able to deepen my understanding this time. Be aware of the N + 1 problem and work on the database. I thought that Christmas like this is sometimes good.

This is a poor article for beginners, but I hope it will be of some help to you. Thank you for reading to the end.

Reference article

[Qiita] N + 1 problem [Pikawaka] Let's solve the Rails/N + 1 problem with the includes method!