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] Updated: (OPENJPA-1140) Inefficient SQL after detached objects containing collections are merged.
Date Thu, 18 Jun 2009 21:00:09 GMT

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

Ravi P Palacherla updated OPENJPA-1140:
---------------------------------------

    Attachment: openjpa_collection_sample.zip

Steps to Reproduce:
I uploaded openjpa_collection_map_sample.zip as standalone testcase.
It works on Oracle Database.

1. extract zip file

2. update configurations to use database schema configured in step2.
   src/META-INF/persistence.xml

3. run test
   3-1. if objects are not detached, executed SQLs are efficient.
   > ant test

   3-2. if objects are detached, OpenJPA generates lots of inefficient SQLs. 
   > ant test2

> 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
>         Attachments: openjpa_collection_sample.zip
>
>
> 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