LoginSignup
0
0

More than 1 year has passed since last update.

@yoshiki_murakami(Yoshiki Murakami)

「Rails パフォーマンスチューニング」viewファイルで起こるN + 1問題の修正方法

Last updated at Posted at 2022-06-07

controller側ではなく、viewのeachでN + 1が発生していたので修正しました。

Ruby:3.1
Rails:7.0.2.3
MySQL:8.0

仕様

  • userはcompanyをお気に入り(favorite)登録することができる
  • favoriteは中間テーブル
  • companyに紐づくfavoriteの中に、ログイン中のuser.idと同じものがあればCSSのclassを追加

上記の仕様を、この様に書いていた。

companies_controller.rb
class CompaniesController < ApplicationController
  def index
    @companies = Company.all.includes(:addresses).page params[:page]
  end
end
index.html.erb
<% @companies.each do |company| %>
  <!--ここは省略-->

  <% if company.favorites.find_by(user_id: current_user.id, company_id: company.id) %>
    <%= button_to company_favorites_path(company), class: "items-center justify-center outline-none focus:outline-none focus:shadow-none transition-all duration-300 rounded-full w-10 h-10 p-0 grid text-xs bg-blue-500 hover:bg-blue-700 focus:bg-blue-400 shadow-lg shadow-blue-500/30 hover:shadow-blue-700/30" do %>
      <span class="material-icons text-xl">favorite</span>
    <% end %>
  <% else %>
    <%= button_to company_favorites_path(company), class: "items-center justify-center outline-none focus:outline-none focus:shadow-none transition-all duration-300 rounded-full w-10 h-10 p-0 grid text-xs text-white bg-blue-500 hover:bg-blue-700 focus:bg-blue-400 shadow-lg shadow-blue-500/30 hover:shadow-blue-700/30" do %>
      <span class="material-icons text-xl">favorite</span>
    <% end %>
  <% end %>
<% end %>

そうすると、ここで問題が発生。

Started GET "/companies" for 192.168.48.1 at 2022-06-08 00:03:56 +0900
Cannot render console from 192.168.48.1! Allowed networks: 127.0.0.0/127.255.255.255, ::1
Processing by CompaniesController#index as HTML
  Rendering layout layouts/application.html.erb
  Rendering companies/index.html.erb within layouts/application
  Rendered layouts/service/_nav.html.erb (Duration: 1.9ms | Allocations: 46627)
  Rendered layouts/service/_header.html.erb (Duration: 11.1ms | Allocations: 216711)
  Company Load (1.8ms)  SELECT `companies`.* FROM `companies` LIMIT 20 OFFSET 0
  ↳ app/views/companies/index.html.erb:9
  Address Load (3.2ms)  SELECT `addresses`.* FROM `addresses` WHERE `addresses`.`company_id` IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20)
  ↳ app/views/companies/index.html.erb:9
  Favorite Load (2.4ms)  SELECT `favorites`.* FROM `favorites` WHERE `favorites`.`company_id` = 1
  ↳ app/views/companies/index.html.erb:38:in `detect'
  User Load (1.8ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 1 ORDER BY `users`.`id` ASC LIMIT 1
  ↳ app/views/companies/index.html.erb:38
  Favorite Load (1.7ms)  SELECT `favorites`.* FROM `favorites` WHERE `favorites`.`company_id` = 2
  ↳ app/views/companies/index.html.erb:38:in `detect'
  Favorite Load (1.7ms)  SELECT `favorites`.* FROM `favorites` WHERE `favorites`.`company_id` = 3
  ↳ app/views/companies/index.html.erb:38:in `detect'
  Favorite Load (2.5ms)  SELECT `favorites`.* FROM `favorites` WHERE `favorites`.`company_id` = 4
  ↳ app/views/companies/index.html.erb:38:in `detect'
  Favorite Load (2.8ms)  SELECT `favorites`.* FROM `favorites` WHERE `favorites`.`company_id` = 5
  ↳ app/views/companies/index.html.erb:38:in `detect'
  Favorite Load (2.2ms)  SELECT `favorites`.* FROM `favorites` WHERE `favorites`.`company_id` = 6
  ↳ app/views/companies/index.html.erb:38:in `detect'
  Favorite Load (2.1ms)  SELECT `favorites`.* FROM `favorites` WHERE `favorites`.`company_id` = 7
  ↳ app/views/companies/index.html.erb:38:in `detect'
  Favorite Load (2.1ms)  SELECT `favorites`.* FROM `favorites` WHERE `favorites`.`company_id` = 8
  ↳ app/views/companies/index.html.erb:38:in `detect'
  Favorite Load (2.2ms)  SELECT `favorites`.* FROM `favorites` WHERE `favorites`.`company_id` = 9
  ↳ app/views/companies/index.html.erb:38:in `detect'
  Favorite Load (2.2ms)  SELECT `favorites`.* FROM `favorites` WHERE `favorites`.`company_id` = 10
  ↳ app/views/companies/index.html.erb:38:in `detect'
  Favorite Load (2.7ms)  SELECT `favorites`.* FROM `favorites` WHERE `favorites`.`company_id` = 11
  ↳ app/views/companies/index.html.erb:38:in `detect'
  Favorite Load (2.0ms)  SELECT `favorites`.* FROM `favorites` WHERE `favorites`.`company_id` = 12
  ↳ app/views/companies/index.html.erb:38:in `detect'
  Favorite Load (1.8ms)  SELECT `favorites`.* FROM `favorites` WHERE `favorites`.`company_id` = 13
  ↳ app/views/companies/index.html.erb:38:in `detect'
  Favorite Load (2.4ms)  SELECT `favorites`.* FROM `favorites` WHERE `favorites`.`company_id` = 14
  ↳ app/views/companies/index.html.erb:38:in `detect'
  Favorite Load (2.2ms)  SELECT `favorites`.* FROM `favorites` WHERE `favorites`.`company_id` = 15
  ↳ app/views/companies/index.html.erb:38:in `detect'
  Favorite Load (2.2ms)  SELECT `favorites`.* FROM `favorites` WHERE `favorites`.`company_id` = 16
  ↳ app/views/companies/index.html.erb:38:in `detect'
  Favorite Load (2.1ms)  SELECT `favorites`.* FROM `favorites` WHERE `favorites`.`company_id` = 17
  ↳ app/views/companies/index.html.erb:38:in `detect'
  Favorite Load (2.5ms)  SELECT `favorites`.* FROM `favorites` WHERE `favorites`.`company_id` = 18
  ↳ app/views/companies/index.html.erb:38:in `detect'
  Favorite Load (2.3ms)  SELECT `favorites`.* FROM `favorites` WHERE `favorites`.`company_id` = 19
  ↳ app/views/companies/index.html.erb:38:in `detect'
  Favorite Load (2.1ms)  SELECT `favorites`.* FROM `favorites` WHERE `favorites`.`company_id` = 20
  ↳ app/views/companies/index.html.erb:38:in `detect'
  Company Count (6.9ms)  SELECT COUNT(*) FROM `companies`
  ↳ app/views/companies/index.html.erb:54
  Rendered layouts/service/_footer.html.erb (Duration: 1.3ms | Allocations: 34141)
  Rendered companies/index.html.erb within layouts/application (Duration: 1159.4ms | Allocations: 20031008)
  Rendered layout layouts/application.html.erb (Duration: 1370.2ms | Allocations: 22327166)
Completed 200 OK in 1382ms (Views: 1319.3ms | ActiveRecord: 58.0ms | Allocations: 22518667)

Favorite Loadが繰り返し行われている。
これはbulletでも検知出来ていません。

そこで、コードを下記に変更

companies_controller.rb
class CompaniesController < ApplicationController
  def index
    # :favoritesを追加
    @companies = Company.all.includes(:addresses, :favorites).page params[:page]
  end
end
index.html.erb
<% @companies.each do |company| %>
  <!-- ここは省略 -->

  <!-- find_byではなく、detectを使用してDBを叩かない様にする -->
  <% if company.favorites.detect { |favorite| favorite.user_id == current_user.id } %>
    <%= button_to company_favorites_path(company), class: "items-center justify-center outline-none focus:outline-none focus:shadow-none transition-all duration-300 rounded-full w-10 h-10 p-0 grid text-xs bg-blue-500 hover:bg-blue-700 focus:bg-blue-400 shadow-lg shadow-blue-500/30 hover:shadow-blue-700/30" do %>
      <span class="material-icons text-xl">favorite</span>
    <% end %>
  <% else %>
    <%= button_to company_favorites_path(company), class: "items-center justify-center outline-none focus:outline-none focus:shadow-none transition-all duration-300 rounded-full w-10 h-10 p-0 grid text-xs text-white bg-blue-500 hover:bg-blue-700 focus:bg-blue-400 shadow-lg shadow-blue-500/30 hover:shadow-blue-700/30" do %>
      <span class="material-icons text-xl">favorite</span>
    <% end %>
  <% end %>
<% end %>

その結果

Started GET "/companies" for 192.168.48.1 at 2022-06-08 00:05:28 +0900
Cannot render console from 192.168.48.1! Allowed networks: 127.0.0.0/127.255.255.255, ::1
Processing by CompaniesController#index as HTML
  Rendering layout layouts/application.html.erb
  Rendering companies/index.html.erb within layouts/application
  Rendered layouts/service/_nav.html.erb (Duration: 2.0ms | Allocations: 50666)
  Rendered layouts/service/_header.html.erb (Duration: 10.9ms | Allocations: 235560)
  Company Load (1.6ms)  SELECT `companies`.* FROM `companies` LIMIT 20 OFFSET 0
  ↳ app/views/companies/index.html.erb:9
  Address Load (2.9ms)  SELECT `addresses`.* FROM `addresses` WHERE `addresses`.`company_id` IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20)
  ↳ app/views/companies/index.html.erb:9
  Favorite Load (2.2ms)  SELECT `favorites`.* FROM `favorites` WHERE `favorites`.`company_id` IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20)
  ↳ app/views/companies/index.html.erb:9
  User Load (2.0ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 1 ORDER BY `users`.`id` ASC LIMIT 1
  ↳ app/views/companies/index.html.erb:38
  Company Count (7.0ms)  SELECT COUNT(*) FROM `companies`
  ↳ app/views/companies/index.html.erb:54
  Rendered layouts/service/_footer.html.erb (Duration: 1.3ms | Allocations: 37099)
  Rendered companies/index.html.erb within layouts/application (Duration: 661.4ms | Allocations: 15769835)
  Rendered layout layouts/application.html.erb (Duration: 862.9ms | Allocations: 18262511)
Completed 200 OK in 875ms (Views: 854.4ms | ActiveRecord: 15.7ms | Allocations: 18473245)

チューニング結果

# チューニング前
Completed 200 OK in 1382ms (Views: 1319.3ms | ActiveRecord: 58.0ms | Allocations: 22518667)

# チューニング後
Completed 200 OK in 875ms (Views: 854.4ms | ActiveRecord: 15.7ms | Allocations: 18473245)

おしまい。

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0