SONARCLOUD-160 fix SQL hotspot to get distrib of private projects

4 files changed, 31 insertions, 48 deletions

diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/component/ComponentDao.java b/server/sonar-db-dao/src/main/java/org/sonar/db/component/ComponentDao.java
index 2fa1094b1c3..8bd51ce589d 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/component/ComponentDao.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/component/ComponentDao.java
@@ -45,7 +45,6 @@ import static com.google.common.base.Preconditions.checkArgument;
 import static java.util.Collections.emptyList;
 import static java.util.Objects.requireNonNull;
 import static org.apache.commons.lang.StringUtils.isBlank;
-import static org.sonar.api.measures.CoreMetrics.NCLOC_KEY;
 import static org.sonar.core.util.stream.MoreCollectors.toList;
 import static org.sonar.core.util.stream.MoreCollectors.toSet;
 import static org.sonar.db.DaoUtils.buildLikeValue;
@@ -245,7 +244,7 @@ public class ComponentDao implements Dao {
   /**
    * Select the children or the leaves of a base component, given by its UUID. The components that are not present in last
    * analysis are ignored.
-   *
+   * <p>
    * An empty list is returned if the base component does not exist or if the base component is a leaf.
    */
   public List<ComponentDto> selectDescendants(DbSession dbSession, ComponentTreeQuery query) {
@@ -297,9 +296,9 @@ public class ComponentDao implements Dao {
   /**
    * Returns all projects (Scope {@link Scopes#PROJECT} and qualifier
    * {@link Qualifiers#PROJECT}) which are enabled.
-   *
+   * <p>
    * Branches are not returned.
-   *
+   * <p>
    * Used by Views.
    */
   public List<ComponentDto> selectProjects(DbSession session) {
@@ -308,7 +307,7 @@ public class ComponentDao implements Dao {
 
   /**
    * Select all projects for a given organization.
-   *
+   * <p>
    * Branches are not returned
    */
   public List<ComponentDto> selectProjectsByOrganization(DbSession dbSession, String organizationUuid) {
@@ -325,9 +324,10 @@ public class ComponentDao implements Dao {
 
   /**
    * Selects all components that are relevant for indexing. The result is not returned (since it is usually too big), but handed over to the <code>handler</code>
-   * @param session the database session
+   *
+   * @param session     the database session
    * @param projectUuid the project uuid, which is selected with all of its children
-   * @param handler the action to be applied to every result
+   * @param handler     the action to be applied to every result
    */
   public void scrollForIndexing(DbSession session, @Nullable String projectUuid, ResultHandler<ComponentDto> handler) {
     mapper(session).scrollForIndexing(projectUuid, handler);
@@ -335,7 +335,7 @@ public class ComponentDao implements Dao {
 
   /**
    * Retrieves all components with a specific root project Uuid, no other filtering is done by this method.
-   *
+   * <p>
    * Used by Views plugin
    */
   public List<ComponentDto> selectByProjectUuid(String projectUuid, DbSession dbSession) {
@@ -344,7 +344,7 @@ public class ComponentDao implements Dao {
 
   /**
    * Retrieve enabled components keys with given qualifiers
-   *
+   * <p>
    * Used by Views plugin
    */
   public Set<ComponentDto> selectComponentsByQualifiers(DbSession dbSession, Set<String> qualifiers) {
@@ -420,7 +420,7 @@ public class ComponentDao implements Dao {
   }
 
   public List<ProjectNclocDistributionDto> selectPrivateProjectsWithNcloc(DbSession dbSession, String organizationUuid) {
-    return mapper(dbSession).selectPrivateProjectsWithNcloc(NCLOC_KEY, organizationUuid, KeyType.BRANCH, BranchType.LONG);
+    return mapper(dbSession).selectPrivateProjectsWithNcloc(organizationUuid);
   }
 
 }
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/component/ComponentMapper.java b/server/sonar-db-dao/src/main/java/org/sonar/db/component/ComponentMapper.java
index b810da61207..63b083191ba 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/component/ComponentMapper.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/component/ComponentMapper.java
@@ -163,9 +163,5 @@ public interface ComponentMapper {
 
   List<KeyWithUuidDto> selectComponentKeysHavingIssuesToMerge(@Param("mergeBranchUuid") String mergeBranchUuid);
 
-  List<ProjectNclocDistributionDto> selectPrivateProjectsWithNcloc(
-    @Param("ncloc") String ncloc,
-    @Param("organizationUuid") String organizationUuid,
-    @Param("branch") KeyType branchOrPullRequest,
-    @Param("branchType") BranchType branchType);
+  List<ProjectNclocDistributionDto> selectPrivateProjectsWithNcloc(@Param("organizationUuid") String organizationUuid);
 }
diff --git a/server/sonar-db-dao/src/main/resources/org/sonar/db/component/ComponentMapper.xml b/server/sonar-db-dao/src/main/resources/org/sonar/db/component/ComponentMapper.xml
index 3a08df2c23d..50c9bff261e 100644
--- a/server/sonar-db-dao/src/main/resources/org/sonar/db/component/ComponentMapper.xml
+++ b/server/sonar-db-dao/src/main/resources/org/sonar/db/component/ComponentMapper.xml
@@ -826,35 +826,22 @@
   </select>
 
   <select id="selectPrivateProjectsWithNcloc" resultType="org.sonar.db.component.ProjectNclocDistributionDto">
-    SELECT
-      p.kee AS kee,
-      p.name AS name,
-      t.ncloc AS ncloc
-    FROM
-      projects p
-      INNER JOIN (
-        SELECT
-          b.project_uuid AS projectUuid,
-          max(lm.value) AS ncloc
-        FROM
-          live_measures lm
-        INNER JOIN metrics m ON m.id = lm.metric_id
-        INNER JOIN project_branches b ON b.uuid = lm.project_uuid
-        WHERE
-          m.name = #{ncloc, jdbcType=VARCHAR}
-          AND b.branch_type = #{branchType, jdbcType=VARCHAR}
-          AND b.key_type = #{branch, jdbcType=VARCHAR}
-        GROUP BY
-          b.project_uuid) t ON t.projectUuid = p.uuid
-      WHERE
-        p.enabled = ${_true}
-        AND p.private = ${_true}
-        AND p.scope = 'PRJ'
-        AND p.qualifier = 'TRK'
-        AND p.copy_component_uuid IS NULL
-        AND p.organization_uuid = #{organizationUuid, jdbcType=VARCHAR}
-      ORDER BY
-        ncloc DESC
+    select p.kee as kee, p.name as name, max(lm.value) as ncloc
+    from live_measures lm
+    inner join metrics m on m.id = lm.metric_id
+    inner join project_branches b on b.uuid = lm.component_uuid
+    inner join projects p on b.project_uuid = p.uuid
+    where
+      m.name = 'ncloc'
+      and b.key_type = 'BRANCH'
+      and b.branch_type = 'LONG'
+      and p.enabled = ${_true}
+      and p.private = ${_true}
+      and p.scope = 'PRJ'
+      and p.qualifier = 'TRK'
+      and p.copy_component_uuid is null
+      and p.organization_uuid = #{organizationUuid, jdbcType=VARCHAR}
+    group by p.kee, p.name
+    order by ncloc desc
   </select>
-
 </mapper>
diff --git a/server/sonar-db-dao/src/test/java/org/sonar/db/component/ComponentDaoTest.java b/server/sonar-db-dao/src/test/java/org/sonar/db/component/ComponentDaoTest.java
index f69460bec7b..2ae2b30ba45 100644
--- a/server/sonar-db-dao/src/test/java/org/sonar/db/component/ComponentDaoTest.java
+++ b/server/sonar-db-dao/src/test/java/org/sonar/db/component/ComponentDaoTest.java
@@ -2022,7 +2022,7 @@ public class ComponentDaoTest {
   }
 
   @Test
-  public void selectPrivateProjectsWithNcloc() throws Exception {
+  public void selectPrivateProjectsWithNcloc() {
     MetricDto metric = db.measures().insertMetric(m -> m.setKey("ncloc"));
     OrganizationDto organizationDto = db.organizations().insert();
 
@@ -2037,10 +2037,10 @@ public class ComponentDaoTest {
     insertMeasure(10d, db.components().insertMainBranch(organizationDto, b -> b.setName("bar")), metric);
 
     // public project - not returned
-    insertMeasure(10d, db.components().insertMainBranch(organizationDto, b -> b.setPrivate(false)), metric);
+    insertMeasure(11d, db.components().insertMainBranch(organizationDto, b -> b.setPrivate(false)), metric);
 
     // different org - not returned
-    insertMeasure(10d, db.components().insertMainBranch(db.organizations().insert()), metric);
+    insertMeasure(12d, db.components().insertMainBranch(db.organizations().insert()), metric);
 
     List<ProjectNclocDistributionDto> result = underTest.selectPrivateProjectsWithNcloc(db.getSession(), organizationDto.getUuid());