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

@@ -11,7 +11,6 @@ def initialize(project:, current_user: nil, params: {})
           @builds = params[:builds] || []
           @dast_site_profiles_builds = []
           @dast_scanner_profiles_builds = []
-          @errors = []
         end
 
         def execute
@@ -20,6 +19,17 @@ def execute
           dast_site_profiles = find_dast_site_profiles(project.id)
           dast_scanner_profiles = find_dast_scanner_profiles(project.id)
 
+          prepare_builds(dast_scanner_profiles, dast_site_profiles)
+
+          return ServiceResponse.error(message: errors) unless errors.empty?
+
+          insert_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)
 
@@ -33,14 +43,11 @@ def execute
               @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
           end
-
-          return ServiceResponse.error(message: @errors) unless @errors.empty?
-
-          insert_builds
-          ServiceResponse.success(payload: @builds)
         end
 
-        private
+        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]}
@@ -64,7 +71,7 @@ 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 })
+            errors.push(_('DAST profile not found: %{name}') % { name: name })
             false
           end
         end