'JPA findAll(Specification specification, Pageable pageable) count is very costly and slow

public Page<MyObject> findByCriteria(MySearchFilters mySearchFilters, PageRequest pageRequest) {
        Page<MyObject> all = myObjectRepository.findAll(new Specification<MyObject>() {
            @Override
            public Predicate toPredicate(Root<MyObject> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
                List<Predicate> predicates = new ArrayList<>();

            ................ here is lots of different optional fields that might or not get added to query

                return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
            }

            private void addLikeCriteria(String field, String fieldName, CriteriaBuilder criteriaBuilder, Root<MyObject> root, List<Predicate> predicates) {
                predicates.add(criteriaBuilder.like(criteriaBuilder.lower(root.get(fieldName).as(String.class)), "%" + field.toLowerCase() + "%"));
            }
        }, pageRequest);
        return all;
    }

I am calling the method which in the background does the also count(field) query that is very costly! I analyzed it and if it would do count(*) instead it would be 10x faster as then it does not have to check the value.

Page<T> findAll(@Nullable Specification<T> spec, Pageable pageable);

Is it possible to make it count() instead or should I make a custom Specification, pageable repository method that would not do the count(field) and do the count() myself to attach to the result?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source