From d19b4f0f976a97c6428c8f5fdf7d2c383bf2e760 Mon Sep 17 00:00:00 2001
From: Julien Lancelot <julien.lancelot@sonarsource.com>
Date: Thu, 14 Apr 2016 11:06:49 +0200
Subject: SONAR-7187 Fix perf issue in selectByQuery

Replace usage of RowBounds by SQL pagination
---
 .../main/java/org/sonar/db/ce/CeActivityDao.java   |  2 +-
 .../java/org/sonar/db/ce/CeActivityMapper.java     |  2 +-
 .../resources/org/sonar/db/ce/CeActivityMapper.xml | 28 ++++++++++++++++--
 .../java/org/sonar/db/ce/CeActivityDaoTest.java    | 33 +++++++++++++++++++++-
 4 files changed, 60 insertions(+), 5 deletions(-)

(limited to 'sonar-db')

diff --git a/sonar-db/src/main/java/org/sonar/db/ce/CeActivityDao.java b/sonar-db/src/main/java/org/sonar/db/ce/CeActivityDao.java
index 050389c8858..e32a1be1535 100644
--- a/sonar-db/src/main/java/org/sonar/db/ce/CeActivityDao.java
+++ b/sonar-db/src/main/java/org/sonar/db/ce/CeActivityDao.java
@@ -69,7 +69,7 @@ public class CeActivityDao implements Dao {
       return Collections.emptyList();
     }
 
-    return mapper(dbSession).selectByQuery(query, new RowBounds(offset, pageSize));
+    return mapper(dbSession).selectByQuery(query, offset, pageSize);
   }
 
   public int countByQuery(DbSession dbSession, CeTaskQuery query) {
diff --git a/sonar-db/src/main/java/org/sonar/db/ce/CeActivityMapper.java b/sonar-db/src/main/java/org/sonar/db/ce/CeActivityMapper.java
index a59254827ed..ea4652d4185 100644
--- a/sonar-db/src/main/java/org/sonar/db/ce/CeActivityMapper.java
+++ b/sonar-db/src/main/java/org/sonar/db/ce/CeActivityMapper.java
@@ -33,7 +33,7 @@ public interface CeActivityMapper {
 
   List<CeActivityDto> selectByComponentUuid(@Param("componentUuid") String componentUuid);
 
-  List<CeActivityDto> selectByQuery(@Param("query") CeTaskQuery query, RowBounds rowBounds);
+  List<CeActivityDto> selectByQuery(@Param("query") CeTaskQuery query, @Param("offset") int offset, @Param("pageSize") int pageSize);
 
   List<CeActivityDto> selectOlderThan(@Param("beforeDate") long beforeDate);
 
diff --git a/sonar-db/src/main/resources/org/sonar/db/ce/CeActivityMapper.xml b/sonar-db/src/main/resources/org/sonar/db/ce/CeActivityMapper.xml
index d86c8f73212..08247b4faa1 100644
--- a/sonar-db/src/main/resources/org/sonar/db/ce/CeActivityMapper.xml
+++ b/sonar-db/src/main/resources/org/sonar/db/ce/CeActivityMapper.xml
@@ -4,6 +4,7 @@
 <mapper namespace="org.sonar.db.ce.CeActivityMapper">
 
   <sql id="columns">
+    ca.id,
     ca.uuid,
     ca.task_type as taskType,
     ca.component_uuid as componentUuid,
@@ -44,10 +45,33 @@
   </select>
 
   <select id="selectByQuery" parameterType="map" resultType="org.sonar.db.ce.CeActivityDto">
-    select
+    SELECT
     <include refid="columns"/>
     <include refid="sqlSelectByQuery" />
-    order by ca.submitted_at desc, ca.id desc
+    ORDER BY ca.id desc
+    LIMIT #{pageSize} OFFSET #{offset}
+  </select>
+
+  <select id="selectByQuery" parameterType="map" resultType="org.sonar.db.ce.CeActivityDto" databaseId="mssql">
+    SELECT * FROM (
+    SELECT ROW_NUMBER() OVER(ORDER BY id desc) AS NUMBER,
+      <include refid="columns"/>
+      <include refid="sqlSelectByQuery" />
+      ) AS QUERY
+    WHERE NUMBER BETWEEN (#{offset} * #{pageSize} + 1) AND ((#{offset} + 1) * #{pageSize})
+    ORDER BY id desc
+  </select>
+
+  <select id="selectByQuery" parameterType="map" resultType="org.sonar.db.ce.CeActivityDto" databaseId="oracle">
+    SELECT * FROM (
+      SELECT ROWNUM AS rn, t.* FROM (
+        SELECT
+        <include refid="columns"/>
+        <include refid="sqlSelectByQuery" />
+        ORDER BY ca.id desc
+      ) t
+    ) t
+    WHERE rn BETWEEN (#{offset} * #{pageSize} + 1) AND ((#{offset} + 1) * #{pageSize})
   </select>
 
   <select id="countByQuery" parameterType="map" resultType="int">
diff --git a/sonar-db/src/test/java/org/sonar/db/ce/CeActivityDaoTest.java b/sonar-db/src/test/java/org/sonar/db/ce/CeActivityDaoTest.java
index 8b2b03adde9..bb92b76097b 100644
--- a/sonar-db/src/test/java/org/sonar/db/ce/CeActivityDaoTest.java
+++ b/sonar-db/src/test/java/org/sonar/db/ce/CeActivityDaoTest.java
@@ -19,9 +19,12 @@
  */
 package org.sonar.db.ce;
 
+import com.google.common.base.Function;
 import com.google.common.base.Optional;
+import com.google.common.collect.FluentIterable;
 import java.util.Collections;
 import java.util.List;
+import javax.annotation.Nonnull;
 import org.junit.Rule;
 import org.junit.Test;
 import org.sonar.api.utils.internal.TestSystem2;
@@ -31,7 +34,6 @@ import static java.util.Collections.singletonList;
 import static org.assertj.core.api.Assertions.assertThat;
 import static org.sonar.db.ce.CeTaskTypes.REPORT;
 
-
 public class CeActivityDaoTest {
 
   TestSystem2 system2 = new TestSystem2().setNow(1_450_000_000_000L);
@@ -122,6 +124,22 @@ public class CeActivityDaoTest {
     assertThat(dtos).extracting("uuid").containsExactly("TASK_2");
   }
 
+  @Test
+  public void selectByQuery_is_paginated_and_return_results_sorted_from_last_to_first() {
+    insert("TASK_1", REPORT, "PROJECT_1", CeActivityDto.Status.SUCCESS);
+    insert("TASK_2", REPORT, "PROJECT_1", CeActivityDto.Status.FAILED);
+    insert("TASK_3", REPORT, "PROJECT_2", CeActivityDto.Status.SUCCESS);
+    insert("TASK_4", "views", null, CeActivityDto.Status.SUCCESS);
+
+    assertThat(selectPageOfUuids(0, 1)).containsExactly("TASK_4");
+    assertThat(selectPageOfUuids(1, 1)).containsExactly("TASK_3");
+    assertThat(selectPageOfUuids(0, 3)).containsExactly("TASK_4", "TASK_3", "TASK_2");
+    assertThat(selectPageOfUuids(0, 4)).containsExactly("TASK_4", "TASK_3", "TASK_2", "TASK_1");
+    assertThat(selectPageOfUuids(0, 100)).containsExactly("TASK_4", "TASK_3", "TASK_2", "TASK_1");
+    assertThat(selectPageOfUuids(0, 0)).isEmpty();
+    assertThat(selectPageOfUuids(10, 2)).isEmpty();
+  }
+
   @Test
   public void test_countByQuery() {
     insert("TASK_1", REPORT, "PROJECT_1", CeActivityDto.Status.SUCCESS);
@@ -262,4 +280,17 @@ public class CeActivityDaoTest {
     system2.setNow(date);
     underTest.insert(db.getSession(), dto);
   }
+
+  private List<String> selectPageOfUuids(int offset, int pageSize) {
+    return FluentIterable.from(underTest.selectByQuery(db.getSession(), new CeTaskQuery(), offset, pageSize)).transform(CeActivityToUuid.INSTANCE).toList();
+  }
+
+  private enum CeActivityToUuid implements Function<CeActivityDto, String> {
+    INSTANCE;
+
+    @Override
+    public String apply(@Nonnull CeActivityDto input) {
+      return input.getUuid();
+    }
+  }
 }
-- 
cgit v1.2.3