Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 1.0.0
    • Fix Version/s: 1.2.0
    • Component/s: sql
    • Labels:
      None
    • Environment:
      all

      Description

      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?

      1. openjpa_241.patch
        11 kB
        Fay Wang
      2. openjpa_241_1.patch
        1 kB
        Fay Wang

        Issue Links

          Activity

          Rob Wisniewski created issue -
          Kevin Sutter made changes -
          Field Original Value New Value
          Link This issue is related to OPENJPA-292 [ OPENJPA-292 ]
          Hide
          Fay Wang added a comment -

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

          Show
          Fay Wang added a comment - 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 ; 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.
          Fay Wang made changes -
          Attachment openjpa_241.patch [ 12386589 ]
          Hide
          Catalina Wei added a comment -

          Patch from Fay Wang has been committed in svn r678828

          Show
          Catalina Wei added a comment - Patch from Fay Wang has been committed in svn r678828
          Catalina Wei made changes -
          Resolution Fixed [ 1 ]
          Assignee Catalina Wei [ fancy ]
          Fix Version/s 1.2.0 [ 12313102 ]
          Status Open [ 1 ] Closed [ 6 ]
          Hide
          Fay Wang added a comment -

          The attached patch addresses the following issues raised by Pinaki:

          1. Should all the fields including superclass fields be considered for matching? getDeclaredFieldMappings() only return the fields declared in the current class.
          2. Can be modified from linear scan to lookup. Please see ClassMapping.getField(String fieldName) or ClassMapping.getDeclaredField(String fieldName).
          3. Test cases?

          The test case will be attached to JIRA-292.

          Show
          Fay Wang added a comment - The attached patch addresses the following issues raised by Pinaki: 1. Should all the fields including superclass fields be considered for matching? getDeclaredFieldMappings() only return the fields declared in the current class. 2. Can be modified from linear scan to lookup. Please see ClassMapping.getField(String fieldName) or ClassMapping.getDeclaredField(String fieldName). 3. Test cases? The test case will be attached to JIRA-292.
          Fay Wang made changes -
          Attachment openjpa_241_1.patch [ 12386767 ]

            People

            • Assignee:
              Catalina Wei
              Reporter:
              Rob Wisniewski
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development