search

cat-in-136 / redmine_hearts

a redmine plugin which provides intra-Redmine Like/Fav reactions
https://www.redmine.org/plugins/redmine_hearts
GNU General Public License v2.0
33 stars 3 forks source link

N+1 problem at comments in issue pages #4

Closed cat-in-136 closed 6 years ago

cat-in-136 commented 6 years ago

heart_link_with_counter is called for each Journal in the issue pages. This yields N times query calls where N is the number of Journal.

diffshare commented 6 years ago

I confirmed that this N+1 problem will output the following log.

  Rendered issues/_relations.html.erb (1.4ms)
  Heart Exists (0.0ms)  SELECT  1 AS one FROM "hearts" WHERE "hearts"."heartable_type" = ? AND "hearts"."user_id" = ? AND "hearts"."heartable_id" = 1 LIMIT 1  [["heartable_type", "Journal"], ["user_
   (0.0ms)  SELECT COUNT(*) FROM "users" INNER JOIN "hearts" ON "users"."id" = "hearts"."user_id" WHERE "users"."type" IN ('User', 'AnonymousUser') AND "hearts"."heartable_id" = ? AND "hearts"."hear
  Rendered plugins/redmine_hearts/app/views/hooks/redmine_hearts/_view_issues_history_journal_bottom.html.erb (1.6ms)
  Heart Exists (0.0ms)  SELECT  1 AS one FROM "hearts" WHERE "hearts"."heartable_type" = ? AND "hearts"."user_id" = ? AND "hearts"."heartable_id" = 2 LIMIT 1  [["heartable_type", "Journal"], ["user_
   (0.0ms)  SELECT COUNT(*) FROM "users" INNER JOIN "hearts" ON "users"."id" = "hearts"."user_id" WHERE "users"."type" IN ('User', 'AnonymousUser') AND "hearts"."heartable_id" = ? AND "hearts"."hear
  Rendered plugins/redmine_hearts/app/views/hooks/redmine_hearts/_view_issues_history_journal_bottom.html.erb (1.3ms)
  Heart Exists (0.0ms)  SELECT  1 AS one FROM "hearts" WHERE "hearts"."heartable_type" = ? AND "hearts"."user_id" = ? AND "hearts"."heartable_id" = 3 LIMIT 1  [["heartable_type", "Journal"], ["user_
   (0.0ms)  SELECT COUNT(*) FROM "users" INNER JOIN "hearts" ON "users"."id" = "hearts"."user_id" WHERE "users"."type" IN ('User', 'AnonymousUser') AND "hearts"."heartable_id" = ? AND "hearts"."hear
  Rendered plugins/redmine_hearts/app/views/hooks/redmine_hearts/_view_issues_history_journal_bottom.html.erb (1.3ms)
  Heart Exists (0.0ms)  SELECT  1 AS one FROM "hearts" WHERE "hearts"."heartable_type" = ? AND "hearts"."user_id" = ? AND "hearts"."heartable_id" = 4 LIMIT 1  [["heartable_type", "Journal"], ["user_
   (0.0ms)  SELECT COUNT(*) FROM "users" INNER JOIN "hearts" ON "users"."id" = "hearts"."user_id" WHERE "users"."type" IN ('User', 'AnonymousUser') AND "hearts"."heartable_id" = ? AND "hearts"."hear
  Rendered plugins/redmine_hearts/app/views/hooks/redmine_hearts/_view_issues_history_journal_bottom.html.erb (1.3ms)
  Heart Exists (0.0ms)  SELECT  1 AS one FROM "hearts" WHERE "hearts"."heartable_type" = ? AND "hearts"."user_id" = ? AND "hearts"."heartable_id" = 5 LIMIT 1  [["heartable_type", "Journal"], ["user_
   (0.0ms)  SELECT COUNT(*) FROM "users" INNER JOIN "hearts" ON "users"."id" = "hearts"."user_id" WHERE "users"."type" IN ('User', 'AnonymousUser') AND "hearts"."heartable_id" = ? AND "hearts"."hear
  Rendered plugins/redmine_hearts/app/views/hooks/redmine_hearts/_view_issues_history_journal_bottom.html.erb (1.3ms)
  Heart Exists (0.0ms)  SELECT  1 AS one FROM "hearts" WHERE "hearts"."heartable_type" = ? AND "hearts"."user_id" = ? AND "hearts"."heartable_id" = 6 LIMIT 1  [["heartable_type", "Journal"], ["user_
   (0.0ms)  SELECT COUNT(*) FROM "users" INNER JOIN "hearts" ON "users"."id" = "hearts"."user_id" WHERE "users"."type" IN ('User', 'AnonymousUser') AND "hearts"."heartable_id" = ? AND "hearts"."hear
  Rendered plugins/redmine_hearts/app/views/hooks/redmine_hearts/_view_issues_history_journal_bottom.html.erb (1.4ms)
  Heart Exists (0.0ms)  SELECT  1 AS one FROM "hearts" WHERE "hearts"."heartable_type" = ? AND "hearts"."user_id" = ? AND "hearts"."heartable_id" = 7 LIMIT 1  [["heartable_type", "Journal"], ["user_
   (0.0ms)  SELECT COUNT(*) FROM "users" INNER JOIN "hearts" ON "users"."id" = "hearts"."user_id" WHERE "users"."type" IN ('User', 'AnonymousUser') AND "hearts"."heartable_id" = ? AND "hearts"."hear
  Rendered plugins/redmine_hearts/app/views/hooks/redmine_hearts/_view_issues_history_journal_bottom.html.erb (1.5ms)
cat-in-136 commented 6 years ago

Sure. The purpose of this issue is to reduce them!

cat-in-136 commented 6 years ago

The above changeset does work. But controller.instance_variable_set(:@__journal_heart_link_cache, links) is very ugly.

It seems to be still better to expand all the "heart button with counter" of the journals at the view_layouts_base_content hook, and then transplant them to proper position using "transplant_heart_link_with_counter.js".