How a JPQL JOIN FETCH Query Turned 11,000 Records into 50 Million Rows in Hibernate

During a user‑registration batch import, a JPQL query that eagerly fetched two collections with JOIN FETCH turned 11,000 template records into roughly 50 million result rows, causing the application to run for over 90 minutes and eventually run out of memory.

Eager vs. Lazy Loading

Hibernate offers two fetching strategies: eager (JOIN FETCH) and lazy. Eager loading issues a single LEFT JOIN query that returns all related rows, which is fast for small data but can load millions of rows when collections are large. Lazy loading returns a proxy for the collection and issues a second query only when the collection is accessed, avoiding unnecessary data but potentially creating the N+1 query problem.

The Multiple‑Bag Problem

When a single query tries to JOIN FETCH two collections, Hibernate does not fetch them side‑by‑side; instead it performs a Cartesian‑product join. For a template with 5 rows in collection A and 5 rows in collection B, the expected 10 rows become 25 rows. Scaling this to 15,000 templates with 10–20 rows each yields the 50 million‑row explosion.

Investigation Steps

Extracted a heap dump from the failing instance and used JProfiler, discovering millions of duplicate entity instances.

Enabled Hibernate SQL logging, revealing a single generated SQL statement thousands of characters long—typical of a cross‑join.

Checked Hibernate statistics, confirming that the number of rows returned far exceeded the actual table size.

Solution (Three Parts)

Split the query. Instead of one query that joins both collections, fetch each collection separately. Hibernate’s first‑level cache automatically re‑assembles the entity.

@Query("SELECT t FROM Template t JOIN FETCH t.collectionA WHERE t.id = :id")
Template findWithCollectionA(@Param("id") Long id);

@Query("SELECT t FROM Template t JOIN FETCH t.collectionB WHERE t.id = :id")
Template findWithCollectionB(@Param("id") Long id);

Use batch fetching for the remaining collections. Apply @BatchSize(size = 20) so Hibernate issues an IN clause to load many child rows in a single statement instead of one query per parent.

@OneToMany(mappedBy = "template")
@BatchSize(size = 20)
private Set<CollectionAItem> collectionA;

Periodically clear the session. In long‑running import transactions, call session.flush() and session.clear() at intervals to prevent the first‑level cache from becoming a memory leak.

// example inside the import loop
if (counter % 1000 == 0) {
    entityManager.flush();
    entityManager.clear();
}

After applying these changes, the row count dropped by about nine times, processing time improved fifteenfold, and the nightly out‑of‑memory crashes disappeared.

Key Recommendations

Never include more than one collection in a single JOIN FETCH; use separate queries or @BatchSize for additional collections.

Inspect the actual SQL generated by Hibernate, not just the JPQL, because a concise JPQL can hide a complex, costly query.

Compare the number of rows returned with the real table size to detect Cartesian‑product multiplication.

For long‑running imports, schedule regular session flushes and clears to avoid cache‑driven memory leaks.