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] Updated: (OPENJPA-241) Extra SQL on lazy CMR load
Date Mon, 21 Jul 2008 23:50:31 GMT

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


Mime
View raw message