openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Catalina Wei (JIRA)" <j...@apache.org>
Subject [jira] Created: (OPENJPA-134) Extra unneeded SQL joins for OneToMany relationship with fetch type EAGER
Date Thu, 08 Feb 2007 01:48:05 GMT
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


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.


Mime
View raw message