From e8522a8bed4da2f7293b67be005318d1a351d7b4 Mon Sep 17 00:00:00 2001
From: Vasilii Iakliushin <viakliushin@gitlab.com>
Date: Thu, 7 Oct 2021 18:36:51 +0200
Subject: [PATCH] Optimize merge request query for large collections

`WHERE ... IN (...` query is not performant for large collections. A
faster alternative is a `INNER JOIN` over a `VALUES` clause.

Changelog: performance
---
 .../merge_request_reference_filter.rb         |  6 +++++-
 .../merge_request_reference_filter_spec.rb    | 20 +++++++++++++++++++
 2 files changed, 25 insertions(+), 1 deletion(-)

diff --git a/lib/banzai/filter/references/merge_request_reference_filter.rb b/lib/banzai/filter/references/merge_request_reference_filter.rb
index 6c5ad83d9ae810..5f505beb0ca630 100644
--- a/lib/banzai/filter/references/merge_request_reference_filter.rb
+++ b/lib/banzai/filter/references/merge_request_reference_filter.rb
@@ -48,8 +48,12 @@ def object_link_text_extras(object, matches)
         end
 
         def parent_records(parent, ids)
+          return MergeRequest.none if ids.empty?
+
+          mr_id_list = ids.uniq.map { |id| "(#{Integer(id)})" }.join(',')
+
           parent.merge_requests
-            .where(iid: ids.to_a)
+            .joins("INNER JOIN (VALUES #{mr_id_list}) mr_ids (id) ON (mr_ids.id = #{MergeRequest.table_name}.iid)")
             .includes(target_project: :namespace)
         end
 
diff --git a/spec/lib/banzai/filter/references/merge_request_reference_filter_spec.rb b/spec/lib/banzai/filter/references/merge_request_reference_filter_spec.rb
index ee2ce967a47e9d..cd2e7d381eaeb8 100644
--- a/spec/lib/banzai/filter/references/merge_request_reference_filter_spec.rb
+++ b/spec/lib/banzai/filter/references/merge_request_reference_filter_spec.rb
@@ -297,4 +297,24 @@
       expect(result.css('a').first.attr('href')).to eq(urls.project_merge_request_url(project, merge))
     end
   end
+
+  describe '#parent_records' do
+    subject { filter_instance.parent_records(project, ids) }
+
+    let(:ids) { [merge.iid, another_merge.iid, unrelated_merge.iid, non_existing_record_id] }
+    let(:another_merge) { create(:merge_request, source_project: project, source_branch: 'another') }
+    let(:unrelated_merge) { create(:merge_request) }
+    let(:input_text) { '' }
+    let(:current_user) { create(:user) }
+
+    it 'returns only merge requests for the project' do
+      is_expected.to match_array([merge, another_merge])
+    end
+
+    context 'without merge request references' do
+      let(:ids) { [] }
+
+      it { is_expected.to be_empty }
+    end
+  end
 end
-- 
GitLab