search

miminashi / ytdlor

youtube-dlのWebフロントエンド
6 stars 3 forks source link

index.html のレンダリングが遅い #23

Closed miminashi closed 2 years ago

miminashi commented 2 years ago

N+1クエリになってしまってる

N=2 の場合

Started GET "/" for 127.0.0.1 at 2022-09-20 01:33:17 +0900
Processing by ArchivesController#index as HTML
  Rendering layout layouts/application.html.erb
  Rendering archives/index.html.erb within layouts/application
  Archive Load (0.2ms)  SELECT "archives".* FROM "archives" ORDER BY "archives"."id" DESC
  ↳ app/views/archives/index.html.erb:15
  ActiveStorage::Attachment Load (0.1ms)  SELECT "active_storage_attachments".* FROM "active_storage_attachments" WHERE "active_storage_attachments"."record_id" = ? AND "active_storage_attachments"."record_type" = ? AND "active_storage_attachments"."name" = ? LIMIT ?  [["record_id", 74], ["record_type", "Archive"], ["name", "thumbnail"], ["LIMIT", 1]]
  ↳ app/views/archives/_archive.html.erb:4
  ActiveStorage::Blob Load (0.1ms)  SELECT "active_storage_blobs".* FROM "active_storage_blobs" WHERE "active_storage_blobs"."id" = ? LIMIT ?  [["id", 194], ["LIMIT", 1]]
  ↳ app/views/archives/_archive.html.erb:5
  ActiveStorage::Attachment Load (0.1ms)  SELECT "active_storage_attachments".* FROM "active_storage_attachments" WHERE "active_storage_attachments"."record_id" = ? AND "active_storage_attachments"."record_type" = ? AND "active_storage_attachments"."name" = ? LIMIT ?  [["record_id", 73], ["record_type", "Archive"], ["name", "thumbnail"], ["LIMIT", 1]]
  ↳ app/views/archives/_archive.html.erb:4
  ActiveStorage::Blob Load (0.1ms)  SELECT "active_storage_blobs".* FROM "active_storage_blobs" WHERE "active_storage_blobs"."id" = ? LIMIT ?  [["id", 191], ["LIMIT", 1]]
  ↳ app/views/archives/_archive.html.erb:5
  Rendered collection of archives/_archive.html.erb [2 times] (Duration: 6.5ms | Allocations: 4381)
  Rendered archives/index.html.erb within layouts/application (Duration: 8.3ms | Allocations: 5478)
  Rendered layout layouts/application.html.erb (Duration: 12.1ms | Allocations: 9204)
Completed 200 OK in 13ms (Views: 12.2ms | ActiveRecord: 0.4ms | Allocations: 9577)

N=20くらい の場合

Started GET "/" for 127.0.0.1 at 2022-09-20 01:19:25 +0900
Processing by ArchivesController#index as HTML
  Rendering layout layouts/application.html.erb
  Rendering archives/index.html.erb within layouts/application
  Archive Load (0.2ms)  SELECT "archives".* FROM "archives" ORDER BY "archives"."id" DESC
  ↳ app/views/archives/index.html.erb:15
  ActiveStorage::Attachment Load (0.1ms)  SELECT "active_storage_attachments".* FROM "active_storage_attachments" WHERE "active_storage_attachments"."record_id" = ? AND "active_storage_attachments"."record_type" = ? AND "active_storage_attachments"."name" = ? LIMIT ?  [["record_id", 94], ["record_type", "Archive"], ["name", "thumbnail"], ["LIMIT", 1]]
  ↳ app/views/archives/_archive.html.erb:4
  ActiveStorage::Blob Load (0.1ms)  SELECT "active_storage_blobs".* FROM "active_storage_blobs" WHERE "active_storage_blobs"."id" = ? LIMIT ?  [["id", 254], ["LIMIT", 1]]
  ↳ app/views/archives/_archive.html.erb:5
  ActiveStorage::Attachment Load (0.1ms)  SELECT "active_storage_attachments".* FROM "active_storage_attachments" WHERE "active_storage_attachments"."record_id" = ? AND "active_storage_attachments"."record_type" = ? AND "active_storage_attachments"."name" = ? LIMIT ?  [["record_id", 93], ["record_type", "Archive"], ["name", "thumbnail"], ["LIMIT", 1]]
  ↳ app/views/archives/_archive.html.erb:4
  ActiveStorage::Blob Load (0.2ms)  SELECT "active_storage_blobs".* FROM "active_storage_blobs" WHERE "active_storage_blobs"."id" = ? LIMIT ?  [["id", 251], ["LIMIT", 1]]
  ↳ app/views/archives/_archive.html.erb:5
  ActiveStorage::Attachment Load (0.1ms)  SELECT "active_storage_attachments".* FROM "active_storage_attachments" WHERE "active_storage_attachments"."record_id" = ? AND "active_storage_attachments"."record_type" = ? AND "active_storage_attachments"."name" = ? LIMIT ?  [["record_id", 92], ["record_type", "Archive"], ["name", "thumbnail"], ["LIMIT", 1]]
  ↳ app/views/archives/_archive.html.erb:4
  ActiveStorage::Blob Load (0.1ms)  SELECT "active_storage_blobs".* FROM "active_storage_blobs" WHERE "active_storage_blobs"."id" = ? LIMIT ?  [["id", 248], ["LIMIT", 1]]
  ↳ app/views/archives/_archive.html.erb:5
  ActiveStorage::Attachment Load (0.1ms)  SELECT "active_storage_attachments".* FROM "active_storage_attachments" WHERE "active_storage_attachments"."record_id" = ? AND "active_storage_attachments"."record_type" = ? AND "active_storage_attachments"."name" = ? LIMIT ?  [["record_id", 91], ["record_type", "Archive"], ["name", "thumbnail"], ["LIMIT", 1]]
  ↳ app/views/archives/_archive.html.erb:4
  ActiveStorage::Blob Load (0.1ms)  SELECT "active_storage_blobs".* FROM "active_storage_blobs" WHERE "active_storage_blobs"."id" = ? LIMIT ?  [["id", 245], ["LIMIT", 1]]
  ↳ app/views/archives/_archive.html.erb:5
.
.
.
  ActiveStorage::Attachment Load (0.1ms)  SELECT "active_storage_attachments".* FROM "active_storage_attachments" WHERE "active_storage_attachments"."record_id" = ? AND "active_storage_attachments"."record_type" = ? AND "active_storage_attachments"."name" = ? LIMIT ?  [["record_id", 73], ["record_type", "Archive"], ["name", "thumbnail"], ["LIMIT", 1]]
  ↳ app/views/archives/_archive.html.erb:4
  ActiveStorage::Blob Load (0.1ms)  SELECT "active_storage_blobs".* FROM "active_storage_blobs" WHERE "active_storage_blobs"."id" = ? LIMIT ?  [["id", 191], ["LIMIT", 1]]
  ↳ app/views/archives/_archive.html.erb:5
  Rendered collection of archives/_archive.html.erb [22 times] (Duration: 72.1ms | Allocations: 46258)
  Rendered archives/index.html.erb within layouts/application (Duration: 73.9ms | Allocations: 47472)
  Rendered layout layouts/application.html.erb (Duration: 78.0ms | Allocations: 51222)
Completed 200 OK in 79ms (Views: 74.5ms | ActiveRecord: 3.9ms | Allocations: 51560)

このへんでも言及されているようにscopeかなにかをつかってjoinされるようにする。 https://railsguides.jp/active_storage_overview.html#%E9%81%85%E5%BB%B6%E8%AA%AD%E3%81%BF%E8%BE%BC%E3%81%BF%E3%81%A8%E3%82%A4%E3%83%9F%E3%83%87%E3%82%A3%E3%82%A8%E3%82%A4%E3%83%88%E8%AA%AD%E3%81%BF%E8%BE%BC%E3%81%BF

miminashi commented 2 years ago

archive#indexにアクセスしたときのクエリの数を数えてみたら、レコードが7つある状態で15回クエリが発行されていた。

miminashi commented 2 years ago

https://api.rubyonrails.org/classes/ActiveStorage/Attached/Model.html#method-i-has_one_attached with_attached_foo を使うとよさそう

モデルが以下のようになっているとき、

has_one_attached :thumbnail

以下のようにすると関連するActiveStorageのレコードを同時にまとめてとってきてくれるようだ。

Archive.with_attached_thumbnail

複数のAttachedがある場合は、以下のようにすればいいっぽい。

Archive.with_attached_thumbnail.with_attached_video

ただし、joinされるわけではなく、Attachedごとに別々のクエリになるようだった。 これをEager Loadingというらしい。

miminashi commented 2 years ago

体感としてわかる程度に改善できたようなのでクローズ