Way to disable count query from PageRequest for getting total pages?

Question

We are using Spring Data with the PageRequest and hitting a significantly large set of data. All queries perform optimally except the query being executed to get the total number of pages. Is there a way to disable this feature or would we most likely have to implement our own Pageable?

http://static.springsource.org/spring-data/data-commons/docs/1.3.2.RELEASE/api/org/springframework/data/domain/PageRequest.html

http://static.springsource.org/spring-data/data-commons/docs/1.3.2.RELEASE/api/org/springframework/data/domain/Pageable.html

Edit: After further analysis I believe the only way around this problem is to not use Spring Data and use EntityManager as it allows setting of start row and number of records to return. We only need whether next page is available so we just retrieve one extra record. We also need a dynamic query which doesn't seem possible in Spring Data.

Edit 2: And it would seem I just didn't wait long enough for some responses. Thanks guys!!!

Answer 1

The way to achieve this is simply by using List as return value. So for example for a repository defined like this:

interface CustomerRepository extends Repository<Customer, Long> {

  List<Customer> findByLastname(String lastname, Pageable pageable);
}

The query execution engine would apply the offset and pagesize as handed in by the Pageable but not trigger the additional count query as we don't need to construct a Page instance. This also documented in the relevant sections of the reference documentation.

Update: If you want the next page / previous page of Page but still skip the count query you may use Slice as the return value.

Answer 2

I was able to avoid the count performance degradation in a dynamic query (using Spring Data Specifications) with the base repository solution indicated in several post.

public class ExtendedRepositoryImpl<T, ID extends Serializable> extends SimpleJpaRepository<T, ID> implements ExtendedRepository<T, ID> {

    private EntityManager entityManager;

    public ExtendedRepositoryImpl(JpaEntityInformation<T, ?> entityInformation, EntityManager entityManager) {
        super(entityInformation, entityManager);
        this.entityManager = entityManager;
    }

    @Override
    public List<T> find(Specification<T> specification, int offset, int limit, Sort sort) {
        TypedQuery<T> query = getQuery(specification, sort);
        query.setFirstResult(offset);
        query.setMaxResults(limit);
        return query.getResultList();
    }

}

A query to retrieve 20 record slices, from a 6M records dataset, takes milliseconds with this approach. A bit over the same filtered queries run in SQL.

A similar implementation using Slice<T> find(Specification<T> specification, Pageable pageable) takes over 10 seconds.

And similar implementation returning Page<T> find(Specification<T> specification, Pageable pageable) takes around 15 seconds.

Answer 3

I had recently got such a requirement and the latest spring-boot-starter-data-jpa library has provided the out-of-box solution. Without count feature pagination can be achieved using org.springframework.data.domain.Slice interface.

An excerpt from blog

Depending on the database you are using in your application, it might become expensive as the number of items increased. To avoid this costly count query, you should instead return a Slice. Unlike a Page, a Slice only knows about whether the next slice is available or not. This information is sufficient to walk through a larger result set. Both Slice and Page are part of Spring Data JPA, where Page is just a sub-interface of Slice with a couple of additional methods. You should use Slice if you don't need the total number of items and pages.

@Repository
public interface UserRepository extends CrudRepository<Employee, String> {

    Slice<Employee> getByEmployeeId(String employeeId, Pageable pageable);

}

Sample code-snippet to navigate through larger result sets using Slice#hasNext. Until the hasNext method returns false, there is a possibility of data presence for the requested query criteria.

        int page = 0;
        int limit = 25;
        boolean hasNext;
        do {
            PageRequest pageRequest = PageRequest.of(page, limit );
            Slice<Employee> employeeSlice = employeeRepository.getByEmployeeId(sourceId, pageRequest);
            ++page;
            hasNext = employeeSlice .hasNext();
        } while (hasNext);