[jira] Updated: (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:all-tabpanel ]

Fay Wang updated OPENJPA-134:
-----------------------------

    Attachment: openjpa134_3.patch

I have tested the following scenario:

@Entity
public class Customer {
..
   @OneToMany(fetch=FetchType.EAGER, mappedBy="customer")
    private Collection<Order> orders = new ArrayList<Order>();
...
}

@Entity
public class Order {
        @Id
        @GeneratedValue(strategy=GenerationType.IDENTITY)
         int oid;

         double amount;
         boolean delivered;
         
         @ManyToOne (fetch=FetchType.EAGER)
         Customer customer;

...

}

A JPQL :
   Select c from Customer c

generates the following SQL:

(1) SELECT t0.countryCode, t0.id, t0.version, t0.city, t0.state, t0.street, t0.zip
      FROM PdqCustomer t0

(2) SELECT t0.countryCode, t0.id, t1.oid, t1.version, t1.amount, t1.delivered
      FROM PdqCustomer t0
      INNER JOIN PdqOrder t1 ON t0.countryCode = t1.CUSTOMER_COUNTRYCODE AND
                             t0.id = t1.CUSTOMER_ID ORDER BY t0.countryCode ASC, t0.id ASC

(3) SELECT t1.countryCode, t1.id, t1.version, t1.city, t1.state, t1.street, t1.zip
      FROM PdqOrder t0
      INNER JOIN PdqCustomer t1 ON t0.CUSTOMER_COUNTRYCODE = t1.countryCode
                             AND t0.CUSTOMER_ID = t1.id WHERE t0.oid = ?  optimize for 1 row

(4) SELECT t1.countryCode, t1.id, t1.version, t1.city, t1.state, t1.street, t1.zip
      FROM PdqOrder t0
      INNER JOIN PdqCustomer t1 ON t0.CUSTOMER_COUNTRYCODE = t1.countryCode
                              AND t0.CUSTOMER_ID = t1.id WHERE t0.oid = ?  optimize for 1 row

(5) SELECT t1.countryCode, t1.id, t1.version, t1.city, t1.state, t1.street, t1.zip
      FROM PdqOrder t0
      INNER JOIN PdqCustomer t1 ON t0.CUSTOMER_COUNTRYCODE = t1.countryCode
                             AND t0.CUSTOMER_ID = t1.id WHERE t0.oid = ?  optimize for 1 row

(6) SELECT t1.countryCode, t1.id, t1.version, t1.city, t1.state, t1.street, t1.zip
      FROM PdqOrder t0
      INNER JOIN PdqCustomer t1 ON t0.CUSTOMER_COUNTRYCODE = t1.countryCode
                              AND t0.CUSTOMER_ID = t1.id WHERE t0.oid = ?  optimize for 1 row

Note that the last four sql are the same ((3) -(6)). This is because there are four orders for the customer and each loading of order will load its inverse relationship with customer.

This case is most similar to the case (3) described by Catalina. The attached patch detects the inverse relationship and get rid of sql (3) to (6).

> 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.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.