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!!!

Solution 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.

Solution 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.

Solution 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);

Comments

[jirka.pinkas]

Hello Oliver, is it possible to do the same using findAll() method? Something like List<T> findAll(Pageable pageable)? (so that it would be possible to generate something like: SELECT * FROM TABLE ORDER BY xxx LIMIT yyy)? I did not figure the way how to do it (and started a "flame war" with Ralph in this thread) :-)

[Stephane]

What if I want to get them all in one page ? That is, ignoring pagination. Can I do something like new PageRequest(0, 0) ?

[JBCP]

@StephaneEybert - if you want everything, just leave off the Pageable parameter from your method definition. Of course you need to be careful with this and giant datasets though.

[mstahv]

Like Jirka, I'd like to find a solution for the findAll case as well. I came up with following stupid workaround, but that don't feel quite right: github.com/mstahv/spring-data-vaadin-crud/commit/…

[mstahv]

For find all query the proper method signature is List findAllBy(Pageable) A tip by Oliver via github.

[SeaBiscuit]

Since Page is performing count. is there a way for me to access the result of that count? Cheers

[user3444718]

just by calling method with return type of Slice, still makes count query though, is that correct?

[Prasanth Rajendran]

@user3444718, The Slice will not call the count query, please refer to this answer, I have explained the insights of the Slice query -> stackoverflow.com/a/64035276/3303074

[Sebastian Zubrinic]

From what I gather the Slice option also has performance issues in huge datasets, so the only viable solution is to implement a custom base repo with query limit as mentioned in stackoverflow.com/questions/26738199/…

[Prasanth Rajendran]

@SebastianZubrinic, even with the custom repository, you might encounter a similar implementation like sessionFactory.getCurrentSession().getNamedQuery("someQuery"‌​).setParameter("some‌​Field", someField)).setFirstResult(5).setMaxResults(30); and the slice basically like above where the offset is being calculated based on the page and the requested limit and fetching the result till it reaches the limit, and I don't sure the point of performance degradation. Sometimes distributed storage stacks like elasticsearch doesn't encourage deeper pagination, but most traditional databases support it

[Sebastian Zubrinic]

Hi @PrasanthRajendran I've just posted my solution in an answer. From what I gather, the issue is always the count and the base repo solution is the only one that really avoids it. Regarding elasticsearch, with similar queries and dataset, I've also faced huge issues. In particular Hibernate Search with Lucene takes a lot of time computing counts in a facetted search. Actually, I was not able to solve that yet. Maybe Solr or other implementations have better performance.

[Prasanth Rajendran]

@SebastianZubrinic, If you dig deeper Slice implementation you may wonder that Slice implementation also doing the same kind of thing that you are intended to do in your answer.

[Sanjiv Jivan]

Very useful answer