Spring data n+1 problem and filtering on big entity

Question

I have a Spring Boot with hibernate/spring data project that has a performance issue, an entity with a lot of OneToMany relationships. Using hibernate can I fetch multiple entries and all its children in one query? The query is also filter based on various fields (which change according to what the user selects) thus writing a native script or hql will be difficult to implement and maintain.

I tried using EntityGraph but I ran in the Cartesian Product issue.

I know that I can use joi fetch, but there are a lot of filters for this entity and thus it will render it difficult to write.

Can you help me find a simpler solution to fix this performance issue?

Best regard

Answer 1

This approach (fetching multiple OneToMany in one query) created a ton of problem for me in the past (behind the scenes it does cartesian product before creating the entity for you).

https://vladmihalcea.com/hibernate-multiplebagfetchexception/

Vlad is the real deal. I followed his advice and "To avoid a Cartesian Product, you can fetch at most one association at a time. So, instead of executing a single JPQL query that fetches two associations."

If you execute 2 jpql queries in the same transaction (you may need a @Transactional annotation in your method to keep the transaction boundaries open between the 2 jpql queries) this will "combine" the results of the 2 queries in a single entity / collection with the best performance :)

See also here https://www.baeldung.com/java-hibernate-multiplebagfetchexception#ideal-solution-using-multiple-queries (paragraph 8 has the suggest solution just like Vlad's).

I hope it helped :)