[Rails] N + 1 problems and countermeasures

What is the N + 1 problem?

For example, when trying to retrieve multiple (N) data using .each method etc., SQL is issued N times, which leads to performance degradation.

Example

Suppose you have such a one-to-many Use and Message model.

app/model/user.rb


class Message < ApplicationRecord
  has_many :messages
end

app/model/message.rb


class User < ApplicationRecord
  belongs_to :user
end

Get the value to pass to the view in the controller

app/controllers/message_controller.rb


def index
  @messages = Message.all
end

Display content and the name associated with it

app/views/index.html.erb


<% @messages.each do |m| %>
  <span><%= m.content %> </span>
  <span><%= m.user.name %> </span>
<% end %>

As for the SQL at that time, SQL will be issued for each record like this.

Message Load (3.7ms)  SELECT `messages`.* FROM `messages` # +Part 1
  ↳ app/views/messages/index.html.erb:5
  User Load (9.4ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 1 LIMIT 1
  ↳ app/views/messages/index.html.erb:8
  User Load (1.0ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 3 LIMIT 1
  ↳ app/views/messages/index.html.erb:8
  CACHE User Load (0.0ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 3 LIMIT 1  [["id", 3], ["LIMIT", 1]]
  ↳ app/views/messages/index.html.erb:8
  CACHE User Load (0.0ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 3 LIMIT 1  [["id", 3], ["LIMIT", 1]]
  ↳ app/views/messages/index.html.erb:8
・
・
・
(Continued for N pieces)

Countermeasures

Introduce gem bullet. Then, SQL like this will be issued and performance will improve.

Message Load (1.1ms)  SELECT `messages`.* FROM `messages`
  ↳ app/views/messages/index.html.erb:5
  User Load (0.9ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` IN (1, 3, 4)

Recommended Posts

[Rails] N + 1 problems and countermeasures
Rails and FormData
Rails valid? And invalid?
Rails: Difference between resources and resources
Rails Posts and User Linkage
[Rails] require method and permit method
Rails "render method" and "redirect method"
Rails Tutorial Records and Memorandum # 0
rails path and url methods
Ruby syntax errors and countermeasures
Rails is difficult and painful!
Introducing Bootstrap and Font-Awesome (Rails)
Rails is difficult and painful! Ⅱ
[Rails] strftime this and that
Rails web server and application server
Summary of problems and countermeasures when operating IE with WebDriver of Selenium2
[Rails] Save start time and end time
Enable jQuery and Bootstrap in Rails 6 (Rails 6)
[Rails] Difference between find and find_by
[Rails] Validation settings and Japanese localization
Rails model and table naming conventions
Remove "assets" and "turbolinks" in "Rails6".
CRUD features and MVC in Rails
[Rails] Differences and usage of each_with_index and each.with_index
Project ruby and rails version upgrade
Consideration about Rails and Clean Architecture
[rails] Difference between redirect_to and render