SONAR-4301 Improve performance when searching component children ids from specified component keys

10 files changed, 78 insertions, 33 deletions

diff --git a/sonar-core/src/main/java/org/sonar/core/issue/db/IssueDao.java b/sonar-core/src/main/java/org/sonar/core/issue/db/IssueDao.java
index 92de3dcd68b..0a030d6889c 100644
--- a/sonar-core/src/main/java/org/sonar/core/issue/db/IssueDao.java
+++ b/sonar-core/src/main/java/org/sonar/core/issue/db/IssueDao.java
@@ -100,7 +100,7 @@ public class IssueDao implements BatchComponent, ServerComponent {
 
   private List<IssueDto> selectIssues(IssueQuery query, @Nullable Integer userId, Integer maxResults, SqlSession session){
     IssueMapper mapper = session.getMapper(IssueMapper.class);
-    return mapper.selectIssues(query, userId, query.requiredRole(), maxResults);
+    return mapper.selectIssues(query, query.componentRoots(), userId, query.requiredRole(), maxResults);
   }
 
   @VisibleForTesting
diff --git a/sonar-core/src/main/java/org/sonar/core/issue/db/IssueMapper.java b/sonar-core/src/main/java/org/sonar/core/issue/db/IssueMapper.java
index f5e85754ef8..c37fe4803ca 100644
--- a/sonar-core/src/main/java/org/sonar/core/issue/db/IssueMapper.java
+++ b/sonar-core/src/main/java/org/sonar/core/issue/db/IssueMapper.java
@@ -24,6 +24,7 @@ import org.sonar.api.issue.IssueQuery;
 
 import javax.annotation.Nullable;
 
+import java.util.Collection;
 import java.util.List;
 
 public interface IssueMapper {
@@ -32,8 +33,8 @@ public interface IssueMapper {
 
   List<IssueDto> selectNonClosedIssues(int rootComponentId);
 
-  List<IssueDto> selectIssues(@Param("query") IssueQuery query, @Nullable @Param("userId") Integer userId, @Param("role") String role,
-                                          @Param("maxResults") Integer maxResult);
+  List<IssueDto> selectIssues(@Param("query") IssueQuery query, @Param("componentRootKeys") Collection<String> componentRootKeys,
+                              @Nullable @Param("userId") Integer userId, @Param("role") String role, @Param("maxResults") Integer maxResult);
 
   void insert(IssueDto issue);
 
diff --git a/sonar-core/src/main/java/org/sonar/core/issue/db/IssueStatsDao.java b/sonar-core/src/main/java/org/sonar/core/issue/db/IssueStatsDao.java
index b5cfc3dfdc8..059e8888df9 100644
--- a/sonar-core/src/main/java/org/sonar/core/issue/db/IssueStatsDao.java
+++ b/sonar-core/src/main/java/org/sonar/core/issue/db/IssueStatsDao.java
@@ -44,7 +44,7 @@ public class IssueStatsDao implements ServerComponent {
     SqlSession session = mybatis.openSession();
     try {
       IssueStatsMapper mapper = session.getMapper(IssueStatsMapper.class);
-      return mapper.selectIssuesColumn(query, column, userId, query.requiredRole());
+      return mapper.selectIssuesColumn(query, column, query.componentRoots(), userId, query.requiredRole());
     } finally {
       MyBatis.closeQuietly(session);
     }
diff --git a/sonar-core/src/main/java/org/sonar/core/issue/db/IssueStatsMapper.java b/sonar-core/src/main/java/org/sonar/core/issue/db/IssueStatsMapper.java
index 3f401bf783e..e2e097ec6c9 100644
--- a/sonar-core/src/main/java/org/sonar/core/issue/db/IssueStatsMapper.java
+++ b/sonar-core/src/main/java/org/sonar/core/issue/db/IssueStatsMapper.java
@@ -25,11 +25,12 @@ import org.sonar.api.issue.IssueQuery;
 
 import javax.annotation.Nullable;
 
+import java.util.Collection;
 import java.util.List;
 
 public interface IssueStatsMapper {
 
-  List<Object> selectIssuesColumn(@Param("query") IssueQuery query, @Param("column") String column,
+  List<Object> selectIssuesColumn(@Param("query") IssueQuery query, @Param("column") String column, @Param("componentRootKeys") Collection<String> componentRootKeys,
                                   @Nullable @Param("userId") Integer userId, @Param("role") String role);
 
 }
diff --git a/sonar-core/src/main/java/org/sonar/core/resource/ResourceDao.java b/sonar-core/src/main/java/org/sonar/core/resource/ResourceDao.java
index 661527f987d..4901abda11c 100644
--- a/sonar-core/src/main/java/org/sonar/core/resource/ResourceDao.java
+++ b/sonar-core/src/main/java/org/sonar/core/resource/ResourceDao.java
@@ -154,6 +154,18 @@ public class ResourceDao {
     }
   }
 
+  public List<Integer> findChildrenComponentIds(Collection<String> componentRootKeys){
+    if (componentRootKeys.isEmpty()) {
+      return Collections.emptyList();
+    }
+    SqlSession session = mybatis.openSession();
+    try {
+      return session.getMapper(ResourceMapper.class).selectChildrenComponentIds(componentRootKeys);
+    } finally {
+      MyBatis.closeQuietly(session);
+    }
+  }
+
   @CheckForNull
   public ResourceDto getRootProjectByComponentKey(String componentKey) {
     SqlSession session = mybatis.openSession();
diff --git a/sonar-core/src/main/java/org/sonar/core/resource/ResourceMapper.java b/sonar-core/src/main/java/org/sonar/core/resource/ResourceMapper.java
index 7e500ac589a..2d1c7429d21 100644
--- a/sonar-core/src/main/java/org/sonar/core/resource/ResourceMapper.java
+++ b/sonar-core/src/main/java/org/sonar/core/resource/ResourceMapper.java
@@ -22,6 +22,7 @@ package org.sonar.core.resource;
 import org.apache.ibatis.annotations.Param;
 import org.apache.ibatis.session.ResultHandler;
 
+import java.util.Collection;
 import java.util.List;
 
 public interface ResourceMapper {
@@ -65,6 +66,11 @@ public interface ResourceMapper {
    */
   ResourceDto selectRootProjectByComponentId(@Param("componentId") Long componentId);
 
+  /**
+   * @since 3.6
+   */
+  List<Integer> selectChildrenComponentIds(@Param("componentRootKeys") Collection<String> componentRootKeys);
+
 
   void insert(ResourceDto resource);
 
diff --git a/sonar-core/src/main/resources/org/sonar/core/issue/db/IssueMapper.xml b/sonar-core/src/main/resources/org/sonar/core/issue/db/IssueMapper.xml
index 3ebf476c91e..09c2ab1fb14 100644
--- a/sonar-core/src/main/resources/org/sonar/core/issue/db/IssueMapper.xml
+++ b/sonar-core/src/main/resources/org/sonar/core/issue/db/IssueMapper.xml
@@ -171,26 +171,8 @@
   <sql id="selectQueryConditions">
     from issues i
     inner join (<include refid="org.sonar.core.user.AuthorizationMapper.selectAuthorizedRootProjectsIdsQuery" />) authorizedProjects on authorizedProjects.root_project_id=i.root_component_id
-    <if test="query.componentRoots() != null and query.componentRoots().size() > 0">
-      inner join projects rootprojects on rootprojects.enabled=${_true} and rootprojects.kee in
-      <foreach item="componentRoot" index="index" collection="query.componentRoots()" open="(" separator="," close=")">
-        #{componentRoot}
-      </foreach>
-      inner join snapshots rootsnapshots on rootsnapshots.project_id=rootprojects.id and rootsnapshots.islast=${_true}
-      inner join snapshots s on s.project_id=i.component_id and s.islast=${_true} and
-      (s.id=rootsnapshots.id or ((s.root_snapshot_id=rootsnapshots.id or s.root_snapshot_id=rootsnapshots.root_snapshot_id) and
-      <choose>
-        <when test="_databaseId == 'mssql'">
-          s.path LIKE rootsnapshots.path + CAST(rootsnapshots.id AS varchar(15)) + '.%'
-        </when>
-        <when test="_databaseId == 'mysql'">
-          s.path LIKE concat(rootsnapshots.path, rootsnapshots.id, '.%')
-        </when>
-        <otherwise>
-          s.path LIKE rootsnapshots.path || rootsnapshots.id || '.%'
-        </otherwise>
-      </choose>
-      ))
+    <if test="componentRootKeys != null and componentRootKeys.size() > 0">
+      inner join (<include refid="org.sonar.core.resource.ResourceMapper.selectChildrenComponentIdsQuery" />) components on components.project_id=i.component_id
     </if>
     <if test="query.components() != null and query.components().size() > 0">
       inner join projects project_component on project_component.id=i.component_id and project_component.enabled=${_true} and project_component.kee in
diff --git a/sonar-core/src/main/resources/org/sonar/core/resource/ResourceMapper.xml b/sonar-core/src/main/resources/org/sonar/core/resource/ResourceMapper.xml
index 1b1270b7dbe..41de56b6931 100644
--- a/sonar-core/src/main/resources/org/sonar/core/resource/ResourceMapper.xml
+++ b/sonar-core/src/main/resources/org/sonar/core/resource/ResourceMapper.xml
@@ -120,6 +120,35 @@
     </where>
   </select>
 
+  <select id="selectChildrenComponentIds" parameterType="map" resultType="int">
+    <include refid="selectChildrenComponentIdsQuery" />
+  </select>
+
+  <sql id="selectChildrenComponentIdsQuery">
+    select s.project_id
+    from snapshots s
+    inner join snapshots rootsnapshots on (rootsnapshots.id=s.id or ((rootsnapshots.id=s.root_snapshot_id or rootsnapshots.root_snapshot_id=s.root_snapshot_id) and
+      <choose>
+        <when test="_databaseId == 'mssql'">
+          s.path LIKE rootsnapshots.path + CAST(rootsnapshots.id AS varchar(15)) + '.%'
+        </when>
+        <when test="_databaseId == 'mysql'">
+          s.path LIKE concat(rootsnapshots.path, rootsnapshots.id, '.%')
+        </when>
+        <otherwise>
+          s.path LIKE rootsnapshots.path || rootsnapshots.id || '.%'
+        </otherwise>
+      </choose>))
+      and rootsnapshots.islast=${_true}
+    <where>
+      and s.islast=${_true}
+      and rootsnapshots.project_id in
+      (select p.id from projects p where p.enabled=${_true}
+        and p.kee in <foreach item="componentKey" index="index" collection="componentRootKeys" open="(" separator="," close=")">#{componentKey}</foreach>)
+    </where>
+  </sql>
+
+
   <insert id="insert" parameterType="Resource" useGeneratedKeys="true" keyProperty="id">
     insert into projects
     (name, long_name, description, scope, qualifier, kee, language, root_id, copy_resource_id, person_id, enabled, created_at)
diff --git a/sonar-core/src/test/java/org/sonar/core/resource/ResourceDaoTest.java b/sonar-core/src/test/java/org/sonar/core/resource/ResourceDaoTest.java
index 3ef4e110568..00102831f4e 100644
--- a/sonar-core/src/test/java/org/sonar/core/resource/ResourceDaoTest.java
+++ b/sonar-core/src/test/java/org/sonar/core/resource/ResourceDaoTest.java
@@ -28,6 +28,7 @@ import org.sonar.api.resources.Scopes;
 import org.sonar.core.persistence.AbstractDaoTestCase;
 
 import java.util.Collection;
+import java.util.Collections;
 import java.util.List;
 
 import static com.google.common.collect.Lists.newArrayList;
@@ -245,4 +246,17 @@ public class ResourceDaoTest extends AbstractDaoTestCase {
 
     assertEmptyTables("projects");
   }
+
+  @Test
+  public void should_find_children_component_ids(){
+    setupData("fixture");
+
+    assertThat(dao.findChildrenComponentIds(newArrayList("org.struts:struts"))).hasSize(4);
+    assertThat(dao.findChildrenComponentIds(newArrayList("org.struts:struts-core"))).hasSize(3);
+    assertThat(dao.findChildrenComponentIds(newArrayList("org.struts:struts:org.struts"))).hasSize(2);
+    assertThat(dao.findChildrenComponentIds(newArrayList("org.struts:struts:org.struts.RequestContext"))).hasSize(1);
+
+    assertThat(dao.findChildrenComponentIds(newArrayList("unknown"))).isEmpty();
+    assertThat(dao.findChildrenComponentIds(Collections.<String>emptyList())).isEmpty();
+  }
 }
diff --git a/sonar-core/src/test/resources/org/sonar/core/resource/ResourceDaoTest/fixture.xml b/sonar-core/src/test/resources/org/sonar/core/resource/ResourceDaoTest/fixture.xml
index 6c362ca7963..6445d143ed7 100644
--- a/sonar-core/src/test/resources/org/sonar/core/resource/ResourceDaoTest/fixture.xml
+++ b/sonar-core/src/test/resources/org/sonar/core/resource/ResourceDaoTest/fixture.xml
@@ -5,28 +5,28 @@
             description="the description" long_name="Apache Struts"
             enabled="[true]" language="java" copy_resource_id="[null]" person_id="[null]"/>
   <snapshots id="1" project_id="1" parent_snapshot_id="[null]" root_project_id="1" root_snapshot_id="[null]"
-               status="P" islast="[false]" purge_status="[null]"
+               status="P" islast="[true]" purge_status="[null]"
                period1_mode="[null]" period1_param="[null]" period1_date="[null]"
                period2_mode="[null]" period2_param="[null]" period2_date="[null]"
                period3_mode="[null]" period3_param="[null]" period3_date="[null]"
                period4_mode="[null]" period4_param="[null]" period4_date="[null]"
                period5_mode="[null]" period5_param="[null]" period5_date="[null]"
                depth="[null]" scope="PRJ" qualifier="TRK" created_at="2008-12-02 13:58:00.00" build_date="2008-12-02 13:58:00.00"
-               version="[null]" path="[null]"/>
+               version="[null]" path=""/>
 
   <!-- project -->
   <projects id="2" root_id="1" kee="org.struts:struts-core" name="Struts Core"
             scope="PRJ" qualifier="BRC" long_name="Struts Core"
             description="[null]" enabled="[true]" language="java" copy_resource_id="[null]" person_id="[null]"/>
   <snapshots id="2" project_id="2" parent_snapshot_id="1" root_project_id="1" root_snapshot_id="1"
-               status="P" islast="[false]" purge_status="[null]"
+               status="P" islast="[true]" purge_status="[null]"
                period1_mode="[null]" period1_param="[null]" period1_date="[null]"
                period2_mode="[null]" period2_param="[null]" period2_date="[null]"
                period3_mode="[null]" period3_param="[null]" period3_date="[null]"
                period4_mode="[null]" period4_param="[null]" period4_date="[null]"
                period5_mode="[null]" period5_param="[null]" period5_date="[null]"
                depth="[null]" scope="PRJ" qualifier="BRC" created_at="2008-12-02 13:58:00.00" build_date="2008-12-02 13:58:00.00"
-               version="[null]" path="[null]"/>
+               version="[null]" path="1."/>
 
   <!-- directory -->
   <projects long_name="org.struts" id="3" scope="DIR" qualifier="PAC" kee="org.struts:struts:org.struts"
@@ -34,14 +34,14 @@
               description="[null]"
               enabled="[true]" language="java" copy_resource_id="[null]" person_id="[null]"/>
   <snapshots id="3" project_id="3" parent_snapshot_id="2" root_project_id="1" root_snapshot_id="1"
-               status="P" islast="[false]" purge_status="[null]"
+               status="P" islast="[true]" purge_status="[null]"
                period1_mode="[null]" period1_param="[null]" period1_date="[null]"
                period2_mode="[null]" period2_param="[null]" period2_date="[null]"
                period3_mode="[null]" period3_param="[null]" period3_date="[null]"
                period4_mode="[null]" period4_param="[null]" period4_date="[null]"
                period5_mode="[null]" period5_param="[null]" period5_date="[null]"
                depth="[null]" scope="DIR" qualifier="PAC" created_at="2008-12-02 13:58:00.00" build_date="2008-12-02 13:58:00.00"
-               version="[null]" path="[null]"/>
+               version="[null]" path="1.2."/>
 
   <!-- file -->
   <projects long_name="org.struts.RequestContext" id="4" scope="FIL" qualifier="CLA" kee="org.struts:struts:org.struts.RequestContext"
@@ -50,12 +50,12 @@
             enabled="[true]" language="java" copy_resource_id="[null]" person_id="[null]"/>
 
   <snapshots id="4" project_id="4" parent_snapshot_id="3" root_project_id="1" root_snapshot_id="1"
-               status="P" islast="[false]" purge_status="[null]"
+               status="P" islast="[true]" purge_status="[null]"
                period1_mode="[null]" period1_param="[null]" period1_date="[null]"
                period2_mode="[null]" period2_param="[null]" period2_date="[null]"
                period3_mode="[null]" period3_param="[null]" period3_date="[null]"
                period4_mode="[null]" period4_param="[null]" period4_date="[null]"
                period5_mode="[null]" period5_param="[null]" period5_date="[null]"
                depth="[null]" scope="FIL" qualifier="CLA" created_at="2008-12-02 13:58:00.00" build_date="2008-12-02 13:58:00.00"
-               version="[null]" path="[null]"/>
+               version="[null]" path="1.2.3."/>
 </dataset>