Spring Data JPA - Predicates - Join Fetch with Pagination - Very Slow response

Question

I'm using spring datat jpa predicates with Join Fetch and my response is very slow. I'm getting a warn message:

[WARN ] org.hibernate.hql.internal.ast.QueryTranslatorImpl : HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!

see this https://vladmihalcea.com/fix-hibernate-hhh000104-entity-fetch-pagination-warning-message/ — Lemmy, Apr 15 '20 at 08:11
Does this answer your question? [How can I avoid the Warning "firstResult/maxResults specified with collection fetch; applying in memory!" when using Hibernate?](https://stackoverflow.com/questions/11431670/how-can-i-avoid-the-warning-firstresult-maxresults-specified-with-collection-fe) — Jens Schauder, Apr 15 '20 at 12:43

score 0 · Answer 1 · answered May 20 '21 at 02:36

You could use a generic / reusable approach based on the two-queries approach.

One SQL query to retrieve the entities' IDs and a second query with an IN predicate including the IDs from the second query.

Implementing a custom Spring Data JPA Executor:

@NoRepositoryBean
public interface AsimioJpaSpecificationExecutor<E, ID extends Serializable> extends JpaSpecificationExecutor<E> {

  Page<ID> findEntityIds(Pageable pageable);
}


public class AsimioSimpleJpaRepository<E, ID extends Serializable> extends SimpleJpaRepository<E, ID>
        implements AsimioJpaSpecificationExecutor<E, ID> {

  private final EntityManager entityManager;
  private final JpaEntityInformation<E, ID> entityInformation;

  public AsimioSimpleJpaRepository(JpaEntityInformation<E, ID> entityInformation, EntityManager entityManager) {
    super(entityInformation, entityManager);
    this.entityManager = entityManager;
    this.entityInformation = entityInformation;
  }

  @Override
  public Page<ID> findEntityIds(Pageable pageable) {
    CriteriaBuilder criteriaBuilder = this.entityManager.getCriteriaBuilder();
    CriteriaQuery<ID> criteriaQuery = criteriaBuilder.createQuery(this.entityInformation.getIdType());
    Root<E> root = criteriaQuery.from(this.getDomainClass());

    // Get the entities ID only
    criteriaQuery.select((Path<ID>) root.get(this.entityInformation.getIdAttribute()));

    // Update Sorting
    Sort sort = pageable.isPaged() ? pageable.getSort() : Sort.unsorted();
    if (sort.isSorted()) {
      criteriaQuery.orderBy(toOrders(sort, root, criteriaBuilder));
    }

    TypedQuery<ID> typedQuery = this.entityManager.createQuery(criteriaQuery);

    // Update Pagination attributes
    if (pageable.isPaged()) {
      typedQuery.setFirstResult((int) pageable.getOffset());
      typedQuery.setMaxResults(pageable.getPageSize());
    }

    return PageableExecutionUtils.getPage(typedQuery.getResultList(), pageable,
      () -> executeCountQuery(this.getCountQuery(null, this.getDomainClass())));
  }

  protected static long executeCountQuery(TypedQuery<Long> query) {
    Assert.notNull(query, "TypedQuery must not be null!");

    List<Long> totals = query.getResultList();
    long total = 0L;

    for (Long element : totals) {
      total += element == null ? 0 : element;
    }

    return total;
  }
}

Your Repository class would be something like:

@Repository
public interface FilmDao extends JpaRepository<Film, Integer>, AsimioJpaSpecificationExecutor<Film, Integer> {

  @EntityGraph(
    type = EntityGraphType.FETCH,
    attributePaths = {
      "language", 
      "filmActors", "filmActors.actor"
    }
  )
  List<Film> findAll(@Nullable Specification<Film> spec);

  @EntityGraph(
    type = EntityGraphType.FETCH,
      attributePaths = {
      "language", 
      "filmActors", "filmActors.actor"
    }
  )
  Page<Integer> findEntityIds(Pageable pageable);
}

And your @Service-annotated class could do something like:

public Page<Film> retrieveFilms(Pageable page) {
    // Getting film ids and page data to prevent:
    // HHH000104: firstResult/maxResults specified with collection fetch; applying in memory! 
    // which affects application's performance
    Page<Integer> filmIdsPage = this.filmDao.findEntityIds(page);

    List<Film> result;
    List<Integer> filmIds = filmIdsPage.getContent();
    if (CollectionUtils.isEmpty(filmIds)) {
      result = Lists.newArrayList();
    } else {
      // Retrieve films using IN predicate
      Specification<Film> fimlIdInSpecification = FilmSpecifications.idIn(Sets.newHashSet(filmIds));
      result = this.filmDao.findAll(fimlIdInSpecification);
    }
    return PageableExecutionUtils.getPage(result, page, () -> filmIdsPage.getTotalElements());
  }

You can read more at https://tech.asimio.net/2021/05/19/Fixing-Hibernate-HHH000104-firstResult-maxResults-warning-using-Spring-Data-JPA.html

Spring Data JPA - Predicates - Join Fetch with Pagination - Very Slow response

1 Answers1