Hibernate Pagination In-Memory Warning HHH000104

Hibernate Pagination and Collection Fetching Memory Warning

0 reputation · 09 Oct 2023, 00:32 UTC

138K views0

When implementing pagination using setFirstResult() and setMaxResults(), Hibernate typically delegates the limit and offset logic to the database dialect. However, this behavior changes when the query involves join fetching of collections.

The goal is to retrieve a paginated subset of entities while simultaneously loading their associated collections to avoid N+1 select issues. The constraint is that Hibernate may trigger a HHH000104 warning, indicating that pagination is being applied in-memory rather than at the database level.

Under what specific mapping conditions does Hibernate decide to perform in-memory pagination instead of generating a database-level LIMIT/OFFSET clause?
What are the performance implications for the JVM heap when the result set exceeds the setMaxResults value before the in-memory filter is applied?

Keep exploring this topic

Does Hibernate trigger in-memory pagination when using JOIN FETCH on collections?

An inquiry into the conditions that cause Hibernate to switch from database-level pagination to in-memory filtering when using JOIN FETCH on OneToMany associations.

Hibernate pagination result consistency and order limits

Examination of result consistency limits in Hibernate pagination when using setFirstResult and setMaxResults without explicit ordering.

Hibernate pagination behavior with collection join fetches

Analysis of Hibernate's HHH000104 warning and the performance implications of in-memory pagination when using join fetches with setFirstResult and setMaxResults.

Offset pagination with SUBSELECT versus cursor-based pagination for deep Hibernate queries

Choosing between offset-based pagination with SUBSELECT fetching and cursor-based pagination for deep Hibernate queries that join large collections, under bounded heap constraints.

In Hibernate 5.x and 6.x, a query that contains a JOIN FETCH on a collection cannot safely add a LIMIT/OFFSET in the generated SQL. The dialect therefore loads all matching rows into memory and then applies setFirstResult()/setMaxResults() in Java, which triggers the HHH000104 warning.

Because the entire result set—including all fetched collections—is materialized before the pagination filter, the JVM heap must hold every root entity and its collection rows, even if many will be discarded. This can increase GC pressure and, with large collections, lead to OutOfMemoryError.

To keep pagination at the database level, replace the collection fetch join with a @Fetch(FetchMode.SUBSELECT) or use @BatchSize so Hibernate issues a separate SELECT for the collection.

Hibernate Pagination and Collection Fetching Memory Warning

0 answers

Your perspective belongs here.

1 question comment