openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Vermeulen (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (OPENJPA-2299) N+1 select when eager fetching two collection fields of the same entity type
Date Mon, 21 Jul 2014 08:06:38 GMT

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

Vermeulen commented on OPENJPA-2299:
------------------------------------

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)

Mime
View raw message