From 11c9d4f6e496041a0dee7b7f637890a51f2a8632 Mon Sep 17 00:00:00 2001
From: Julien Lancelot <julien.lancelot@gmail.com>
Date: Fri, 14 Jun 2013 18:00:08 +0200
Subject: [PATCH] SONAR-4321 Fix performance issue on Issues search SQL

---
 .../org/sonar/core/issue/db/IssueDao.java     | 10 +--
 .../org/sonar/core/issue/db/IssueMapper.java  |  2 +
 .../org/sonar/core/resource/ResourceDao.java  |  5 +-
 .../sonar/core/resource/ResourceMapper.java   |  5 +-
 .../org/sonar/core/user/AuthorizationDao.java | 12 +--
 .../org/sonar/core/issue/db/IssueMapper.xml   | 43 ++++------
 .../org/sonar/core/persistence/rows-h2.sql    |  2 +
 .../org/sonar/core/persistence/schema-h2.ddl  |  4 +
 .../sonar/core/resource/ResourceMapper.xml    | 82 +++++++++----------
 .../sonar/core/user/AuthorizationMapper.xml   | 40 ++++-----
 .../sonar/core/resource/ResourceDaoTest.java  | 40 +++++++--
 .../core/resource/ResourceDaoTest/fixture.xml |  4 +
 ...nent_ids_for_secured_project_for_group.xml | 78 ++++++++++++++++++
 ...onent_ids_for_secured_project_for_user.xml | 76 +++++++++++++++++
 ..._add_index_to_snapshots_root_project_id.rb | 36 ++++++++
 .../405_add_index_to_group_roles_role.rb      | 36 ++++++++
 16 files changed, 357 insertions(+), 118 deletions(-)
 create mode 100644 sonar-core/src/test/resources/org/sonar/core/resource/ResourceDaoTest/should_find_children_component_ids_for_secured_project_for_group.xml
 create mode 100644 sonar-core/src/test/resources/org/sonar/core/resource/ResourceDaoTest/should_find_children_component_ids_for_secured_project_for_user.xml
 create mode 100644 sonar-server/src/main/webapp/WEB-INF/db/migrate/404_add_index_to_snapshots_root_project_id.rb
 create mode 100644 sonar-server/src/main/webapp/WEB-INF/db/migrate/405_add_index_to_group_roles_role.rb

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 7b93ecfb41f..9cc7fa17b8b 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
@@ -21,7 +21,6 @@
 package org.sonar.core.issue.db;
 
 import com.google.common.annotations.VisibleForTesting;
-import com.google.common.collect.Lists;
 import org.apache.ibatis.session.ResultHandler;
 import org.apache.ibatis.session.SqlSession;
 import org.sonar.api.BatchComponent;
@@ -35,10 +34,6 @@ import javax.annotation.Nullable;
 import java.util.Collection;
 import java.util.Collections;
 import java.util.List;
-import java.util.Map;
-
-import static com.google.common.collect.Lists.newArrayList;
-import static com.google.common.collect.Maps.newHashMap;
 
 /**
  * @since 3.6
@@ -118,9 +113,6 @@ public class IssueDao implements BatchComponent, ServerComponent {
     if (ids.isEmpty()) {
       return Collections.emptyList();
     }
-    Object idsPartition = Lists.partition(newArrayList(ids), 1000);
-    Map<String, Object> params = newHashMap();
-    params.put("ids", idsPartition);
-    return session.selectList("org.sonar.core.issue.db.IssueMapper.selectByIds", params);
+    return session.getMapper(IssueMapper.class).selectByIds(ids);
   }
 }
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 42c9b091340..18d9a4b98e5 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
@@ -36,6 +36,8 @@ public interface IssueMapper {
   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);
 
+  List<IssueDto> selectByIds(@Param("ids") Collection<Long> ids);
+
   void insert(IssueDto issue);
 
   int update(IssueDto issue);
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 d87ee7c7014..bcb7514322d 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
@@ -26,6 +26,7 @@ import org.sonar.core.component.ComponentDto;
 import org.sonar.core.persistence.MyBatis;
 
 import javax.annotation.CheckForNull;
+import javax.annotation.Nullable;
 
 import java.util.Collection;
 import java.util.Collections;
@@ -159,13 +160,13 @@ public class ResourceDao {
     return resourceDto != null ? toComponent(resourceDto) : null;
   }
 
-  public List<Integer> findChildrenComponentIds(Collection<String> componentRootKeys){
+  public List<Integer> findAuthorizedChildrenComponentIds(Collection<String> componentRootKeys, @Nullable Integer userId, String role){
     if (componentRootKeys.isEmpty()) {
       return Collections.emptyList();
     }
     SqlSession session = mybatis.openSession();
     try {
-      return session.getMapper(ResourceMapper.class).selectChildrenComponentIds(componentRootKeys);
+      return session.getMapper(ResourceMapper.class).selectAuthorizedChildrenComponentIds(componentRootKeys, userId, role);
     } finally {
       MyBatis.closeQuietly(session);
     }
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 05b68a913ee..ea89cbfac58 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,8 @@ package org.sonar.core.resource;
 import org.apache.ibatis.annotations.Param;
 import org.apache.ibatis.session.ResultHandler;
 
+import javax.annotation.Nullable;
+
 import java.util.Collection;
 import java.util.List;
 
@@ -69,7 +71,8 @@ public interface ResourceMapper {
   /**
    * @since 3.6
    */
-  List<Integer> selectChildrenComponentIds(@Param("componentRootKeys") Collection<String> componentRootKeys);
+  List<Integer> selectAuthorizedChildrenComponentIds(@Param("componentRootKeys") Collection<String> componentRootKeys,
+                                                     @Param("userId") @Nullable Integer userId, @Param("role") String role);
 
 
   void insert(ResourceDto resource);
diff --git a/sonar-core/src/main/java/org/sonar/core/user/AuthorizationDao.java b/sonar-core/src/main/java/org/sonar/core/user/AuthorizationDao.java
index 19be037eedd..90f0fcefb69 100644
--- a/sonar-core/src/main/java/org/sonar/core/user/AuthorizationDao.java
+++ b/sonar-core/src/main/java/org/sonar/core/user/AuthorizationDao.java
@@ -20,7 +20,6 @@
 package org.sonar.core.user;
 
 import com.google.common.collect.ImmutableMap;
-import com.google.common.collect.Lists;
 import com.google.common.collect.Sets;
 import org.apache.ibatis.session.SqlSession;
 import org.sonar.api.ServerComponent;
@@ -28,9 +27,11 @@ import org.sonar.core.persistence.MyBatis;
 
 import javax.annotation.Nullable;
 
-import java.util.*;
+import java.util.Collection;
+import java.util.Collections;
+import java.util.Map;
+import java.util.Set;
 
-import static com.google.common.collect.Lists.newArrayList;
 import static com.google.common.collect.Maps.newHashMap;
 
 public class AuthorizationDao implements ServerComponent {
@@ -57,13 +58,12 @@ public class AuthorizationDao implements ServerComponent {
     }
     String sql;
     Map<String, Object> params;
-    List<List<Long>> componentIdsPartition = Lists.partition(newArrayList(componentIds), 1000);
     if (userId == null) {
       sql = "keepAuthorizedComponentIdsForAnonymous";
-      params = ImmutableMap.of("role", role, "componentIds", componentIdsPartition);
+      params = ImmutableMap.of("role", role, "componentIds", componentIds);
     } else {
       sql = "keepAuthorizedComponentIdsForUser";
-      params = ImmutableMap.of("userId", userId, "role", role, "componentIds", componentIdsPartition);
+      params = ImmutableMap.of("userId", userId, "role", role, "componentIds", componentIds);
     }
 
     return Sets.newHashSet(session.<Long>selectList(sql, params));
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 aff84d57572..2d11f53f828 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
@@ -192,12 +192,7 @@
     inner join projects p on p.id=i.component_id
     inner join projects root on root.id=i.root_component_id
     <where>
-      and
-      <foreach collection="ids" open="i.id in (" close=")" item="list" separator=") or i.id in (">
-        <foreach collection="list" item="element" separator=",">
-          #{element}
-        </foreach>
-      </foreach>
+      and <foreach collection="ids" open="(" close=")" item="element" index="index" separator=" or ">i.id=#{element}</foreach>
     </where>
   </select>
 
@@ -225,13 +220,15 @@
 
   <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="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 test="componentRootKeys.size() == 0">
+      inner join (<include refid="org.sonar.core.user.AuthorizationMapper.selectAuthorizedRootProjectsIdsQuery" />) authorizedProjects on authorizedProjects.root_project_id=i.root_component_id
+    </if>
+    <if test="componentRootKeys.size() > 0">
+      inner join (<include refid="org.sonar.core.resource.ResourceMapper.selectAuthorizedChildrenComponentIdsQuery" />) authorizedComponents on authorizedComponents.project_id=i.component_id
     </if>
     <if test="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
-      <foreach item="component" index="index" collection="query.components()" open="(" separator="," close=")">#{component}
+      inner join projects project_component on project_component.id=i.component_id and project_component.enabled=${_true} and
+      <foreach item="component" index="index" collection="query.components()" open="(" separator=" or " close=")">project_component.kee=#{component}
       </foreach>
     </if>
     <if test="query.rules().size()>0">
@@ -240,23 +237,19 @@
     </if>
     <where>
       <if test="query.issueKeys().size()>0">
-        and i.kee in
-        <foreach item="key" index="index" collection="query.issueKeys()" open="(" separator="," close=")">#{key}
+        and <foreach item="key" index="index" collection="query.issueKeys()" open="(" separator=" or " close=")">i.kee=#{key}
         </foreach>
       </if>
       <if test="query.severities().size()>0">
-        and i.severity in
-        <foreach item="severity" index="index" collection="query.severities()" open="(" separator="," close=")">#{severity}
+        and <foreach item="severity" index="index" collection="query.severities()" open="(" separator=" or " close=")">i.severity=#{severity}
         </foreach>
       </if>
       <if test="query.statuses().size()>0">
-        and i.status in
-        <foreach item="status" index="index" collection="query.statuses()" open="(" separator="," close=")">#{status}
+        and <foreach item="status" index="index" collection="query.statuses()" open="(" separator=" or " close=")">i.status=#{status}
         </foreach>
       </if>
       <if test="query.resolutions().size()>0">
-        and i.resolution in
-        <foreach item="resolution" index="index" collection="query.resolutions()" open="(" separator="," close=")">#{resolution}
+        and <foreach item="resolution" index="index" collection="query.resolutions()" open="(" separator=" or " close=")">i.resolution=#{resolution}
         </foreach>
       </if>
       <if test="query.resolved() != null">
@@ -268,13 +261,11 @@
         </if>
       </if>
       <if test="query.reporters().size()>0">
-        and i.reporter in
-        <foreach item="reporter" index="index" collection="query.reporters()" open="(" separator="," close=")">#{reporter}
+        and <foreach item="reporter" index="index" collection="query.reporters()" open="(" separator=" or " close=")">i.reporter=#{reporter}
         </foreach>
       </if>
       <if test="query.assignees().size()>0">
-        and i.assignee in
-        <foreach item="assignee" index="index" collection="query.assignees()" open="(" separator="," close=")">#{assignee}
+        and <foreach item="assignee" index="index" collection="query.assignees()" open="(" separator=" or " close=")">i.assignee=#{assignee}
         </foreach>
       </if>
       <if test="query.assigned() != null">
@@ -294,9 +285,9 @@
         </if>
       </if>
       <if test="query.actionPlans().size()>0">
-        and i.action_plan_key in
-        <foreach item="action_plan" index="index" collection="query.actionPlans()" open="(" separator="," close=")">
-          #{action_plan}
+        and
+        <foreach item="action_plan" index="index" collection="query.actionPlans()" open="(" separator=" or " close=")">
+          i.action_plan_key=#{action_plan}
         </foreach>
       </if>
       <if test="query.createdAfter() != null">
diff --git a/sonar-core/src/main/resources/org/sonar/core/persistence/rows-h2.sql b/sonar-core/src/main/resources/org/sonar/core/persistence/rows-h2.sql
index 18020cb76a1..47f7780a0f8 100644
--- a/sonar-core/src/main/resources/org/sonar/core/persistence/rows-h2.sql
+++ b/sonar-core/src/main/resources/org/sonar/core/persistence/rows-h2.sql
@@ -167,6 +167,8 @@ INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('400');
 INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('401');
 INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('402');
 INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('403');
+INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('404');
+INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('405');
 INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('410');
 INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('411');
 
diff --git a/sonar-core/src/main/resources/org/sonar/core/persistence/schema-h2.ddl b/sonar-core/src/main/resources/org/sonar/core/persistence/schema-h2.ddl
index d32ca781b6b..99c8814b2c5 100644
--- a/sonar-core/src/main/resources/org/sonar/core/persistence/schema-h2.ddl
+++ b/sonar-core/src/main/resources/org/sonar/core/persistence/schema-h2.ddl
@@ -661,3 +661,7 @@ CREATE INDEX "ISSUE_FILTERS_NAME" ON "ISSUE_FILTERS" ("NAME");
 CREATE INDEX "ISSUE_FILTER_FAVS_USER" ON "ISSUE_FILTER_FAVOURITES" ("USER_LOGIN");
 
 CREATE UNIQUE INDEX "USERS_LOGIN" ON "USERS" ("LOGIN");
+
+CREATE INDEX "SNAPSHOTS_ROOT_PROJECT_ID" ON "SNAPSHOTS" ("ROOT_PROJECT_ID");
+
+CREATE INDEX "GROUP_ROLES_ROLE" ON "GROUP_ROLES" ("ROLE");
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 bfaff59ad83..3a3955aca9a 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
@@ -121,51 +121,49 @@
     </where>
   </select>
 
-  <select id="selectChildrenComponentIds" parameterType="map" resultType="int">
-    <include refid="selectChildrenComponentIdsQuery" />
+  <select id="selectAuthorizedChildrenComponentIds" parameterType="map" resultType="int">
+    <include refid="selectAuthorizedChildrenComponentIdsQuery" />
   </select>
 
-  <sql id="selectChildrenComponentIdsQuery">
-    -- project ids of the children of a component which is not PRJ/TRK
-    select children.project_id
-    from snapshots children
+  <sql id="selectAuthorizedChildrenComponentIdsQuery">
+    select s.project_id from snapshots s, (
+    select project_components.id as id,
+      snapshot_components.id as sid,
+      root_snapshot_components.project_id as root_project_id,
+      root_snapshot_components.id as root_snapshot_id,
+      snapshot_components.path as path
+    from projects project_components
+    inner join snapshots snapshot_components on snapshot_components.project_id = project_components.id and snapshot_components.islast = ${_true}
+    inner join snapshots root_snapshot_components on root_snapshot_components.project_id = snapshot_components.root_project_id and root_snapshot_components.islast = ${_true}
     inner join (
-      select rootSnapshot.id, rootSnapshot.root_snapshot_id, rootSnapshot.path
-      from snapshots rootSnapshot
-      inner join (
-        <include refid="org.sonar.core.resource.ResourceMapper.selectProjectIdsFromComponentKeysQuery" />
-      ) rootProject on rootProject.id = rootSnapshot.project_id
-      and rootSnapshot.islast=${_true}
-      and rootSnapshot.root_snapshot_id is not null
-    ) rootSnapshot on children.root_snapshot_id = rootSnapshot.root_snapshot_id
-    and
-    <choose>
-      <when test="_databaseId == 'mssql'">
-        children.path LIKE rootSnapshot.path + CAST(rootSnapshot.id AS varchar(15)) + '.%'
-      </when>
-      <when test="_databaseId == 'mysql'">
-        children.path LIKE concat(rootSnapshot.path, rootSnapshot.id, '.%')
-      </when>
-      <otherwise>
-        children.path LIKE rootSnapshot.path || rootSnapshot.id || '.%'
-      </otherwise>
-    </choose>
-    union
-    -- project ids of the children of a PRJ/TRK component
-    select children.project_id
-    from snapshots children
-    inner join (
-      select rootSnapshot.id
-      from snapshots rootSnapshot
-      inner join (
-        <include refid="org.sonar.core.resource.ResourceMapper.selectProjectIdsFromComponentKeysQuery" />
-      ) rootProject on rootProject.id = rootSnapshot.project_id
-      and rootSnapshot.islast=${_true}
-      and rootSnapshot.root_snapshot_id is null
-    ) rootSnapshot on children.root_snapshot_id = rootSnapshot.id
-    union
-    -- project id of the component itself
-    <include refid="org.sonar.core.resource.ResourceMapper.selectProjectIdsFromComponentKeysQuery" />
+      <include refid="org.sonar.core.user.AuthorizationMapper.selectAuthorizedRootProjectsIdsQuery" />
+    ) authorized_projects on authorized_projects.root_project_id = root_snapshot_components.project_id
+    <where>
+      and (<foreach item="componentRootKey" index="index" collection="componentRootKeys" open="(" separator=" or " close=")"> project_components.kee=#{componentRootKey}</foreach>)
+      and project_components.enabled = ${_true}
+    </where>
+    ) as authorized_input_components
+    <where>
+      and s.root_project_id = authorized_input_components.root_project_id
+      and s.islast = ${_true}
+      and (
+        (s.root_snapshot_id = authorized_input_components.root_snapshot_id
+          and
+          <choose>
+            <when test="_databaseId == 'mssql'">
+              s.path LIKE authorized_input_components.path + CAST(authorized_input_components.sid AS varchar(15)) + '.%'
+            </when>
+            <when test="_databaseId == 'mysql'">
+              s.path LIKE concat(authorized_input_components.path, authorized_input_components.sid, '.%')
+            </when>
+            <otherwise>
+              s.path LIKE authorized_input_components.path || authorized_input_components.sid || '.%'
+            </otherwise>
+          </choose>
+        )
+        or (s.id = authorized_input_components.sid)
+      )
+    </where>
   </sql>
 
   <sql id="selectProjectIdsFromComponentKeysQuery">
diff --git a/sonar-core/src/main/resources/org/sonar/core/user/AuthorizationMapper.xml b/sonar-core/src/main/resources/org/sonar/core/user/AuthorizationMapper.xml
index 2dfbded21f3..88677e1dd94 100644
--- a/sonar-core/src/main/resources/org/sonar/core/user/AuthorizationMapper.xml
+++ b/sonar-core/src/main/resources/org/sonar/core/user/AuthorizationMapper.xml
@@ -10,12 +10,7 @@
       gr.role=#{role}
       and (gr.group_id is null or gr.group_id in (select gu.group_id from groups_users gu where gu.user_id=#{userId}))
       and gr.resource_id = s.root_project_id and
-      <foreach collection="componentIds" open="project_id in (" close=")" item="list" separator=") or project_id in (" >
-        <foreach collection="list" item="element" separator=",">
-          #{element}
-        </foreach>
-      </foreach>
-
+      <foreach collection="componentIds" open="(" close=")" item="element" index="index" separator=" or " >project_id=#{element}</foreach>
       and s.islast = ${_true}
     UNION
     SELECT s.project_id
@@ -23,11 +18,7 @@
     WHERE
       ur.role=#{role}
       and ur.user_id=#{userId} and
-      <foreach collection="componentIds" open="s.project_id in (" close=")" item="list" separator=") or s.project_id in (" >
-        <foreach collection="list" item="element" separator=",">
-          #{element}
-        </foreach>
-      </foreach>
+      <foreach collection="componentIds" open="(" close=")" item="element" index="index" separator=" or " >project_id=#{element}</foreach>
       and s.islast = ${_true}
   </select>
 
@@ -38,11 +29,7 @@
         gr.role=#{role}
         and gr.group_id is null
         and gr.resource_id = s.root_project_id and
-        <foreach collection="componentIds" open="s.project_id in (" close=")" item="list" separator=") or s.project_id in (" >
-          <foreach collection="list" item="element" separator=",">
-            #{element}
-          </foreach>
-        </foreach>
+        <foreach collection="componentIds" open="(" close=")" item="element" index="index" separator=" or " >project_id=#{element}</foreach>
   </select>
 
   <select id="selectAuthorizedRootProjectsIds" parameterType="map" resultType="long">
@@ -55,24 +42,27 @@
         SELECT p.id as root_project_id
         FROM group_roles gr
         INNER JOIN projects p on p.id = gr.resource_id AND p.scope = 'PRJ' AND p.qualifier = 'TRK'
-        WHERE
-        gr.role=#{role}
-        and (gr.group_id is null or gr.group_id in (select gu.group_id from groups_users gu where gu.user_id=#{userId}))
+        <where>
+          and gr.role=#{role}
+          and (gr.group_id is null or gr.group_id in (select gu.group_id from groups_users gu where gu.user_id=#{userId}))
+        </where>
         UNION
         SELECT p.id as root_project_id
         FROM user_roles ur
         INNER JOIN projects p on p.id = ur.resource_id AND p.scope = 'PRJ' AND p.qualifier = 'TRK'
-        WHERE
-        ur.role=#{role}
-        and ur.user_id = #{userId}
+        <where>
+          and ur.role=#{role}
+          and ur.user_id = #{userId}
+        </where>
       </when>
       <otherwise>
         SELECT p.id as root_project_id
         FROM group_roles gr
         INNER JOIN projects p on p.id = gr.resource_id AND p.scope = 'PRJ' AND p.qualifier = 'TRK'
-        WHERE
-        gr.role=#{role}
-        and gr.group_id is null
+        <where>
+          and gr.role=#{role}
+          and gr.group_id is null
+        </where>
       </otherwise>
     </choose>
   </sql>
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 77146477c3e..94e9978f2f4 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
@@ -248,16 +248,42 @@ public class ResourceDaoTest extends AbstractDaoTestCase {
   }
 
   @Test
-  public void should_find_children_component_ids(){
+  public void should_find_children_component_ids_for_unsecured_project(){
     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.findAuthorizedChildrenComponentIds(newArrayList("org.struts:struts"), null, "user")).hasSize(4);
+    assertThat(dao.findAuthorizedChildrenComponentIds(newArrayList("org.struts:struts-core"), null, "user")).hasSize(3);
+    assertThat(dao.findAuthorizedChildrenComponentIds(newArrayList("org.struts:struts:org.struts"), null, "user")).hasSize(2);
+    assertThat(dao.findAuthorizedChildrenComponentIds(newArrayList("org.struts:struts:org.struts.RequestContext"), null, "user")).hasSize(1);
 
-    assertThat(dao.findChildrenComponentIds(newArrayList("unknown"))).isEmpty();
-    assertThat(dao.findChildrenComponentIds(Collections.<String>emptyList())).isEmpty();
+    assertThat(dao.findAuthorizedChildrenComponentIds(newArrayList("unknown"), null, "user")).isEmpty();
+    assertThat(dao.findAuthorizedChildrenComponentIds(Collections.<String>emptyList(), null, "user")).isEmpty();
+  }
+
+  @Test
+  public void should_find_children_component_ids_for_secured_project_for_user(){
+    setupData("should_find_children_component_ids_for_secured_project_for_user");
+
+    assertThat(dao.findAuthorizedChildrenComponentIds(newArrayList("org.struts:struts"), 100, "user")).hasSize(4);
+    assertThat(dao.findAuthorizedChildrenComponentIds(newArrayList("org.struts:struts-core"), 100, "user")).hasSize(3);
+    assertThat(dao.findAuthorizedChildrenComponentIds(newArrayList("org.struts:struts:org.struts"), 100, "user")).hasSize(2);
+    assertThat(dao.findAuthorizedChildrenComponentIds(newArrayList("org.struts:struts:org.struts.RequestContext"), 100, "user")).hasSize(1);
+
+    assertThat(dao.findAuthorizedChildrenComponentIds(newArrayList("unknown"), 100, "user")).isEmpty();
+    assertThat(dao.findAuthorizedChildrenComponentIds(Collections.<String>emptyList(), 100, "user")).isEmpty();
+  }
+
+  @Test
+  public void should_find_children_component_ids_for_secured_project_for_group(){
+    setupData("should_find_children_component_ids_for_secured_project_for_group");
+
+    assertThat(dao.findAuthorizedChildrenComponentIds(newArrayList("org.struts:struts"), 100, "user")).hasSize(4);
+    assertThat(dao.findAuthorizedChildrenComponentIds(newArrayList("org.struts:struts-core"), 100, "user")).hasSize(3);
+    assertThat(dao.findAuthorizedChildrenComponentIds(newArrayList("org.struts:struts:org.struts"), 100, "user")).hasSize(2);
+    assertThat(dao.findAuthorizedChildrenComponentIds(newArrayList("org.struts:struts:org.struts.RequestContext"), 100, "user")).hasSize(1);
+
+    assertThat(dao.findAuthorizedChildrenComponentIds(newArrayList("unknown"), 100, "user")).isEmpty();
+    assertThat(dao.findAuthorizedChildrenComponentIds(Collections.<String>emptyList(), 100, "user")).isEmpty();
   }
 
   @Test
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 0c72a978001..57be22f2d1c 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
@@ -1,5 +1,9 @@
 <dataset>
 
+  <!-- Struts projects is authorized for all user -->
+  <group_roles id="1" group_id="[null]" resource_id="1" role="user"/>
+
+
   <!-- root project -->
   <projects id="1" root_id="[null]" scope="PRJ" qualifier="TRK" kee="org.struts:struts" name="Struts"
             description="the description" long_name="Apache Struts"
diff --git a/sonar-core/src/test/resources/org/sonar/core/resource/ResourceDaoTest/should_find_children_component_ids_for_secured_project_for_group.xml b/sonar-core/src/test/resources/org/sonar/core/resource/ResourceDaoTest/should_find_children_component_ids_for_secured_project_for_group.xml
new file mode 100644
index 00000000000..7463dda0ea7
--- /dev/null
+++ b/sonar-core/src/test/resources/org/sonar/core/resource/ResourceDaoTest/should_find_children_component_ids_for_secured_project_for_group.xml
@@ -0,0 +1,78 @@
+<dataset>
+
+  <!-- user 100 has no direct grant access, but is in the group 200 that has the role "user"
+  on the project 1  -->
+  <user_roles id="1" user_id="100" resource_id="999" role="user"/>
+  <groups_users user_id="100" group_id="200"/>
+  <group_roles id="1" group_id="200" resource_id="1" role="user"/>
+
+
+  <!-- root project -->
+  <projects id="1" root_id="[null]" scope="PRJ" qualifier="TRK" kee="org.struts:struts" name="Struts"
+            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="[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=""/>
+  <snapshots id="10" project_id="1" parent_snapshot_id="[null]" root_project_id="1" root_snapshot_id="[null]"
+             status="P" islast="[false]" 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-01 13:58:00.00" build_date="2008-12-01 13:58:00.00"
+             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="[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="1."/>
+
+  <!-- directory -->
+  <projects long_name="org.struts" id="3" scope="DIR" qualifier="PAC" kee="org.struts:struts:org.struts"
+            name="org.struts" root_id="1"
+            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="[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="1.2."/>
+
+  <!-- file -->
+  <projects long_name="org.struts.RequestContext" id="4" scope="FIL" qualifier="CLA" kee="org.struts:struts:org.struts.RequestContext"
+            name="RequestContext" root_id="1"
+            description="[null]"
+            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="[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="1.2.3."/>
+
+</dataset>
diff --git a/sonar-core/src/test/resources/org/sonar/core/resource/ResourceDaoTest/should_find_children_component_ids_for_secured_project_for_user.xml b/sonar-core/src/test/resources/org/sonar/core/resource/ResourceDaoTest/should_find_children_component_ids_for_secured_project_for_user.xml
new file mode 100644
index 00000000000..52c069319c2
--- /dev/null
+++ b/sonar-core/src/test/resources/org/sonar/core/resource/ResourceDaoTest/should_find_children_component_ids_for_secured_project_for_user.xml
@@ -0,0 +1,76 @@
+<dataset>
+
+  <!-- user 100 has the role "user" on the project 1 and in group 200 -->
+  <user_roles id="1" user_id="100" resource_id="1" role="user"/>
+  <groups_users user_id="100" group_id="200"/>
+  <group_roles id="1" group_id="200" resource_id="999" role="user"/>
+
+  <!-- root project -->
+  <projects id="1" root_id="[null]" scope="PRJ" qualifier="TRK" kee="org.struts:struts" name="Struts"
+            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="[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=""/>
+  <snapshots id="10" project_id="1" parent_snapshot_id="[null]" root_project_id="1" root_snapshot_id="[null]"
+             status="P" islast="[false]" 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-01 13:58:00.00" build_date="2008-12-01 13:58:00.00"
+             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="[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="1."/>
+
+  <!-- directory -->
+  <projects long_name="org.struts" id="3" scope="DIR" qualifier="PAC" kee="org.struts:struts:org.struts"
+            name="org.struts" root_id="1"
+            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="[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="1.2."/>
+
+  <!-- file -->
+  <projects long_name="org.struts.RequestContext" id="4" scope="FIL" qualifier="CLA" kee="org.struts:struts:org.struts.RequestContext"
+            name="RequestContext" root_id="1"
+            description="[null]"
+            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="[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="1.2.3."/>
+
+</dataset>
diff --git a/sonar-server/src/main/webapp/WEB-INF/db/migrate/404_add_index_to_snapshots_root_project_id.rb b/sonar-server/src/main/webapp/WEB-INF/db/migrate/404_add_index_to_snapshots_root_project_id.rb
new file mode 100644
index 00000000000..b0d6b4e111c
--- /dev/null
+++ b/sonar-server/src/main/webapp/WEB-INF/db/migrate/404_add_index_to_snapshots_root_project_id.rb
@@ -0,0 +1,36 @@
+#
+# Sonar, entreprise quality control tool.
+# Copyright (C) 2008-2013 SonarSource
+# mailto:contact AT sonarsource DOT com
+#
+# SonarQube is free software; you can redistribute it and/or
+# modify it under the terms of the GNU Lesser General Public
+# License as published by the Free Software Foundation; either
+# version 3 of the License, or (at your option) any later version.
+#
+# SonarQube is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+# Lesser General Public License for more details.
+#
+# You should have received a copy of the GNU Lesser General Public License
+# along with this program; if not, write to the Free Software Foundation,
+# Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+#
+
+#
+# Sonar 3.6
+#
+class AddIndexToSnapshotsRootProjectId < ActiveRecord::Migration
+
+  def self.up
+    begin
+      add_index :snapshots, :root_project_id, :name => 'snapshots_root_project_id'
+    rescue
+      # already exists
+    end
+
+  end
+
+end
+
diff --git a/sonar-server/src/main/webapp/WEB-INF/db/migrate/405_add_index_to_group_roles_role.rb b/sonar-server/src/main/webapp/WEB-INF/db/migrate/405_add_index_to_group_roles_role.rb
new file mode 100644
index 00000000000..5a353ecbb5b
--- /dev/null
+++ b/sonar-server/src/main/webapp/WEB-INF/db/migrate/405_add_index_to_group_roles_role.rb
@@ -0,0 +1,36 @@
+#
+# Sonar, entreprise quality control tool.
+# Copyright (C) 2008-2013 SonarSource
+# mailto:contact AT sonarsource DOT com
+#
+# SonarQube is free software; you can redistribute it and/or
+# modify it under the terms of the GNU Lesser General Public
+# License as published by the Free Software Foundation; either
+# version 3 of the License, or (at your option) any later version.
+#
+# SonarQube is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+# Lesser General Public License for more details.
+#
+# You should have received a copy of the GNU Lesser General Public License
+# along with this program; if not, write to the Free Software Foundation,
+# Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+#
+
+#
+# Sonar 3.6
+#
+class AddIndexToGroupRolesRole < ActiveRecord::Migration
+
+  def self.up
+    begin
+      add_index :group_roles, :role, :name => 'group_roles_role'
+    rescue
+      # already exists
+    end
+
+  end
+
+end
+
-- 
2.39.5