From 0fb39e83de58a422ad4ae6a0fe25b4163344d5a7 Mon Sep 17 00:00:00 2001
From: Jacek Poreda <jacek.poreda@sonarsource.com>
Date: Mon, 20 Feb 2023 12:05:32 +0100
Subject: [PATCH] [NO-JIRA] Optimize query for 'ncloc' metric for largest
 branch per project

---
 .../org/sonar/db/measure/LiveMeasureDao.java  |  4 +-
 .../sonar/db/measure/LiveMeasureMapper.java   |  2 +-
 .../sonar/db/measure/LiveMeasureMapper.xml    | 52 ++++++++-----------
 .../sonar/db/measure/LiveMeasureDaoTest.java  |  5 +-
 4 files changed, 29 insertions(+), 34 deletions(-)

diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/measure/LiveMeasureDao.java b/server/sonar-db-dao/src/main/java/org/sonar/db/measure/LiveMeasureDao.java
index 17441f11236..9f04ff22b5e 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/measure/LiveMeasureDao.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/measure/LiveMeasureDao.java
@@ -105,8 +105,8 @@ public class LiveMeasureDao implements Dao {
     return ncloc == null ? 0L : ncloc;
   }
 
-  public List<LargestBranchNclocDto> getLargestBranchNclocPerProject(DbSession dbSession) {
-    return mapper(dbSession).getLargestBranchNclocPerProject();
+  public List<LargestBranchNclocDto> getLargestBranchNclocPerProject(DbSession dbSession, String nclocMetricUuid) {
+    return mapper(dbSession).getLargestBranchNclocPerProject(nclocMetricUuid);
   }
 
   public List<ProjectLocDistributionDto> selectLargestBranchesLocDistribution(DbSession session, String nclocUuid, String nclocDistributionUuid) {
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/measure/LiveMeasureMapper.java b/server/sonar-db-dao/src/main/java/org/sonar/db/measure/LiveMeasureMapper.java
index 5891b136fd7..e93f7742a24 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/measure/LiveMeasureMapper.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/measure/LiveMeasureMapper.java
@@ -62,7 +62,7 @@ public interface LiveMeasureMapper {
     @Param("private") Boolean privateProject,
     @Nullable @Param("projectUuidToExclude") String projectUuidToExclude);
 
-  List<LargestBranchNclocDto> getLargestBranchNclocPerProject();
+  List<LargestBranchNclocDto> getLargestBranchNclocPerProject(@Param("nclocUuid") String nclocUuid);
 
   List<ProjectLocDistributionDto> selectLargestBranchesLocDistribution(@Param("nclocUuid") String nclocUuid, @Param("nclocDistributionUuid") String nclocDistributionUuid);
 
diff --git a/server/sonar-db-dao/src/main/resources/org/sonar/db/measure/LiveMeasureMapper.xml b/server/sonar-db-dao/src/main/resources/org/sonar/db/measure/LiveMeasureMapper.xml
index 23d5ba24e36..c4d49599fc1 100644
--- a/server/sonar-db-dao/src/main/resources/org/sonar/db/measure/LiveMeasureMapper.xml
+++ b/server/sonar-db-dao/src/main/resources/org/sonar/db/measure/LiveMeasureMapper.xml
@@ -82,34 +82,28 @@
   </select>
 
   <select id="getLargestBranchNclocPerProject" parameterType="map" resultType="LargestBranchNclocDto">
-    select tie_breaker.projectUuid,
-    tie_breaker.projectName,
-    tie_breaker.projectKey,
-    ncloc as loc,
-    pb.kee as branchName,
-    pb.branch_type as branchType
-      from
-        (select counter.projectUuid as projectUuid,
-          counter.maxncloc ncloc,
-          min(br.uuid) as branchUuid,
-          counter.projectName,
-          counter.projectKey
-          from
-            (select b.project_uuid as projectUuid,
-              p.name as projectName,
-              p.kee as projectKey,
-              max(lm.value) as maxncloc
-                from live_measures lm
-                inner join metrics m on m.uuid = lm.metric_uuid
-                inner join project_branches b on b.uuid = lm.component_uuid
-                inner join projects p on p.uuid = b.project_uuid and p.qualifier = 'TRK'
-                where m.name = 'ncloc'
-                group by b.project_uuid, p.name, p.kee) counter
-          inner join live_measures lmo on lmo.value = counter.maxncloc
-          inner join project_branches br on br.project_uuid = counter.projectUuid and br.uuid = lmo.component_uuid
-          group by counter.projectUuid, counter.maxncloc, counter.projectName, counter.projectKey) tie_breaker
-      inner join project_branches pb on tie_breaker.branchUuid = pb.uuid
-      order by ncloc desc
+    select loc_grouped_branches.projectUuid,
+        loc_grouped_branches.projectName,
+        loc_grouped_branches.projectKey,
+        loc_grouped_branches.ncloc as loc,
+        loc_grouped_branches.branchName,
+        loc_grouped_branches.branchType
+    from (
+       select pb.project_uuid as projectUuid,
+       p.name as projectName,
+       p.kee as projectKey,
+       pb.kee as branchName,
+       pb.branch_type as branchType,
+       lm.value as ncloc,
+       row_number() over (partition by pb.project_uuid order by lm.value desc, pb.uuid asc) row_number
+       from live_measures lm
+          inner join project_branches pb on pb.uuid = lm.component_uuid
+          inner join projects p on p.uuid = pb.project_uuid
+          where lm.metric_uuid = #{nclocUuid, jdbcType=VARCHAR}
+          and p.qualifier ='TRK'
+      ) loc_grouped_branches
+    where loc_grouped_branches.row_number = 1
+    order by ncloc desc
   </select>
 
   <select id="selectLargestBranchesLocDistribution" parameterType="map" resultType="ProjectLocDistribution">
@@ -126,7 +120,7 @@
           where lm.metric_uuid = #{nclocUuid, jdbcType=VARCHAR}
           and p.qualifier ='TRK'
       ) loc_grouped_branches
-    WHERE loc_grouped_branches.row_number = 1) as top_branches
+    WHERE loc_grouped_branches.row_number = 1) top_branches
     inner join live_measures lm2 on lm2.component_uuid = top_branches.uuid
     where lm2.metric_uuid = #{nclocDistributionUuid, jdbcType=VARCHAR}
   </select>
diff --git a/server/sonar-db-dao/src/test/java/org/sonar/db/measure/LiveMeasureDaoTest.java b/server/sonar-db-dao/src/test/java/org/sonar/db/measure/LiveMeasureDaoTest.java
index 13cf0c87aff..89e34ffd984 100644
--- a/server/sonar-db-dao/src/test/java/org/sonar/db/measure/LiveMeasureDaoTest.java
+++ b/server/sonar-db-dao/src/test/java/org/sonar/db/measure/LiveMeasureDaoTest.java
@@ -376,9 +376,10 @@ public class LiveMeasureDaoTest {
   @Test
   public void get_branch_with_max_ncloc_per_project() {
     Map<String, MetricDto> metrics = setupMetrics();
-    setupProjectsWithLoc(metrics.get("ncloc"), metrics.get("ncloc_language_distribution"), metrics.get("lines"));
+    MetricDto ncloc = metrics.get("ncloc");
+    setupProjectsWithLoc(ncloc, metrics.get("ncloc_language_distribution"), metrics.get("lines"));
 
-    List<LargestBranchNclocDto> results = underTest.getLargestBranchNclocPerProject(db.getSession());
+    List<LargestBranchNclocDto> results = underTest.getLargestBranchNclocPerProject(db.getSession(), ncloc.getUuid());
 
     assertThat(results).hasSize(5);
     assertLocForProject(results.get(0), "projectWithTieOnBranchSize", DEFAULT_MAIN_BRANCH_NAME, 250);
-- 
2.39.5