Fix N+1 queries when loading participants for a commit note

We saw about 10,000 SQL queries for some commits in the NewNoteWorker,
which stalled the Sidekiq queue for other new notes. The notification
service took up to 8 minutes to process the commits. Avoiding this
N+1 query brings the time down significantly.

Closes #45526

3 files changed, 23 insertions, 1 deletions

diff --git a/app/models/commit.rb b/app/models/commit.rb
index de860df4b9c..9750e9298ec 100644
--- a/app/models/commit.rb
+++ b/app/models/commit.rb
@@ -248,7 +248,7 @@ class Commit
   end
 
   def notes_with_associations
-    notes.includes(:author)
+    notes.includes(:author, :award_emoji)
   end
 
   def merge_requests
diff --git a/changelogs/unreleased/sh-fix-award-emoji-nplus-one-participants.yml b/changelogs/unreleased/sh-fix-award-emoji-nplus-one-participants.yml
new file mode 100644
index 00000000000..aee26f9824a
--- /dev/null
+++ b/changelogs/unreleased/sh-fix-award-emoji-nplus-one-participants.yml
@@ -0,0 +1,5 @@
+---
+title: Fix N+1 queries when loading participants for a commit note
+merge_request:
+author:
+type: performance
diff --git a/spec/models/note_spec.rb b/spec/models/note_spec.rb
index 86962cd8d61..6a6c71e6c82 100644
--- a/spec/models/note_spec.rb
+++ b/spec/models/note_spec.rb
@@ -91,6 +91,23 @@ describe Note do
     it "keeps the commit around" do
       expect(note.project.repository.kept_around?(commit.id)).to be_truthy
     end
+
+    it 'does not generate N+1 queries for participants', :request_store do
+      def retrieve_participants
+        commit.notes_with_associations.map(&:participants).to_a
+      end
+
+      # Project authorization checks are cached, establish a baseline
+      retrieve_participants
+
+      control_count = ActiveRecord::QueryRecorder.new do
+        retrieve_participants
+      end
+
+      create(:note_on_commit, project: note.project, note: 'another note', noteable_id: commit.id)
+
+      expect { retrieve_participants }.not_to exceed_query_limit(control_count)
+    end
   end
 
   describe 'authorization' do