aboutsummaryrefslogtreecommitdiffstats
path: root/sonar-db
diff options
context:
space:
mode:
authorJulien Lancelot <julien.lancelot@sonarsource.com>2016-04-14 11:06:49 +0200
committerJulien Lancelot <julien.lancelot@sonarsource.com>2016-04-14 15:52:25 +0200
commitd19b4f0f976a97c6428c8f5fdf7d2c383bf2e760 (patch)
tree26d5dac2d9edf2ac4e7714d9a5c3f505b8524029 /sonar-db
parent21ce21cce54dbeaa40ee9ff3d57d4256e9e5cc3d (diff)
downloadsonarqube-d19b4f0f976a97c6428c8f5fdf7d2c383bf2e760.tar.gz
sonarqube-d19b4f0f976a97c6428c8f5fdf7d2c383bf2e760.zip
SONAR-7187 Fix perf issue in selectByQuery
Replace usage of RowBounds by SQL pagination
Diffstat (limited to 'sonar-db')
-rw-r--r--sonar-db/src/main/java/org/sonar/db/ce/CeActivityDao.java2
-rw-r--r--sonar-db/src/main/java/org/sonar/db/ce/CeActivityMapper.java2
-rw-r--r--sonar-db/src/main/resources/org/sonar/db/ce/CeActivityMapper.xml28
-rw-r--r--sonar-db/src/test/java/org/sonar/db/ce/CeActivityDaoTest.java33
4 files changed, 60 insertions, 5 deletions
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);
@@ -123,6 +125,22 @@ public class CeActivityDaoTest {
}
@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);
insert("TASK_2", REPORT, "PROJECT_1", CeActivityDto.Status.FAILED);
@@ -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();
+ }
+ }
}