openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Fay Wang (JIRA)" <j...@apache.org>
Subject [jira] Commented: (OPENJPA-134) Extra unneeded SQL joins for OneToMany relationship with fetch type EAGER
Date Thu, 31 Jul 2008 22:10:31 GMT

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

Fay Wang commented on OPENJPA-134:
----------------------------------

Scenario (2) could no longer be reproduced with r680566:

@Entity
public class Order {
	@Id 
	@GeneratedValue(strategy=GenerationType.IDENTITY)
	 int oid;
	
	 double amount;
	 boolean delivered;
	 
	@ManyToOne (fetch=FetchType.LAZY)
	 Customer customer;
	
	@OneToMany (fetch=FetchType.EAGER , mappedBy="order")
	 Collection<OrderItem> lineitems = new ArrayList<OrderItem>();
...
}

@Entity

public class OrderItem {
	@Id 
	@GeneratedValue(strategy=GenerationType.IDENTITY)
	int lid;
	
	int quantity;
	double cost;
	 
	@ManyToOne (fetch=FetchType.LAZY)
	Order order;
...
}

JPQL:
--------
select o from Order o left join fetch o.lineitems

Generated SQL:
----------------------
SELECT t0.oid, t0.version, t0.amount, t0.CUSTOMER_COUNTRYCODE, t0.CUSTOMER_ID, 
t0.delivered, t1.ORDER_OID, t1.lid, t1.version, t1.cost, t1.PART_PARTNO, t1.quantity 
FROM Order t0  LEFT OUTER JOIN OrderItem t1 ON t0.oid = t1.ORDER_OID ORDER BY t1.ORDER_OID
ASC 

Note that the unneeded LEFT OUTER JOIN is gone .


> 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
>            Assignee: Fay Wang
>             Fix For: 0.9.7
>
>         Attachments: openjpa134_3.patch
>
>
> 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