From ac52dfa0e238a35355a885155c1188af06fdd83e Mon Sep 17 00:00:00 2001 From: Thomas de Grenier de Latour Date: Thu, 2 Mar 2023 00:49:03 +0100 Subject: [PATCH] [NO-JIRA] optimize slow selectProjectUuidsAssociatedToDefaultQualityProfileByLanguage The project DAO `selectProjectUuidsAssociatedToDefaultQualityProfileByLanguage` SQL query implies scanning through many rows of the `live_measures` table . This takes several minutes to execute in some setups (Postgres 13, 200M rows in the table), making SonarQube upgrades painfully slow (this request is executed for each update of a bundled quality profile). A similar query can be achieved by joining the `projects` table, with a drastic improvement to the execution plan. The results are not the same though: this new version only returns uuids of actual projects, whereas the original one also returns uuids of other components (not existing in the projects table). But this is actually an improvement too: the only call sites for this DAO method (in `QualityProfileChangeEventServiceImpl`) uses the results as an input for the `ProjectDAO.selectByUuids()` method, which only looks at the `projects` table. --- .../main/resources/org/sonar/db/project/ProjectMapper.xml | 5 +++-- 1 file changed, 3 insertions(+), 2 deletions(-) diff --git a/server/sonar-db-dao/src/main/resources/org/sonar/db/project/ProjectMapper.xml b/server/sonar-db-dao/src/main/resources/org/sonar/db/project/ProjectMapper.xml index 6e295c489cd..9f32c5a8520 100644 --- a/server/sonar-db-dao/src/main/resources/org/sonar/db/project/ProjectMapper.xml +++ b/server/sonar-db-dao/src/main/resources/org/sonar/db/project/ProjectMapper.xml @@ -117,12 +117,13 @@ lm.project_uuid from live_measures lm + inner join + projects p on (p.uuid = lm.project_uuid and p.uuid = lm.component_uuid) inner join metrics m on m.uuid = lm.metric_uuid where m.name = 'ncloc_language_distribution' - and lm.component_uuid = lm.project_uuid - and lm.project_uuid not in (select project_uuid from project_qprofiles) + and p.uuid not in (select project_uuid from project_qprofiles) and lm.text_value like #{languageFilter, jdbcType=VARCHAR} escape '/' -- 2.39.5