0
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?

Mart123
  • 327
  • 2
  • 13
  • Check if any of these helps: https://dimitr.im/writing-dynamic-queries-with-spring-data-jpa, https://stackoverflow.com/questions/26738199/how-to-disable-count-when-specification-and-pageable-are-used-together – pringi Mar 11 '22 at 10:52
  • I don't understand your comment? It will execute (more or less) the same query for a count, including all the where stuff. If you omit that it will obviously be faster. Also it isn't Spring Data that generates the query but your JPA provider, so it could be even your own mapping that leads to that inefficient query in the first place. – M. Deinum Mar 11 '22 at 12:31
  • there is a big difference between count(*) versus count(field) in my case 10x. – Mart123 Mar 11 '22 at 12:41
  • usually it should use the id of the entity `MyObject` to do the count, in your case, is it doing so? I suspect there is an issue with the query itself, have you analyzed the query which was generated? – atish.s Mar 11 '22 at 18:16

0 Answers0