diff options
author | Julien Lancelot <julien.lancelot@sonarsource.com> | 2016-04-14 11:06:49 +0200 |
---|---|---|
committer | Julien Lancelot <julien.lancelot@sonarsource.com> | 2016-04-14 15:52:25 +0200 |
commit | d19b4f0f976a97c6428c8f5fdf7d2c383bf2e760 (patch) | |
tree | 26d5dac2d9edf2ac4e7714d9a5c3f505b8524029 /sonar-db | |
parent | 21ce21cce54dbeaa40ee9ff3d57d4256e9e5cc3d (diff) | |
download | sonarqube-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')
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(); + } + } } |