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

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

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

```sql
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が発行されている記述を抜粋しました。

```ruby
#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で必要なデータを持たせたることで、解決すると考えました。

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

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

```ruby
@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が発行

```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を使用

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

```ruby
!!schedule_favorites.find{ |i| i[:employee_id] == employee.id }
#schedule.schedule_favorites.where(employee_id: employee.id).exists?
```
#実行結果
下記のみのSQLの発行でviewの表示に成功

```sql
  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を発行するメソッドは、データがすでにある場合は他の方法で実行する