Skip to content

Fix n plus one issue ci build dast profile

Marcos Rocha requested to merge mc_rocha-fix-n-plus-one-issue-ci-build-dast-profile-346325 into master

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.

Edited by Marcos Rocha

Merge request reports