preventing N+1 selects in JPA

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.

Solution

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.

Comments

[Esteve]

Do you really need EAGER fetching on Customer.orders?

[Henno Vermeulen]

Thank you, using native queries could definitely help. But this feels like you have to hand write something that should be the responsibility of the ORM to perform. This could lead to a lot of manual work and a maintenance nightmare.

[Adam Gent]

@SlowStrider The idea is you shouldn't have to do this very often. The N+1 lazy loading is generally the right way for most problems. Besides when it comes to performance reliable optimizing like this you will find that SQL (not JPA HQL) is your only option... so don't do it unless you have to.

[Adam Gent]

For the down voters it would help if you could please tell me why. N+1 select problem can't be magically fixed. You either have a cartesian product of fields or you lazy load each object. I'm not sure what people are expecting. There isn't a goddamn ORM that can predict database performance and randomly decide when to do eager (cartesian product) vs lazy (N+1). I'm not sure what folks are expecting. I'm sorry I couldn't just provide a cut'n paste of code to make this problem go away.

[Stijn de Witt]

I think the reason for the downvotes was that you say that N+1 lazy loading is generally the right way. I don't agree with that... Actually I think JPA's collection mapping is often counter productive. Just fire a separate (not native) query to get orders where customerId = .... Anyway I still don't think the downvotes are in order.