[jira] Updated: (OPENJPA-241) Extra SQL on lazy CMR load

     [ https://issues.apache.org/jira/browse/OPENJPA-241?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Fay Wang updated OPENJPA-241:
-----------------------------

    Attachment: openjpa_241.patch

The attached patch fixes the scenario described by Rob. The following is the test I used to reproduce the problem:

@Entity
public class Customer {
...
    @OneToMany(fetch=FetchType.LAZY, 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;
...
}

The test case:

        String query = "select c FROM Customer c";
        Query q = em.createQuery(query);
        List list = q.getResultList();
        for (int i = 0; i < list.size(); i++) {
            Customer c = (Customer)list.get(i);
            System.out.println("Customer = " + c);
            Collection orders = c.getOrders();
            for (Iterator iter=orders.iterator(); iter.hasNext();) {
                Order order = (Order)iter.next();
                System.out.println("order = " + order);
            }
        }

As Rob indicates, since the Orders field in the Customer entity is lazy, when the Customer is first loaded, the Orders will not be loaded. After the Orders is loaded, the extra join sql is executed to get the customer field in the Order because it is an eager field.

The above test 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.name FROM PdqCustomer t0 WHERE t0.countryCode = ? AND t0.id = ?

(3) SELECT t0.oid, t0.version, t0.amount, t0.delivered
      FROM PdqOrder t0 WHERE t0.CUSTOMER_COUNTRYCODE = ? AND t0.CUSTOMER_ID = ?

(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

(7) 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
 
The purpose of sql (4) - (7) is to retrieve the inverse relationship of Customer in the Order entity. The attached fix will detect this relationship to get rid of these sql.  

Please note that this patch included the fix in JIRA-134 scenario 3. Please code review it. Any comments are mostly appreciated.

> Extra SQL on lazy CMR load
> --------------------------
>
>                 Key: OPENJPA-241
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-241
>             Project: OpenJPA
>          Issue Type: Bug
>          Components: sql
>    Affects Versions: 1.0.0
>         Environment: all
>            Reporter: Rob Wisniewski
>         Attachments: openjpa_241.patch
>
>
> This issue is similar to openjpa-134 but occurs in a slightly different manner.
> I've got two entities:
> Account (LAZY) (one) <--------> (many) (EAGER) Holding
> So when I load account nothing is loaded from holdings, but once I load the holding, I observe each holding separately making a database call to load it's eager account.  This is quite alot of overhead, and through a simple conditional check we should be able to avoid it.  Essentially if we're loading an entity from a CMR, and the multiplicity of the field from the bidirectional relationship is one, then we don't need to load the data for that entity.  We did something similar to this in openjpa-134 when everything was eager (essentially avoiding the extra left out joing back into account).  Can we avoid these separate calls?

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.