preventing N+1 selects in JPA [duplicate]

Question

I have JPA entities Order with a ManyToOne relation to Customer. It is bidirectional, so that Customer also has a OneToMany field orders. Both of the relations use EAGER fetching (or are in the OpenJPA fetchplan).

When I select from Order, I get 1 select for orders and N selects for the Customer.orders field. To my surprise this problem is present with OpenJPA, EclipseLink and Hibernate, even when I use JOIN FETCH (which does work in a unidirectional case).

Is there a good way to solve this? Are there any solutions for solving N+1 select problems for more complex graphs?

EDIT: Results of my own research: - For OpenJPA (which I'm using) I don't know a solution yet - For Hibernate @Fetch(FetchMode.SUBSELECT) solves the problem. Using @BatchSize also helps, this selects a given number of customer.orders fields at the same time. - For EclipseLink I found a similar feature @BatchFetch(value=BatchFetchType.IN) but it does not help in this case, I suppose it cannot efficiently handle this in a bidirectional relation.

Answer 1

Have a look at: What is SELECT N+1? as there lots of good info there.

If your using Hibernate: Hibernate - Chapter 19: Improving Performance - Fetching Strategies

My own personal solution is to use native SQL and tmp ids table that is because generally IMHO the N+1 select problem is mainly a problem with batch processing. Otherwise lazy loading (typically N+1 solution) can be beneficial to performance.