[ https://issues.apache.org/jira/browse/OPENJPA-134?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12488521 ]
Rob Wisniewski commented on OPENJPA-134: ---------------------------------------- To illustrate the 'eager despite lazy' behavior... here's the before and after sql: svn 04/03/2007: executed once: SELECT t0.HOLDINGID, t1.ACCOUNTID, t1.PROFILE_USERID, t1.BALANCE, t1.CREATIONDATE, t1.LASTLOGIN, t1.LOGINCOUNT, t1.LOGOUTCOUNT, t1.OPENBALANCE, t0.PURCHASEDATE, t0.PURCHASEPRICE, t0.QUANTI TY, t2.SYMBOL, t2.CHANGE1, t2.COMPANYNAME, t2.HIGH, t2.LOW, t2.OPEN1, t2.PRICE, t2.VOLUME FROM HOLDINGEJB t0 LEFT OUTER JOIN ACCOUNTEJB t1 ON t0.ACCOUNT_ACCOUNTID = t1.ACCOUNTID LEFT OUTER JOIN QUOTEEJB t2 ON t0.QUOTE_SYMBOL = t2.SYMBOL WHERE t0.ACCOUNT_ACCOUNTID = ? executed once: SELECT t0.PROFILE_USERID, t0.BALANCE, t0.CREATIONDATE, t0.LASTLOGIN, t0.LOGINCOUNT, t0.LOGOUTCOUNT, t0.OPENBALANCE FROM ACCOUNTEJB t0 WHERE t0.ACCOUNTID = ? optimize for 1 row CORRECT svn 04/12/2007: executed once: SELECT t0.PROFILE_USERID, t0.BALANCE, t0.CREATIONDATE, t0.LASTLOGIN, t0.LOGINCOUNT, t0.LOGOUTCOUNT, t0.OPENBALANCE FROM ACCOUNTEJB t0 WHERE t0.ACCOUNTID = ? FOR READ ONLY optimize for 1 row executed 4 times: SELECT t1.ACCOUNTID, t1.PROFILE_USERID, t1.BALANCE, t1.CREATIONDATE, t1.LASTLOGIN, t1.LOGINCOUNT, t1.LOGOUTCOUNT, t1.OPENBALANCE FROM HOLDINGEJB t0 INNER JOIN ACCOUNTEJB t1 ON t0.ACCOUNT_A CCOUNTID = t1.ACCOUNTID WHERE t0.HOLDINGID = ? FOR READ ONLY optimize for 1 row > 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.