LoginSignup
1
1

More than 3 years have passed since last update.

@dym330

大量SQL発行への対応奮闘記

Posted at

はじめに

ポートフォリオをRailsにて作成中にSQLが大量に発行されていたため、原因の追求を行ったときのまとめです。

実際のSQL発行の様子

以下はTwitterのような、投稿ができるアプリを作成し、投稿一覧画面に遷移した際のlogです

Employee Load (3.7ms)  SELECT  `employees`.* FROM `employees` WHERE `employees`.`id` = 7 LIMIT 1
  ↳ app/helpers/public/sessions_helper.rb:11
  Company Load (1.6ms)  SELECT  `companies`.* FROM `companies` WHERE `companies`.`id` = 2 LIMIT 1
  ↳ app/controllers/application_controller.rb:5
  CACHE Company Load (0.1ms)  SELECT  `companies`.* FROM `companies` WHERE `companies`.`id` = 2 LIMIT 1  [["id", 2], ["LIMIT", 1]]
  ↳ app/controllers/application_controller.rb:5
   (2.6ms)  SELECT `employees`.`id` FROM `employees` WHERE `employees`.`company_id` = 2
  ↳ app/controllers/application_controller.rb:5
   (3.6ms)  SELECT COUNT(*) FROM `schedules` WHERE `schedules`.`employee_id` IN (7, 8, 9, 10, 11, 12) AND `schedules`.`schedule_status` = 1
  ↳ app/controllers/application_controller.rb:6
   (1.7ms)  SELECT COUNT(*) FROM `schedules` WHERE `schedules`.`employee_id` = 7 AND `schedules`.`comment_status` = TRUE
  ↳ app/controllers/application_controller.rb:7
  CACHE Company Load (0.0ms)  SELECT  `companies`.* FROM `companies` WHERE `companies`.`id` = 2 LIMIT 1  [["id", 2], ["LIMIT", 1]]
  ↳ app/controllers/public/schedules_controller.rb:6
  CACHE  (0.0ms)  SELECT `employees`.`id` FROM `employees` WHERE `employees`.`company_id` = 2
  ↳ app/controllers/public/schedules_controller.rb:7
  Rendering public/schedules/index.html.erb within layouts/application
  Rendered shared/_sidebar.html.erb (1.2ms)
  Schedule Load (1.7ms)  SELECT `schedules`.* FROM `schedules` WHERE `schedules`.`employee_id` IN (7, 8, 9, 10, 11, 12) ORDER BY `schedules`.`id` DESC
  ↳ app/views/public/schedules/index.html.erb:7
  Employee Load (1.0ms)  SELECT  `employees`.* FROM `employees` WHERE `employees`.`id` = 12 LIMIT 1
  ↳ app/views/shared/_schedule_post.html.erb:5
  ScheduleFavorite Exists (1.3ms)  SELECT  1 AS one FROM `schedule_favorites` WHERE `schedule_favorites`.`schedule_id` = 29 AND `schedule_favorites`.`employee_id` = 7 LIMIT 1
  ↳ app/models/schedule.rb:31
   (0.7ms)  SELECT COUNT(*) FROM `schedule_favorites` WHERE `schedule_favorites`.`schedule_id` = 29
  ↳ app/views/shared/_favorite_button.html.erb:11
  Rendered shared/_favorite_button.html.erb (6.5ms)
   (0.7ms)  SELECT COUNT(*) FROM `schedule_comments` WHERE `schedule_comments`.`schedule_id` = 29
  ↳ app/views/shared/_schedule_post.html.erb:46
  Rendered shared/_schedule_post.html.erb (37.7ms)
  CACHE Employee Load (0.0ms)  SELECT  `employees`.* FROM `employees` WHERE `employees`.`id` = 12 LIMIT 1  [["id", 12], ["LIMIT", 1]]
  ↳ app/views/shared/_schedule_post.html.erb:5
  ScheduleFavorite Exists (1.3ms)  SELECT  1 AS one FROM `schedule_favorites` WHERE `schedule_favorites`.`schedule_id` = 28 AND `schedule_favorites`.`employee_id` = 7 LIMIT 1
  ↳ app/models/schedule.rb:31
   (0.8ms)  SELECT COUNT(*) FROM `schedule_favorites` WHERE `schedule_favorites`.`schedule_id` = 28
  ↳ app/views/shared/_favorite_button.html.erb:11
  Rendered shared/_favorite_button.html.erb (6.1ms)
   (0.8ms)  SELECT COUNT(*) FROM `schedule_comments` WHERE `schedule_comments`.`schedule_id` = 28
  ↳ app/views/shared/_schedule_post.html.erb:46
  Rendered shared/_schedule_post.html.erb (33.8ms)
  Employee Load (1.4ms)  SELECT  `employees`.* FROM `employees` WHERE `employees`.`id` = 11 LIMIT 1
  ↳ app/views/shared/_schedule_post.html.erb:5
  ScheduleFavorite Exists (1.4ms)  SELECT  1 AS one FROM `schedule_favorites` WHERE `schedule_favorites`.`schedule_id` = 27 AND `schedule_favorites`.`employee_id` = 7 LIMIT 1
  ↳ app/models/schedule.rb:31
   (0.8ms)  SELECT COUNT(*) FROM `schedule_favorites` WHERE `schedule_favorites`.`schedule_id` = 27
  ↳ app/views/shared/_favorite_button.html.erb:11
  Rendered shared/_favorite_button.html.erb (6.8ms)
   (0.8ms)  SELECT COUNT(*) FROM `schedule_comments` WHERE `schedule_comments`.`schedule_id` = 27
  ↳ app/views/shared/_schedule_post.html.erb:46
  Rendered shared/_schedule_post.html.erb (37.7ms)
  CACHE Employee Load (0.0ms)  SELECT  `employees`.* FROM `employees` WHERE `employees`.`id` = 11 LIMIT 1  [["id", 11], ["LIMIT", 1]]
  ↳ app/views/shared/_schedule_post.html.erb:5
  ScheduleFavorite Exists (1.2ms)  SELECT  1 AS one FROM `schedule_favorites` WHERE `schedule_favorites`.`schedule_id` = 26 AND `schedule_favorites`.`employee_id` = 7 LIMIT 1
  ↳ app/models/schedule.rb:31
   (0.7ms)  SELECT COUNT(*) FROM `schedule_favorites` WHERE `schedule_favorites`.`schedule_id` = 26
  ↳ app/views/shared/_favorite_button.html.erb:11
  Rendered shared/_favorite_button.html.erb (5.5ms)
   (0.9ms)  SELECT COUNT(*) FROM `schedule_comments` WHERE `schedule_comments`.`schedule_id` = 26
  ↳ app/views/shared/_schedule_post.html.erb:46
  Rendered shared/_schedule_post.html.erb (34.3ms)
  CACHE Employee Load (0.0ms)  SELECT  `employees`.* FROM `employees` WHERE `employees`.`id` = 11 LIMIT 1  [["id", 11], ["LIMIT", 1]]
  ↳ app/views/shared/_schedule_post.html.erb:5
  ScheduleFavorite Exists (1.2ms)  SELECT  1 AS one FROM `schedule_favorites` WHERE `schedule_favorites`.`schedule_id` = 25 AND `schedule_favorites`.`employee_id` = 7 LIMIT 1
  ↳ app/models/schedule.rb:31
   (0.9ms)  SELECT COUNT(*) FROM `schedule_favorites` WHERE `schedule_favorites`.`schedule_id` = 25
  ↳ app/views/shared/_favorite_button.html.erb:11
  Rendered shared/_favorite_button.html.erb (6.2ms)
   (1.1ms)  SELECT COUNT(*) FROM `schedule_comments` WHERE `schedule_comments`.`schedule_id` = 25
  ↳ app/views/shared/_schedule_post.html.erb:46
  Rendered shared/_schedule_post.html.erb (36.6ms)
  Employee Load (1.1ms)  SELECT  `employees`.* FROM `employees` WHERE `employees`.`id` = 10 LIMIT 1
  ↳ app/views/shared/_schedule_post.html.erb:5
  ScheduleFavorite Exists (1.2ms)  SELECT  1 AS one FROM `schedule_favorites` WHERE `schedule_favorites`.`schedule_id` = 24 AND `schedule_favorites`.`employee_id` = 7 LIMIT 1
  ↳ app/models/schedule.rb:31
   (0.8ms)  SELECT COUNT(*) FROM `schedule_favorites` WHERE `schedule_favorites`.`schedule_id` = 24
  ↳ app/views/shared/_favorite_button.html.erb:11
  Rendered shared/_favorite_button.html.erb (5.4ms)
   (1.0ms)  SELECT COUNT(*) FROM `schedule_comments` WHERE `schedule_comments`.`schedule_id` = 24
  ↳ app/views/shared/_schedule_post.html.erb:46
  Rendered shared/_schedule_post.html.erb (33.6ms)
  CACHE Employee Load (0.0ms)  SELECT  `employees`.* FROM `employees` WHERE `employees`.`id` = 10 LIMIT 1  [["id", 10], ["LIMIT", 1]]
  ↳ app/views/shared/_schedule_post.html.erb:5
  ScheduleFavorite Exists (1.3ms)  SELECT  1 AS one FROM `schedule_favorites` WHERE `schedule_favorites`.`schedule_id` = 23 AND `schedule_favorites`.`employee_id` = 7 LIMIT 1
  ↳ app/models/schedule.rb:31
   (1.8ms)  SELECT COUNT(*) FROM `schedule_favorites` WHERE `schedule_favorites`.`schedule_id` = 23
  ↳ app/views/shared/_favorite_button.html.erb:11
  Rendered shared/_favorite_button.html.erb (8.5ms)
   (1.1ms)  SELECT COUNT(*) FROM `schedule_comments` WHERE `schedule_comments`.`schedule_id` = 23
  ↳ app/views/shared/_schedule_post.html.erb:46
  Rendered shared/_schedule_post.html.erb (52.0ms)
  CACHE Employee Load (0.0ms)  SELECT  `employees`.* FROM `employees` WHERE `employees`.`id` = 10 LIMIT 1  [["id", 10], ["LIMIT", 1]]
  ↳ app/views/shared/_schedule_post.html.erb:5
  ScheduleFavorite Exists (2.5ms)  SELECT  1 AS one FROM `schedule_favorites` WHERE `schedule_favorites`.`schedule_id` = 22 AND `schedule_favorites`.`employee_id` = 7 LIMIT 1
  ↳ app/models/schedule.rb:31
   (1.2ms)  SELECT COUNT(*) FROM `schedule_favorites` WHERE `schedule_favorites`.`schedule_id` = 22
  ↳ app/views/shared/_favorite_button.html.erb:11
  Rendered shared/_favorite_button.html.erb (9.9ms)
   (1.3ms)  SELECT COUNT(*) FROM `schedule_comments` WHERE `schedule_comments`.`schedule_id` = 22
  ↳ app/views/shared/_schedule_post.html.erb:46
  Rendered shared/_schedule_post.html.erb (48.9ms)
  Employee Load (0.9ms)  SELECT  `employees`.* FROM `employees` WHERE `employees`.`id` = 9 LIMIT 1
  ↳ app/views/shared/_schedule_post.html.erb:5
  ScheduleFavorite Exists (1.2ms)  SELECT  1 AS one FROM `schedule_favorites` WHERE `schedule_favorites`.`schedule_id` = 21 AND `schedule_favorites`.`employee_id` = 7 LIMIT 1
  ↳ app/models/schedule.rb:31
   (1.2ms)  SELECT COUNT(*) FROM `schedule_favorites` WHERE `schedule_favorites`.`schedule_id` = 21
  ↳ app/views/shared/_favorite_button.html.erb:11
  Rendered shared/_favorite_button.html.erb (8.5ms)
   (1.0ms)  SELECT COUNT(*) FROM `schedule_comments` WHERE `schedule_comments`.`schedule_id` = 21
  ↳ app/views/shared/_schedule_post.html.erb:46
  Rendered shared/_schedule_post.html.erb (39.8ms)
  CACHE Employee Load (0.0ms)  SELECT  `employees`.* FROM `employees` WHERE `employees`.`id` = 9 LIMIT 1  [["id", 9], ["LIMIT", 1]]
  ↳ app/views/shared/_schedule_post.html.erb:5
  ScheduleFavorite Exists (1.3ms)  SELECT  1 AS one FROM `schedule_favorites` WHERE `schedule_favorites`.`schedule_id` = 20 AND `schedule_favorites`.`employee_id` = 7 LIMIT 1
  ↳ app/models/schedule.rb:31
   (0.8ms)  SELECT COUNT(*) FROM `schedule_favorites` WHERE `schedule_favorites`.`schedule_id` = 20
  ↳ app/views/shared/_favorite_button.html.erb:11
  Rendered shared/_favorite_button.html.erb (6.3ms)
   (1.0ms)  SELECT COUNT(*) FROM `schedule_comments` WHERE `schedule_comments`.`schedule_id` = 20
  ↳ app/views/shared/_schedule_post.html.erb:46
  Rendered shared/_schedule_post.html.erb (36.4ms)
  CACHE Employee Load (0.0ms)  SELECT  `employees`.* FROM `employees` WHERE `employees`.`id` = 9 LIMIT 1  [["id", 9], ["LIMIT", 1]]
  ↳ app/views/shared/_schedule_post.html.erb:5
  ScheduleFavorite Exists (1.2ms)  SELECT  1 AS one FROM `schedule_favorites` WHERE `schedule_favorites`.`schedule_id` = 19 AND `schedule_favorites`.`employee_id` = 7 LIMIT 1
  ↳ app/models/schedule.rb:31
   (0.8ms)  SELECT COUNT(*) FROM `schedule_favorites` WHERE `schedule_favorites`.`schedule_id` = 19
  ↳ app/views/shared/_favorite_button.html.erb:11
  Rendered shared/_favorite_button.html.erb (6.1ms)
   (1.1ms)  SELECT COUNT(*) FROM `schedule_comments` WHERE `schedule_comments`.`schedule_id` = 19
  ↳ app/views/shared/_schedule_post.html.erb:46
  Rendered shared/_schedule_post.html.erb (32.0ms)
  Employee Load (1.4ms)  SELECT  `employees`.* FROM `employees` WHERE `employees`.`id` = 8 LIMIT 1
  ↳ app/views/shared/_schedule_post.html.erb:5
  ScheduleFavorite Exists (1.1ms)  SELECT  1 AS one FROM `schedule_favorites` WHERE `schedule_favorites`.`schedule_id` = 18 AND `schedule_favorites`.`employee_id` = 7 LIMIT 1
  ↳ app/models/schedule.rb:31
   (0.8ms)  SELECT COUNT(*) FROM `schedule_favorites` WHERE `schedule_favorites`.`schedule_id` = 18
  ↳ app/views/shared/_favorite_button.html.erb:11
  Rendered shared/_favorite_button.html.erb (6.1ms)
   (0.9ms)  SELECT COUNT(*) FROM `schedule_comments` WHERE `schedule_comments`.`schedule_id` = 18
  ↳ app/views/shared/_schedule_post.html.erb:46
  Rendered shared/_schedule_post.html.erb (39.1ms)
  CACHE Employee Load (0.0ms)  SELECT  `employees`.* FROM `employees` WHERE `employees`.`id` = 8 LIMIT 1  [["id", 8], ["LIMIT", 1]]
  ↳ app/views/shared/_schedule_post.html.erb:5
  ScheduleFavorite Exists (1.2ms)  SELECT  1 AS one FROM `schedule_favorites` WHERE `schedule_favorites`.`schedule_id` = 17 AND `schedule_favorites`.`employee_id` = 7 LIMIT 1
  ↳ app/models/schedule.rb:31
   (0.8ms)  SELECT COUNT(*) FROM `schedule_favorites` WHERE `schedule_favorites`.`schedule_id` = 17
  ↳ app/views/shared/_favorite_button.html.erb:11
  Rendered shared/_favorite_button.html.erb (6.2ms)
   (0.8ms)  SELECT COUNT(*) FROM `schedule_comments` WHERE `schedule_comments`.`schedule_id` = 17
  ↳ app/views/shared/_schedule_post.html.erb:46
  Rendered shared/_schedule_post.html.erb (35.5ms)
  CACHE Employee Load (0.0ms)  SELECT  `employees`.* FROM `employees` WHERE `employees`.`id` = 8 LIMIT 1  [["id", 8], ["LIMIT", 1]]
  ↳ app/views/shared/_schedule_post.html.erb:5
  ScheduleFavorite Exists (1.2ms)  SELECT  1 AS one FROM `schedule_favorites` WHERE `schedule_favorites`.`schedule_id` = 16 AND `schedule_favorites`.`employee_id` = 7 LIMIT 1
  ↳ app/models/schedule.rb:31
   (0.8ms)  SELECT COUNT(*) FROM `schedule_favorites` WHERE `schedule_favorites`.`schedule_id` = 16
  ↳ app/views/shared/_favorite_button.html.erb:11
  Rendered shared/_favorite_button.html.erb (6.6ms)
   (0.7ms)  SELECT COUNT(*) FROM `schedule_comments` WHERE `schedule_comments`.`schedule_id` = 16

コード内容の確認

まず、logを確認し、SQLが発行されている記述を抜粋しました。

#module Public::SessionsHelper
@current_employee ||= Employee.find_by(id: session[:employee_id])

#class ApplicationController < ActionController::Base
@schedule_questions_count = Schedule
.where(employee_id:Company.find(current_employee.company.id).employees.ids)
.where(schedule_status: 1).count

@schedule_replies_count = Schedule
.where(employee_id: current_employee.id).where(comment_status: true).count

#class Public::SchedulesController < ApplicationController
@company = Company.find(current_employee.company.id)

@schedules = Schedule.where(employee_id: @company.employees.ids).order(id: :DESC)

#View　（ここからの発行が何度も発生していた）
<% @schedules.each do |schedule| %>
schedule.employee
schedule.schedule_favorites.count
schedule.schedule_comments.count
schedule.schedule_favorites.where(employee_id: employee.id).exists?
<% end %>

大量なSQLの一番の原因としてviewの表示を行う際に、
@schedules.eachの回数分SQLが発行されていることを確認しました。
（N+1問題）

解決案

コントローラにて全てのデータを予め取得しておくことで、発行回数を減らしたいため、
以下のコード実行時に、viewで必要なデータを持たせたることで、解決すると考えました。

@company = Company.find(current_employee.company.id)
@schedules = Schedule.where(employee_id: @company.employees.ids).order(id: :DESC)

実行内容

includesメソッドを用いることで、viewの表示に必要な要素をまとめて取得

@company = Company.find(current_employee.company.id)
@schedules = Schedule.includes(:employee, :schedule_comments, :schedule_favorites)
                     .where(employee_id: @company.employees.ids).order(id: :DESC)

実行結果

schedule.employeeは実行されなくなったが、他ではeachで回る回数分SQLが発行

(0.8ms)  SELECT COUNT(*) FROM `schedule_favorites` WHERE `schedule_favorites`.`schedule_id` = 20
(1.1ms)  SELECT COUNT(*) FROM `schedule_comments` WHERE `schedule_comments`.`schedule_id` = 20
(1.1ms)  SELECT  1 AS one FROM `schedule_favorites` WHERE `schedule_favorites`.`schedule_id` = 28
         AND `schedule_favorites`.`employee_id` = 7 LIMIT 1

解決案

viewの中で、countやwhere(employee_id: employee.id).exists?を使用しているが、
調べたところ、SQLを発行するメソッドのため、
他のメソッドに置き換える事で防げるのではないかと考えました。

実行内容

いいねやコメント数をカウントするだけのためsizeを使用

schedule.schedule_favorites.size
schedule.schedule_comments.size
#schedule.schedule_comments.count

配列の中のハッシュにemployee.idが存在しているかを確認したいだけのためfindメソッドを使用し、
二重感嘆符をつけることで、真偽値がかえってくるように変更

!!schedule_favorites.find{ |i| i[:employee_id] == employee.id }
#schedule.schedule_favorites.where(employee_id: employee.id).exists?

実行結果

下記のみのSQLの発行でviewの表示に成功

  Schedule Load (2.0ms)  SELECT `schedules`.* FROM `schedules` WHERE `schedules`.`employee_id` IN (7, 8, 9, 10, 11, 12) ORDER BY `schedules`.`id` DESC
  Employee Load (1.9ms)  SELECT `employees`.* FROM `employees` WHERE `employees`.`id` IN (12, 11, 10, 9, 8)
  ScheduleComment Load (1.4ms)  SELECT `schedule_comments`.* FROM `schedule_comments` WHERE `schedule_comments`.`schedule_id` IN (29, 28, 27, 26, 25, 24, 23, 22, 21, 20, 19, 18, 17, 16)
  ScheduleFavorite Load (1.3ms)  SELECT `schedule_favorites`.* FROM `schedule_favorites` WHERE `schedule_favorites`.`schedule_id` IN (29, 28, 27, 26, 25, 24, 23, 22, 21, 20, 19, 18, 17, 16)

まとめ

  • 親子関係があり、例えば、親.each内で親.子の形で表示させる場合、includesメソッドを用いて事前に読み込んでおく
  • countメソッドなど、SQLを発行するメソッドは、データがすでにある場合は他の方法で実行する
1
1
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
1
1