Hibernate Pagination: Memory-side loading when using JOIN FETCH

Answer to the Question

When you call setFirstResult() and setMaxResults() on a query that contains JOIN FETCH for a collection, Hibernate cannot push the pagination into the SQL. The join creates duplicate rows for the root entity, so Hibernate fetches the entire result set from the database and then performs the offset/limit in Java memory. This memory‑side filtering can cause OutOfMemoryError on large datasets.

Confirmed Behavior

• Hibernate generates SQL without LIMIT/OFFSET on the root table when a JOIN FETCH is present.
• After fetching, Hibernate applies DistinctRootEntityResultTransformer (or an equivalent) to deduplicate root entities.
• All matching rows—including duplicates—are loaded into the persistence context before pagination is applied.

What to Check

1. Enable SQL logging:

   logging.level.org.hibernate.SQL=DEBUG
   

   Run the query and confirm that the generated SQL does not contain LIMIT/OFFSET on the root table.
2. Count rows returned from the database (e.g., via a JDBC proxy or SELECT COUNT(*) in a separate query) and compare with the expected page size multiplied by the average number of fetched associations.
3. Monitor JVM heap (VisualVM, JConsole, or similar) while executing the query to detect a significant spike that would indicate in‑memory pagination.

Mitigation Strategies

• Use a distinct root entity transformer:

  query.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
  

  This keeps the root entities unique but does not eliminate the duplicate rows that are fetched.
• Disable pass‑through of DISTINCT:

  hibernate.query.passDistinctThrough=false
  

  Available from Hibernate 5.2+. This forces Hibernate to add a SELECT DISTINCT clause, reducing the number of rows returned.
• Replace JOIN FETCH with batch or subselect fetching:

  @BatchSize(size=30)
  private Set<Child> children;
  

  or

  fetch=FetchMode.SUBSELECT
  

  These strategies load collections lazily or in separate queries, allowing pagination to be applied at the database level.
• Two‑step query approach:
  1. First query: SELECT id FROM Parent WHERE … ORDER BY … LIMIT ? OFFSET ? to get the IDs for the desired page.
  2. Second query: FROM Parent p JOIN FETCH p.children WHERE p.id IN (:ids) to fetch the full entities for those IDs.
  This guarantees that only the rows for the page are fetched.

Version‑Specific Notes

Prior to Hibernate 5.2 the hibernate.query.passDistinctThrough hint does not exist, and some older releases may ignore the distinct transformer altogether. Verify the behavior on your specific version by inspecting the generated SQL.

Diagnostic Detail Needed

To tailor the recommendation precisely, please let us know the exact Hibernate version you are using. Certain strategies (e.g., the passDistinctThrough hint) are only available in newer releases.