You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by "Rob Wisniewski (JIRA)" <ji...@apache.org> on 2007/04/13 00:17:15 UTC
[jira] Commented: (OPENJPA-134) Extra unneeded SQL joins for
OneToMany relationship with fetch type EAGER
[ https://issues.apache.org/jira/browse/OPENJPA-134?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12488520 ]
Rob Wisniewski commented on OPENJPA-134:
----------------------------------------
I did some performance testing and things look good here. My 'one to many eager' scenario, which would be affected the most by this looping query behavior, improved in performance by 6%, and verified by sql dumps.
I did notice that my lazy scenarios are almost 50% slower now, but looking at the sql dumps it appears that we were fetching eagerly even in those scenarios and this (or another JIRA?) seems to have fixed that functional error. Does that seem like something your changes would resolve? Did you run into anything that would cause an eager fetch even when lazy was specified?
> Extra unneeded SQL joins for OneToMany relationship with fetch type EAGER
> -------------------------------------------------------------------------
>
> Key: OPENJPA-134
> URL: https://issues.apache.org/jira/browse/OPENJPA-134
> Project: OpenJPA
> Issue Type: Bug
> Components: sql
> Reporter: Catalina Wei
> Fix For: 0.9.8
>
>
> Running JPAConfiguration default setting for EagerFetchMode (FetchModeValue.EAGER_PARALLEL),
> the SQL generated is sub-optimal.
> Consider the following entities:
> lineitems( OneToMany )
> Order <===========================> OrderItem
> order ( ManyToOne )
> Case 1: why not combining 2 SQL to 1 SQL ?
> =================================================================================================
> Order.lineitmes(EAGER):
> OrderItem.order(LAZY):
> Executing query: select o from Order o
> 2173 demo TRACE [main] openjpa.jdbc.SQL - <t 1094730048, conn 1318342292> executing prepstmnt 1299336562
> SELECT t0.oid, t0.version, t0.amount, t0.customer_countryCode, t0.customer_id, t0.delivered, t0.shipaddr FROM Order t0
> 2213 demo TRACE [main] openjpa.jdbc.SQL - <t 1094730048, conn 1318342292> [40 ms] spent
> 2223 demo TRACE [main] openjpa.jdbc.SQL - <t 1094730048, conn 1318342292> executing prepstmnt 1406424020
> SELECT t0.oid, t1.lid, t1.version, t1.cost, t1.order_oid, t1.part_partno, t1.quantity FROM Order t0 INNER JOIN OrderItem t1 ON t0.oid = t1.order_oid ORDER BY t0.oid ASC
> Case 2: extra unneeded LEFT OUTER JOIN, if eliminated, the selection aliase t2 should change to t1:
> =============================================================================================
> Order.lineitmes(EAGER):
> OrderItem.order(LAZY):
> Executing query: select o from Order o left join fetch o.lineitems
> 2403 demo TRACE [main] openjpa.jdbc.SQL - <t 1094730048, conn 1314410072> executing prepstmnt 1500797300
> SELECT t0.oid, t0.version, t0.amount, t0.customer_countryCode, t0.customer_id, t0.delivered, t0.shipaddr, t2.order_oid, t2.lid, t2.version, t2.cost, t2.part_partno, t2.quantity FROM Order t0 LEFT OUTER JOIN OrderItem t1 ON t0.oid = t1.order_oid LEFT OUTER JOIN OrderItem t2 ON t0.oid = t2.order_oid ORDER BY t2.order_oid ASC
> Case 3: why not generating 1 SQL ?
> ==================================================================================================
> Order.lineitmes(EAGER):
> OrderItem.order(EAGER):
> Executing query: select o from Order o
> 2343 demo TRACE [main] openjpa.jdbc.SQL - <t 1094730048, conn 1318342292> executing prepstmnt 384833264 SELECT t0.oid, t0.version, t0.amount, t0.customer_countryCode, t0.customer_id, t0.delivered, t0.shipaddr FROM Order t0
> 2383 demo TRACE [main] openjpa.jdbc.SQL - <t 1094730048, conn 1318342292> [40 ms] spent
> 2393 demo TRACE [main] openjpa.jdbc.SQL - <t 1094730048, conn 1318342292> executing prepstmnt 1722705582
> SELECT t0.oid, t1.lid, t1.version, t1.cost, t2.oid, t2.version, t2.amount, t2.customer_countryCode, t2.customer_id, t2.delivered, t2.shipaddr, t1.part_partno, t1.quantity FROM Order t0 INNER JOIN OrderItem t1 ON t0.oid = t1.order_oid LEFT OUTER JOIN Order t2 ON t1.order_oid = t2.oid ORDER BY t0.oid ASC
> 2393 demo TRACE [main] openjpa.jdbc.SQL - <t 1094730048, conn 1318342292> [0 ms] spent
> 3134 demo TRACE [main] openjpa.jdbc.SQL - <t 1094730048, conn 1318342292> executing prepstmnt 950548648
> SELECT t0.lid, t0.version, t0.cost, t1.oid, t1.version, t1.amount, t1.customer_countryCode, t1.customer_id, t1.delivered, t1.shipaddr, t0.part_partno, t0.quantity FROM OrderItem t0 LEFT OUTER JOIN Order t1 ON t0.order_oid = t1.oid WHERE t0.order_oid = ? [params=(int) 88]
> 3134 demo TRACE [main] openjpa.jdbc.SQL - <t 1094730048, conn 1318342292> [0 ms] spent
> Case 4: duplicate selections and redundant joins
> ==================================================================================================
> Order.lineitmes(EAGER):
> OrderItem.order(EAGER):
> Executing query: select o from Order o left join fetch o.lineitems
> 2273 demo TRACE [main] openjpa.jdbc.SQL - <t 1094730048, conn 1307463150> executing prepstmnt 1565154634
> SELECT t0.oid, t0.version, t0.amount, t0.customer_countryCode, t0.customer_id, t0.delivered, t0.shipaddr, t2.order_oid, t2.lid, t2.version, t2.cost, t3.oid, t3.version, t3.amount, t3.customer_countryCode, t3.customer_id, t3.delivered, t3.shipaddr, t2.part_partno, t2.quantity FROM Order t0 LEFT OUTER JOIN OrderItem t1 ON t0.oid = t1.order_oid LEFT OUTER JOIN OrderItem t2 ON t0.oid = t2.order_oid LEFT OUTER JOIN Order t3 ON t2.order_oid = t3.oid ORDER BY t2.order_oid ASC
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
Re: [jira] Commented: (OPENJPA-134) Extra unneeded SQL joins for OneToMany relationship with fetch type EAGER
Posted by Abe White <aw...@bea.com>.
> I did notice that my lazy scenarios are almost 50% slower now, but
> looking at the sql dumps it appears that we were fetching eagerly
> even in those scenarios and this (or another JIRA?) seems to have
> fixed that functional error. Does that seem like something your
> changes would resolve? Did you run into anything that would cause
> an eager fetch even when lazy was specified?
I had no intention of "fixing" any lazy loading behavior. The fact
that you're seeing different behavior makes me nervous. Can you post
details on the entities and the test case so we can determine for
sure whether the new or old behavior is correct?
_______________________________________________________________________
Notice: This email message, together with any attachments, may contain
information of BEA Systems, Inc., its subsidiaries and affiliated
entities, that may be confidential, proprietary, copyrighted and/or
legally privileged, and is intended solely for the use of the individual
or entity named in this message. If you are not the intended recipient,
and have received this message in error, please immediately return this
by email and then delete it.