diff options
| author | Thomas de Grenier de Latour <thomas.degrenierdelatour@orange.com> | 2023-03-02 00:49:03 +0100 |
|---|---|---|
| committer | sonartech <sonartech@sonarsource.com> | 2023-03-09 20:02:59 +0000 |
| commit | ac52dfa0e238a35355a885155c1188af06fdd83e (patch) | |
| tree | e71f020be98bacfdf479c891cf2095246c516746 | |
| parent | 273f7412c5a26b9c18abed66c7ffc154a94e9746 (diff) | |
| download | sonarqube-ac52dfa0e238a35355a885155c1188af06fdd83e.tar.gz sonarqube-ac52dfa0e238a35355a885155c1188af06fdd83e.zip | |
[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.
| -rw-r--r-- | server/sonar-db-dao/src/main/resources/org/sonar/db/project/ProjectMapper.xml | 5 |
1 files 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 @@ -118,11 +118,12 @@ 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 <foreach collection="languageFilters" index="index" item="languageFilter" open="(" separator=" or " close=")"> lm.text_value like #{languageFilter, jdbcType=VARCHAR} escape '/' |