[jira] [Comment Edited] (OPENJPA-2299) N+1 select when eager fetching two collection fields of the same entity type

["Vermeulen (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/OPENJPA-2299?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14068305#comment-14068305 ] 

Vermeulen edited comment on OPENJPA-2299 at 7/21/14 8:07 AM:
-------------------------------------------------------------

I did find a (brittle) workaround for this issue. Turn on OpenJPA trace and find out which entities are separately selected with single find-by-id queries. These are all the fields of ProductOrderLine - in the second list - that refer to other entities. Then make a query to select all of these entities before using the same EntityManager to do the main query. OpenJPA will then use the EntityManager cache to find the separate objects instead of querying for them.

E.g. when your query is "SELECT z FROM ProductOrder z WHERE z.id IN (:ids)"
Then you do the following query first:

"SELECT pol.entityRef1, pol.entityRef2, .... FROM ProductOrder z JOIN z.usedProducts pol WHERE z.id IN (:ids)"

So the main idea is to efficiently prefetch any entity that OpenJPA would otherwise find by id. (In my real application the prefetching workaround is a bit more advanced because planned and actual orderlines refer to the same products that don't need to be prefetched twice.)



was (Author: slowstrider):
I did find a (brittle) workaround for this issue. Turn on OpenJPA trace and find out which entities are separately selected with single queries. These are all the fields of ProductOrderLine in the second list. Then make a to select all of these before using the same EntityManager to do your main query. OpenJPA will then use the EntityManager cache to find the separate objects instead of querying for them.

E.g. when your query is "SELECT z FROM ProductOrder z WHERE z.id IN (:ids)"
Then you do the following query first:

"SELECT pol.entityRef1, pol.entityRef2, .... FROM ProductOrder z JOIN z.usedProducts pol WHERE z.id IN (:ids)"

So the main idea is to efficiently prefetch any entity that OpenJPA would otherwise find by id. (In my real application the prefetching workaround is a bit more advanced because planned and actual orderlines refer to the same products that don't need to be prefetched twice.)


> N+1 select when eager fetching two collection fields of the same entity type
> ----------------------------------------------------------------------------
>
>                 Key: OPENJPA-2299
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-2299
>             Project: OpenJPA
>          Issue Type: Bug
>          Components: jdbc, jpa, performance
>    Affects Versions: 2.2.1, 2.3.0
>            Reporter: Vermeulen
>         Attachments: OPENJPA-2299-tests.zip
>
>
> I have a ProductOrder entity that has two @OneToMany lists of ProductOrderLines. When selecting from ProductOrder, eager fetching does not seem to recurse into the second list so that each field of each ProductOrderLine in the second list is fetched using a separate "load field" operation.
> The problem does not occur when there is only one List.
> @Entity
> public class ProductOrder {
> 	@JoinColumn(name = "PLANNED_ORDER_ID")
> 	@OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
> 	private List<ProductOrderLine> plannedOrderLines = new ArrayList<ProductOrderLine>();
> 	@JoinColumn(name = "ACTUAL_ORDER_ID")
> 	@OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
> 	private List<ProductOrderLine> actualOrderLines = new ArrayList<ProductOrderLine>();
> Expected nr of queries: 1 (for Order) + 1 (for Order.plannedOrderLines) + 1 (for Order.actualOrderLines).
> Actual nr of queries: same as expected + 1 query for each field of each ProductOrderLine in Order.actualOrderLines.
> Same problem exists when using @JoinTable instead of @JoinColumn (JPA 2.0 only).



--
This message was sent by Atlassian JIRA
(v6.2#6252)