Skip to content

Fix N+1 queries to find or initialize services

Arturo Herrero requested to merge 326209-fix-find-or-initialize-service-n+1 into master

What does this MR do?

This fixes the N+1 queries to find or initialize services.

Find or initialize the services has the following precedence:

  • Find records.
  • Build services them from instance-level record or service template record.
  • Build services from scratch.

The problem this fixes when building the services from scratch, Rails method build_(association) performs a query for each service that we have to initialize.

License Load (0.4ms)  SELECT "licenses".* FROM "licenses" ORDER BY "licenses"."id" DESC LIMIT 100 /*application:test*/
↳ ee/app/models/license.rb:298:in `load_license'
Service Load (3.2ms)  SELECT "services".* FROM "services" WHERE "services"."project_id" = 1 /*application:test*/
↳ app/models/project.rb:2584:in `find_service'
Service Load (0.8ms)  SELECT "services".* FROM "services" WHERE "services"."instance" = TRUE AND "services"."type" IN ('AsanaService', 'AssemblaService', 'BambooService', 'BugzillaService', 'BuildkiteService', 'CampfireService', 'ConfluenceService', 'CustomIssueTrackerService', 'DatadogService', 'DiscordService', 'DroneCiService', 'EmailsOnPushService', 'EwmService', 'ExternalWikiService', 'FlowdockService', 'HangoutsChatService', 'IrkerService', 'JiraService', 'MattermostService', 'MattermostSlashCommandsService', 'MicrosoftTeamsService', 'PackagistService', 'PipelinesEmailService', 'PivotaltrackerService', 'PrometheusService', 'PushoverService', 'RedmineService', 'SlackService', 'SlackSlashCommandsService', 'TeamcityService', 'UnifyCircuitService', 'WebexTeamsService', 'YoutrackService') /*application:test*/
↳ app/models/project.rb:2584:in `find_service'
Service Load (0.4ms)  SELECT "services".* FROM "services" WHERE "services"."template" = TRUE AND "services"."type" IN ('AsanaService', 'AssemblaService', 'BambooService', 'BugzillaService', 'BuildkiteService', 'CampfireService', 'ConfluenceService', 'CustomIssueTrackerService', 'DatadogService', 'DiscordService', 'DroneCiService', 'EmailsOnPushService', 'EwmService', 'ExternalWikiService', 'FlowdockService', 'HangoutsChatService', 'IrkerService', 'JiraService', 'MattermostService', 'MattermostSlashCommandsService', 'MicrosoftTeamsService', 'PackagistService', 'PipelinesEmailService', 'PivotaltrackerService', 'PrometheusService', 'PushoverService', 'RedmineService', 'SlackService', 'SlackSlashCommandsService', 'TeamcityService', 'UnifyCircuitService', 'WebexTeamsService', 'YoutrackService') /*application:test*/
↳ app/models/project.rb:2584:in `find_service'
AsanaService Load (0.4ms)  SELECT "services".* FROM "services" WHERE "services"."type" = 'AsanaService' AND "services"."project_id" = 1 LIMIT 1 /*application:test*/
↳ app/models/project.rb:1381:in `public_send'
AssemblaService Load (0.4ms)  SELECT "services".* FROM "services" WHERE "services"."type" = 'AssemblaService' AND "services"."project_id" = 1 LIMIT 1 /*application:test*/
↳ app/models/project.rb:1381:in `public_send'
BambooService Load (0.3ms)  SELECT "services".* FROM "services" WHERE "services"."type" = 'BambooService' AND "services"."project_id" = 1 LIMIT 1 /*application:test*/
↳ app/models/project.rb:1381:in `public_send'
BugzillaService Load (0.4ms)  SELECT "services".* FROM "services" WHERE "services"."type" = 'BugzillaService' AND "services"."project_id" = 1 LIMIT 1 /*application:test*/
↳ app/models/project.rb:1381:in `public_send'
BuildkiteService Load (0.4ms)  SELECT "services".* FROM "services" WHERE "services"."type" = 'BuildkiteService' AND "services"."project_id" = 1 LIMIT 1 /*application:test*/
↳ app/models/project.rb:1381:in `public_send'
CampfireService Load (0.3ms)  SELECT "services".* FROM "services" WHERE "services"."type" = 'CampfireService' AND "services"."project_id" = 1 LIMIT 1 /*application:test*/
...

So for each service that we build we were doing one query. Now using NameService.new we reduce the query count from 38 to 4.

Related to #326209 (closed)

Merge request reports