Fix n plus one issue ci build dast profile

What does this MR do and why?

As described in #346325 (closed), there are two N+1 issues when multiple builds with the dast_configuration keyword are present in the YAML file.

Example:

stages:
  - dast
dast:
  stage: dast
  dast_configuration:
    site_profile: dast_site_profile_name
    scanner_profile: dast_scanner_profile_name
  script:
    - exit 0
dast2:
  stage: dast
  dast_configuration:
    site_profile: another_dast_site_profile_name
    scanner_profile: another_dast_scanner_profile_name
  script:
    - exit 0

In order to fix these N+1 issues this Merge Request:

• Creates a service that takes several builds and profiles and associates them inside a single database insert.
• Extends the profiles finder to batch load profiles.

Before

SELECT "dast_site_profiles".* FROM "dast_site_profiles" WHERE "dast_site_profiles"."project_id" = 2 AND "dast_site_profiles"."name" = 'Portable Receiver 172ca0e6 - 1' ORDER BY "dast_site_profiles"."id" ASC LIMIT 1 /*application:test,correlation_id:1d5d62b143cc80539f1417a2e0b7c529,db_config_name:main*/

SELECT "dast_sites".* FROM "dast_sites" WHERE "dast_sites"."id" = 3 /*application:test,correlation_id:1d5d62b143cc80539f1417a2e0b7c529,db_config_name:main*/

SELECT "dast_scanner_profiles".* FROM "dast_scanner_profiles" WHERE "dast_scanner_profiles"."project_id" = 2 AND "dast_scanner_profiles"."name" = 'GPS Bridge 7848fd8e - 1' ORDER BY "dast_scanner_profiles"."id" ASC LIMIT 1 /*application:test,correlation_id:1d5d62b143cc80539f1417a2e0b7c529,db_config_name:main*/

INSERT INTO "dast_site_profiles_builds" ("dast_site_profile_id", "ci_build_id") VALUES (3, 7) /*application:test,correlation_id:1d5d62b143cc80539f1417a2e0b7c529,db_config_name:main*/

INSERT INTO "dast_scanner_profiles_builds" ("dast_scanner_profile_id", "ci_build_id") VALUES (3, 7) /*application:test,correlation_id:1d5d62b143cc80539f1417a2e0b7c529,db_config_name:main*/


SELECT "dast_site_profiles".* FROM "dast_site_profiles" WHERE "dast_site_profiles"."project_id" = 2 AND "dast_site_profiles"."name" = 'Power Bridge a5ed6674 - 2' ORDER BY "dast_site_profiles"."id" ASC LIMIT 1 /*application:test,correlation_id:1d5d62b143cc80539f1417a2e0b7c529,db_config_name:main*/

SELECT "dast_sites".* FROM "dast_sites" WHERE "dast_sites"."id" = 4 /*application:test,correlation_id:1d5d62b143cc80539f1417a2e0b7c529,db_config_name:main*/

SELECT "dast_scanner_profiles".* FROM "dast_scanner_profiles" WHERE "dast_scanner_profiles"."project_id" = 2 AND "dast_scanner_profiles"."name" = 'Video System f5efa6ec - 2' ORDER BY "dast_scanner_profiles"."id" ASC LIMIT 1 /*application:test,correlation_id:1d5d62b143cc80539f1417a2e0b7c529,db_config_name:main*/

INSERT INTO "dast_site_profiles_builds" ("dast_site_profile_id", "ci_build_id") VALUES (4, 8) /*application:test,correlation_id:1d5d62b143cc80539f1417a2e0b7c529,db_config_name:main*/

INSERT INTO "dast_scanner_profiles_builds" ("dast_scanner_profile_id", "ci_build_id") VALUES (4, 8) /*application:test,correlation_id:1d5d62b143cc80539f1417a2e0b7c529,db_config_name:main*/

After

SELECT "dast_site_profiles".* FROM "dast_site_profiles" WHERE "dast_site_profiles"."project_id" = 1 AND "dast_site_profiles"."name" IN ('Power Bridge c1085309 - 1', 'Electric GPS Compressor f3adb06f - 2') 

SELECT "dast_sites".* FROM "dast_sites" WHERE "dast_sites"."id" IN (2, 1) 

SELECT "dast_scanner_profiles".* FROM "dast_scanner_profiles" WHERE "dast_scanner_profiles"."name" IN ('Auto Case 657d053e - 1', 'Digital Transmitter 3c2c49e0 - 2') 

INSERT INTO "dast_site_profiles_builds" ("ci_build_id","dast_site_profile_id") VALUES (3, 1), (4, 2) ON CONFLICT ("ci_build_id") DO NOTHING RETURNING "dast_site_profile_id","ci_build_id" 

INSERT INTO "dast_scanner_profiles_builds" ("ci_build_id","dast_scanner_profile_id") VALUES (3, 1), (4, 2) ON CONFLICT ("ci_build_id") DO NOTHING RETURNING "dast_scanner_profile_id","ci_build_id" 

Screenshots or screen recordings

These are strongly recommended to assist reviewers and reduce the time to merge your change.

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

• I have evaluated the MR acceptance checklist for this MR.

ee/app/services/app_sec/dast/profiles/create_associations_service.rb

@@ -6,78 +6,84 @@ module Profiles
       class CreateAssociationsService < BaseProjectService
         include ::Gitlab::Ci::Pipeline::Chain::Helpers
 
-        def initialize(project:, current_user: nil, params: {})
-          super(project: project, current_user: current_user, params: params)
-          @builds = params[:builds] || []
-          @dast_site_profiles_builds = []
-          @dast_scanner_profiles_builds = []
-        end
-
         def execute
           return ServiceResponse.error(message: _('Insufficient permissions for dast_configuration keyword')) unless allowed?
 
-          dast_site_profiles = find_dast_site_profiles(project.id)
-          dast_scanner_profiles = find_dast_scanner_profiles(project.id)
+          dast_site_profiles = find_dast_site_profiles
+          dast_scanner_profiles = find_dast_scanner_profiles
 
-          prepare_builds(dast_scanner_profiles, dast_site_profiles)
+          dast_site_profiles_builds, dast_scanner_profiles_builds = prepare_batch_inserts(dast_scanner_profiles, dast_site_profiles)
 
           return ServiceResponse.error(message: errors) unless errors.empty?
 
-          insert_builds
+          insert_builds(dast_site_profiles_builds, dast_scanner_profiles_builds)
+
           ServiceResponse.success(payload: @builds)
         end
 
         private
 
-        def prepare_builds(dast_scanner_profiles, dast_site_profiles)
-          @builds.each do |build|
-            next unless build.is_a?(::Ci::Build)
-
-            if build.options.dig(:dast_configuration, :site_profile).present?
-              dast_site_profile = dast_site_profiles.find { |dsp| dsp.name == build.options[:dast_configuration][:site_profile] }
-              @dast_site_profiles_builds.append({ ci_build_id: build.id, dast_site_profile_id: dast_site_profile.id }) if has_permission?(dast_site_profile, build.options[:dast_configuration][:site_profile])
-            end
+        def allowed?
+          can?(current_user, :create_on_demand_dast_scan, project)
+        end
 
-            if build.options.dig(:dast_configuration, :scanner_profile).present?
-              dast_scanner_profile = dast_scanner_profiles.find { |dsp| dsp.name == build.options[:dast_configuration][:scanner_profile] }
-              @dast_scanner_profiles_builds.append({ ci_build_id: build.id, dast_scanner_profile_id: dast_scanner_profile.id }) if has_permission?(dast_scanner_profile, build.options[:dast_configuration][:scanner_profile])
-            end
+        def has_permission?(profile, name)
+          if can?(current_user, :read_on_demand_dast_scan, profile)
+            true
+          else
+            errors.push(_('DAST profile not found: %{name}') % { name: name })
+            false
           end
         end
 
+        def builds
+          @builds ||= params[:builds] || []
+        end
+
         def errors
           @errors ||= []
         end
 
-        def find_dast_site_profiles(project_id)
-          dast_site_profiles_names = @builds.collect {|build| build.options[:dast_configuration][:site_profile] if build.options[:dast_configuration]}
-          DastSiteProfilesFinder.new(project_id: project_id, names: dast_site_profiles_names).execute
-        end
+        def prepare_batch_inserts(dast_scanner_profiles, dast_site_profiles)
+          dast_site_profiles_builds = []
+          dast_scanner_profiles_builds = []
 
-        def find_dast_scanner_profiles(project_id)
-          dast_scanner_profiles_names = @builds.collect {|build| build.options[:dast_configuration][:scanner_profile] if build.options[:dast_configuration]}
-          DastScannerProfilesFinder.new(project_id: project_id, names: dast_scanner_profiles_names).execute
+          builds.each do |build|
+            next unless build.is_a?(::Ci::Build)
+
+            if (site_profile_name = build.options.dig(:dast_configuration, :site_profile))
+              dast_site_profile = dast_site_profiles.find { |dsp| dsp.name == site_profile_name }
+
+              dast_site_profiles_builds.append({ ci_build_id: build.id, dast_site_profile_id: dast_site_profile.id }) if has_permission?(dast_site_profile, site_profile_name)
+            end
+
+            if (scanner_profile_name = build.options.dig(:dast_configuration, :scanner_profile))
+              dast_scanner_profile = dast_scanner_profiles.find { |dsp| dsp.name == scanner_profile_name }
+
+              dast_scanner_profiles_builds.append({ ci_build_id: build.id, dast_scanner_profile_id: dast_scanner_profile.id }) if has_permission?(dast_scanner_profile, scanner_profile_name)
+            end
+          end
+
+          [dast_site_profiles_builds, dast_scanner_profiles_builds]
         end
 
-        def insert_builds
-          return unless @dast_site_profiles_builds.present? && @dast_scanner_profiles_builds.present?
+        def find(key, with:)
+          names = builds.map { |build| build.options.dig(:dast_configuration, key) }.compact
 
-          ::Dast::SiteProfilesBuild.insert_all(@dast_site_profiles_builds, unique_by: 'ci_build_id')
+          with.new(project_id: project.id, names: names).execute
+        end
 
-          ::Dast::ScannerProfilesBuild.insert_all(@dast_scanner_profiles_builds, unique_by: 'ci_build_id')
+        def find_dast_site_profiles
+          find(:site_profile, with: DastSiteProfilesFinder)
         end
 
-        def has_permission?(profile, name)
-          if can?(current_user, :read_on_demand_dast_scan, profile)
-            true
-          else
-            errors.push(_('DAST profile not found: %{name}') % { name: name })
-            false
-          end
+        def find_dast_scanner_profiles
+          find(:scanner_profile, with: DastScannerProfilesFinder)
         end
 
-        def allowed?
-          can?(current_user, :create_on_demand_dast_scan, project)
+        def insert_builds(dast_site_profiles_builds, dast_scanner_profiles_builds)
+          ::Dast::SiteProfilesBuild.insert_all(dast_site_profiles_builds, unique_by: 'ci_build_id') unless dast_site_profiles_builds.empty?
+          ::Dast::ScannerProfilesBuild.insert_all(dast_scanner_profiles_builds, unique_by: 'ci_build_id') unless dast_scanner_profiles_builds.empty?
         end
       end
     end