Avoiding N+1 on collection in recursive relation

[David Minor <da...@gmail.com>]

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

RE: Avoiding N+1 on collection in recursive relation

[Henno Vermeulen <he...@huizemolenaar.nl>]

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:daveminor@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 <cu...@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 <da...@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

Re: Avoiding N+1 on collection in recursive relation

[David Minor <da...@gmail.com>]

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 <cu...@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 <da...@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

Re: Avoiding N+1 on collection in recursive relation

[Rick Curtis <cu...@gmail.com>]

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 <da...@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*