openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Ravi P Palacherla (JIRA)" <j...@apache.org>
Subject [jira] Created: (OPENJPA-1140) Inefficient SQL after detached objects containing collections are merged.
Date Thu, 18 Jun 2009 20:54:07 GMT
Inefficient SQL after detached objects containing collections are merged.
-------------------------------------------------------------------------

                 Key: OPENJPA-1140
                 URL: https://issues.apache.org/jira/browse/OPENJPA-1140
             Project: OpenJPA
          Issue Type: Bug
          Components: sql
    Affects Versions: 2.0.0
            Reporter: Ravi P Palacherla
            Priority: Minor


When my application detaches objects including collection classes like Set and Map, 
OpenJPA generates inefficient SQL on commit.

For example, I created following sample case.

- model class A has one Set field and one Map field.
- test class do as follows.
  1. create A
  2. add 100 elements to Set field.
  3. add 100 key and value to Map field.
  4. persist A and commit
  5. detached all objects (em.clear())
  6. add one more element to Set field
  7. add one more key/value to Map field
  8. merge detached object to entity manager
  9. commit

If my application doesn't call em.clear, last commit() call in step 9 generates following
SQL.

----
     [java] 2814  openjpa  TRACE  [main] openjpa.jdbc.SQL - <t 3975755, conn
13640204> executing prepstmnt 14456678 SELECT t0.ELEMENT FROM JPA_A_ITEMS t0
WHERE t0.ID = ? [params=(int) 0]
     [java] 2816  openjpa  TRACE  [main] openjpa.jdbc.SQL - <t 3975755, conn
13640204> [1 ms] spent
     [java] 2819  openjpa  TRACE  [main] openjpa.jdbc.SQL - <t 3975755, conn
13640204> executing prepstmnt 32619928 SELECT JPA_A_ITEMS_ID_SEQUENCE.NEXTVAL
FROM DUAL
     [java] 2820  openjpa  TRACE  [main] openjpa.jdbc.SQL - <t 3975755, conn
13640204> [1 ms] spent
     [java] 2823  openjpa  TRACE  [main] openjpa.jdbc.SQL - <t 3975755, conn
13640204> executing prepstmnt 30836417 INSERT INTO JPA_A_ITEMS (ID, ELEMENT,
A_ITEM_ID) VALUES (?, ?, ?) [params=(int) 1005, (String) AItem: last, (int)
105]
     [java] 2824  openjpa  TRACE  [main] openjpa.jdbc.SQL - <t 3975755, conn
13640204> [1 ms] spent
     [java] 2825  openjpa  TRACE  [main] openjpa.jdbc.SQL - <t 3975755, conn
13640204> executing prepstmnt 19459570 INSERT INTO JPA_A_MAPS_C (MAP_ID,
MAP_KEY, MAP_VALUE) VALUES (?, ?, ?) [params=(int) 105, (String) key:last,
(String) value:last]
     [java] 2826  openjpa  TRACE  [main] openjpa.jdbc.SQL - <t 3975755, conn
13640204> [1 ms] spent
----

It's reasonable for us. But, If my application calls em.clear()to detach object A, 
OpenJPA generates lots of delete and re-insert/update SQL on last commit() call.

----
     [java] 2843  openjpa  TRACE  [main] openjpa.jdbc.SQL - <t 3975755, conn
13640204> executing prepstmnt 1335930 SELECT t0.ELEMENT FROM JPA
_A_ITEMS t0 WHERE t0.ID = ? [params=(int) 0]
     [java] 2844  openjpa  TRACE  [main] openjpa.jdbc.SQL - <t 3975755, conn
13640204> [1 ms] spent
     [java] 2942  openjpa  TRACE  [main] openjpa.jdbc.SQL - <t 3975755, conn
13640204> executing prepstmnt 4148925 SELECT JPA_A_ITEMS_ID_SEQU
ENCE.NEXTVAL FROM DUAL
     [java] 2943  openjpa  TRACE  [main] openjpa.jdbc.SQL - <t 3975755, conn
13640204> [1 ms] spent
     [java] 2955  openjpa  TRACE  [main] openjpa.jdbc.SQL - <t 3975755, conn
13640204> executing prepstmnt 14765441 DELETE FROM JPA_A_MAPS_C
WHERE MAP_ID = ? [params=(int) 106]
     [java] 2958  openjpa  TRACE  [main] openjpa.jdbc.SQL - <t 3975755, conn
13640204> [3 ms] spent
     [java] 2959  openjpa  TRACE  [main] openjpa.jdbc.SQL - <t 3975755, conn
13640204> executing prepstmnt 27752604 UPDATE JPA_A_ITEMS SET A_
ITEM_ID = ? WHERE A_ITEM_ID = ? [params=(null) null, (int) 106]
     [java] 2963  openjpa  TRACE  [main] openjpa.jdbc.SQL - <t 3975755, conn
13640204> [2 ms] spent
     [java] 2963  openjpa  TRACE  [main] openjpa.jdbc.SQL - <t 3975755, conn
13640204> executing prepstmnt 31416097 INSERT INTO JPA_A_ITEMS (
ID, ELEMENT, A_ITEM_ID) VALUES (?, ?, ?) [params=(int) 1106, (String) AItem:
last, (int) 106]
     [java] 2965  openjpa  TRACE  [main] openjpa.jdbc.SQL - <t 3975755, conn
13640204> [1 ms] spent
     [java] 2968  openjpa  TRACE  [main] openjpa.jdbc.SQL - <t 3975755, conn
13640204> batching prepstmnt 16477279 UPDATE JPA_A_ITEMS SET A_I
TEM_ID = ? WHERE ID = ? [params=(int) 106, (int) 1037]
     [java] 2969  openjpa  TRACE  [main] openjpa.jdbc.SQL - <t 3975755, conn
13640204> [0 ms] spent
     [java] 2969  openjpa  TRACE  [main] openjpa.jdbc.SQL - <t 3975755, conn
13640204> batching prepstmnt 16477279 UPDATE JPA_A_ITEMS SET A_I
TEM_ID = ? WHERE ID = ? [params=(int) 106, (int) 1073]
     ....
     [java] 3053  openjpa  TRACE  [main] openjpa.jdbc.SQL - <t 3975755, conn
13640204> batching prepstmnt 16477279 UPDATE JPA_A_ITEMS SET A_I
TEM_ID = ? WHERE ID = ? [params=(int) 106, (int) 1007]
     [java] 3054  openjpa  TRACE  [main] openjpa.jdbc.SQL - <t 3975755, conn
13640204> [1 ms] spent
     [java] 3054  openjpa  TRACE  [main] openjpa.jdbc.SQL - <t 3975755, conn
13640204> executing batch prepstmnt 16477279 UPDATE JPA_A_ITEMS
SET A_ITEM_ID = ? WHERE ID = ? [params=(int) 106, (int) 1007]
     [java] 3059  openjpa  TRACE  [main] openjpa.jdbc.SQL - <t 3975755, conn
13640204> [5 ms] spent
     [java] 3061  openjpa  TRACE  [main] openjpa.jdbc.SQL - <t 3975755, conn
13640204> batching prepstmnt 8906500 INSERT INTO JPA_A_MAPS_C (M
AP_ID, MAP_KEY, MAP_VALUE) VALUES (?, ?, ?) [params=(int) 106, (String)
key:23, (String) value:23]
     [java] 3062  openjpa  TRACE  [main] openjpa.jdbc.SQL - <t 3975755, conn
13640204> [0 ms] spent
     [java] 3062  openjpa  TRACE  [main] openjpa.jdbc.SQL - <t 3975755, conn
13640204> batching prepstmnt 8906500 INSERT INTO JPA_A_MAPS_C (M
AP_ID, MAP_KEY, MAP_VALUE) VALUES (?, ?, ?) [params=(int) 106, (String)
key:90, (String) value:90]
     [java] 3063  openjpa  TRACE  [main] openjpa.jdbc.SQL - <t 3975755, conn
13640204> [0 ms] spent
     ....
     [java] 3167  openjpa  TRACE  [main] openjpa.jdbc.SQL - <t 3975755, conn
13640204> batching prepstmnt 8906500 INSERT INTO JPA_A_MAPS_C (M
AP_ID, MAP_KEY, MAP_VALUE) VALUES (?, ?, ?) [params=(int) 106, (String)
key:44, (String) value:44]
     [java] 3167  openjpa  TRACE  [main] openjpa.jdbc.SQL - <t 3975755, conn
13640204> [0 ms] spent
     [java] 3168  openjpa  TRACE  [main] openjpa.jdbc.SQL - <t 3975755, conn
13640204> executing batch prepstmnt 8906500 INSERT INTO JPA_A_MA
PS_C (MAP_ID, MAP_KEY, MAP_VALUE) VALUES (?, ?, ?) [params=(int) 106,
(String) key:44, (String) value:44]
     [java] 3169  openjpa  TRACE  [main] openjpa.jdbc.SQL - <t 3975755, conn
13640204> [1 ms] spent
     [java] check executed SQL.
----

If objects are detached, OpenJPA processes it on commit() call.

1. delete current existing all collections.
   for Map)
     DELETE FROM JPA_A_MAPS_C WHERE MAP_ID = ?
   for Set)
     UPDATE JPA_A_ITEMS SET A_ITEM_ID = ? WHERE A_ITEM_ID = ?
 
2. re-insert/update all collections 100 times.
   for Map)
     UPDATE JPA_A_ITEMS SET A_ITEM_ID = ? WHERE ID = ?
   for Set)
     INSERT INTO JPA_A_MAPS_C (MAP_ID, MAP_KEY, MAP_VALUE) VALUES (?, ?, ?)

I think it could not be accepted for most applications.

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