7

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.

Henno Vermeulen
  • 1,427
  • 2
  • 15
  • 25
  • This is somewhat of a rambly question. Do you have *one specific problem* you want to help with or do you just want to complain about JPA? – millimoose Nov 15 '12 at 15:13
  • You are right, I was getting a bit frustrated with JPA, I will edit my question to be more to the point. – Henno Vermeulen Nov 15 '12 at 15:23
  • 1
    Do you really need EAGER fetching on Customer.orders? – Esteve Nov 15 '12 at 15:50
  • @Esteve you have a very good point there. When making a simple list of Orders and their customers, you don't really need any of the other orders of those customers! Still there may be other cases where you DO need to load fields like this and I think it is possible to select this without an N+1 select – Henno Vermeulen Nov 15 '12 at 16:13

3 Answers3

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.

Community
  • 1
  • 1
Adam Gent
  • 44,449
  • 20
  • 142
  • 191
  • 3
    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. – Henno Vermeulen Nov 15 '12 at 16:17
  • @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 Nov 15 '12 at 16:33
  • 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. – Adam Gent Mar 03 '16 at 21:47
  • 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. – Stijn de Witt Apr 25 '16 at 19:36
0

Here is a solution:

  1. Separate the entity layer from the API layer and interact with the API instances solely within you application. The api in this context may also be referred to as a DTO.

  2. Remove the relationship entirely from the Entity.

  3. Create a mechanism to indicate that you wish to fetch the children. For example: propagate a fetchRequestList to the layer that maps the API to the Entity (that way you can fetch conditionally).

  4. During query execution gather the parent objects as you normally would.

  5. Retrieve the entire collection of children using a named parameterized query with an IN clause based on the FK to parent PK.

  6. Loop through the results and match them to the parents.

This would force the ORM to do n+1 queries rather than n(n+1) queries. Keep in mind that you will now have to inplement cascade save, delete, update, etc using custom logic.

cosbor11
  • 9,704
  • 8
  • 43
  • 58
-1

In any ORM framework, N+1 problem is common. You can't avoid that. But, This is more about what kinda approach you take to solve the issue. You can use associations and lazy load or eager load date based on your implementation. You can also perform the mapping the database and getc all the associated data in a single query and you can map it to your Model. Since Data base is indexed, this operation can be faster than you fetching data and mapping using N+1 queries (If your network latency permits).

om39a
  • 1,346
  • 4
  • 16
  • 38