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