I did find a workaround for this issue. It's idea can also be used to work around some other performance issues in JPA. The main idea is to efficiently prefetch any entity that OpenJPA would otherwise fetch with single find-by-id queries. Turn on OpenJPA trace and find out which entities are separately selected. Write a query that fetches exactly these entities. Then when performing the main query, OpenJPA will find these from the EntityManager cache. See also my comment on https://issues.apache.org/jira/browse/OPENJPA-2299
Unfortunately this workaround is a bit brittle because it depends on OpenJPA behavior that may change, in particular your query will depend on the fact that only the entities of the second list in the Java code of the entity suffers from this problem, not the first list. Note that the problem I had in OPENJPA-2299 does not have a tree structure. Fetching (sub)trees with JPA also suffers from N+1 select problems, but I don't think that these are directly related to your and my issue described here. These are for the nodes themselves, not for other entities they refer to. But for tree structures you CAN use a similar idea to efficiently fetch a full tree: make a query that fetches all of the nodes, then query for the root node. See also http://www.tikalk.com/java/load-a-tree-with-jpa-and-hibernate Met vriendelijke groet, Henno Vermeulen +31-6-50643787 Paushuize | Kromme Nieuwegracht 49 | 3512 HE Utrecht Huize Molenaar | Korte Nieuwstraat 6 | 3512 NM Utrecht -----Oorspronkelijk bericht----- Van: David Minor [mailto:davemi...@gmail.com] Verzonden: vrijdag 11 juli 2014 2:09 Aan: users@openjpa.apache.org Onderwerp: Re: Avoiding N+1 on collection in recursive relation The problem seems to be that in org.apache.openjpa.jdbc.sql.SelectImpl, as it's building up the necessary select statements, it keeps a HashSet of the fields that already have eager selects created for them, and doesn't create another eager select if one already exists for that particular field. So, when requesting a particular entity of class A { List<B> bs; List<A> children; }, OpenJPA creates parallel eager selects for 'A.bs' and 'A.children', but then as it examines 'children', it doesn't create a parallel eager select for their 'bs' since the field 'A.bs' already has an eager select for it according to the HashSet. Later as it's loading the data it uses individual statements to get the missing data. There's a comment in JDBCStoreManager prior to the call to createEagerSelects(): // create all our eager selects so that those fields are reserved // and cannot be reused during the actual eager select process, // preventing infinite recursion which makes sense, since you wouldn't necessarily want to select the children's children's children, etc. But in this case, it seems to prevent efficient selection of desired fields. I believe this is also the cause of the problem reported in https://issues.apache.org/jira/browse/OPENJPA-2299 In that case you have class A { List<B> list1; List<B> list2; }, and eager selects are created for list1's collection fields but not list2 (since both lists are of entity B). ________________________________________ From: Rick Curtis <curti...@gmail.com> Sent: Thursday, July 10, 2014 5:58 AM To: users Subject: Re: Avoiding N+1 on collection in recursive relation David - It seems like this question has been asked numerous times on this mailing list and no one has come up with a good answer. I too don't have an answer, but if you come up with anything please post back to the list. Thanks, Rick On Tue, Jul 8, 2014 at 6:56 PM, David Minor <davemi...@gmail.com> wrote: > I have an entity (A) that has a recursive OneToMany relation with > itself (children). A also has a OneToMany relation to entity B. > > Whenever A is fetched, the queries to fetch B for entity A and its children > are all separate queries. > > Is there any way to avoid this? > > -- > _____________ > David Minor > -- *Rick Curtis* -- _____________ David Minor
