[
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)
