Hi,

I'm using hibernate's saveOrUpdate(myObject) method for insert/update operation. The "myObject" that i'm passing in the saveOrUpdate method contains 1 Set object that contains around 700 objects. Save operation on "myObject" takes around 20 minutes. I'm using batch insert/update with batch-size=20. 20 minutes is considerably a very large amount of time so i want to reduce the insertion/updation time.

I've observed that during save operation, first the hibernate executes select operation for every object in the Set object, e.g. "[12/6/05 17:18:26:848 IST] 40867c0b SystemOut O Hibernate: select analysisse_.MAHDLCD, analysisse_.MAHANAL, analysisse_.MAHDESC as MAHDESC9_, analysisse_.MAHEVTCODE as MAHEVTCODE9_, analysisse_.MAHMULTZN as MAHMULTZN9_, analysisse_.MAHMULTDP as MAHMULTDP9_ from PCMANLHD00 analysisse_ where analysisse_.MAHDLCD=? and analysisse_.MAHANAL=?".

And then hibernate fires the insert operation for every object in the Set object. That is why it is taking 20 minutes.

How can i improve the performance of the insert operation.

Following is the mapping for the Set object that i have specified in the hbm file of "myObject".

<set name="setForAnalysisLine" table="tableName" cascade="save-update" batch-size="20" inverse="true" lazy="true">
<key>
<column name="MALDLCD" not-null="true"/>
<column name="MALANAL" not-null="true"/>
</key>
<one-to-many class="AnalysisSetLine" />
</set>

Please tell me if something can be changed/added to the above mapping in order to improve the performance.

Thanks in advance,
Anurag

20 minutes ? This sounds very strange.
How long does it take, if you manually insert
the 700 objects ?
And how long does the query (select) take,
if you execute it alone ?

Sorry It took around 16 minutes (not 20 minutes) for inserting 700 objects.
When i inserted data for 700 objects using raw jdbc insert query then it took less than 4 minutes.
Could you please suggest any solution to reduce the execution time for hibernate's saveOrUpdate() method?
should i make any change in the mapping of the Set object?

Thanks

Can you show the generated sql ?

generated sql is pasted below.
This sql is for 8 object's insert operation.

[12/8/05 14:56:23:712 IST] 45b9f8dc SystemOut O Hibernate: select analysisse_.MAHDLCD, analysisse_.MAHANAL, analysisse_.MAHDESC as MAHDESC9_, analysisse_.MAHEVTCODE as MAHEVTCODE9_, analysisse_.MAHMULTZN as MAHMULTZN9_, analysisse_.MAHMULTDP as MAHMULTDP9_, analysisse_.MAHSTATUS as MAHSTATUS9_, analysisse_.MAHSCNID as MAHSCNID9_, analysisse_.MAHRLINKS as MAHRLINKS9_, analysisse_.MAHCHANGE as MAHCHANGE9_, analysisse_.MAHTEMPID as MAHTEMPID9_, analysisse_.MAHTEMPYN as MAHTEMPYN9_, analysisse_.MAHINUSER as MAHINUSER9_, analysisse_.MAHPARTIAL as MAHPARTIAL9_, analysisse_.MAHLVDATE as MAHLVDATE9_, analysisse_.MAHLADATE as MAHLADATE9_, analysisse_.MAHCRTUSER as MAHCRTUSER9_, analysisse_.MAHCRTDATE as MAHCRTDATE9_, analysisse_.MAHJOB as MAHJOB9_, analysisse_.MAHUSER as MAHUSER9_, analysisse_.MAHOPCODE as MAHOPCODE9_, analysisse_.MAHTMSTMP as MAHTMSTMP9_, analysisse_.MAHLSVIEW as MAHLSVIEW9_ from PCMANLHD00 analysisse_ where analysisse_.MAHDLCD=? and analysisse_.MAHANAL=?
[12/8/05 14:56:25:131 IST] 45b9f8dc SystemOut O Hibernate: select analysisse_.MALDLCD, analysisse_.MALANAL, analysisse_.MALLINE, analysisse_.MALSTATUS as MALSTATUS10_, analysisse_.MALRLNK as MALRLNK10_, analysisse_.MALSCNID as MALSCNID10_, analysisse_.MALJOB as MALJOB10_, analysisse_.MALUSER as MALUSER10_, analysisse_.MALOPCODE as MALOPCODE10_, analysisse_.MALTMSTMP as MALTMSTMP10_ from PCMANLLN00 analysisse_ where analysisse_.MALDLCD=? and analysisse_.MALANAL=? and analysisse_.MALLINE=?
[12/8/05 14:56:26:643 IST] 45b9f8dc SystemOut O Hibernate: select analysisse_.MALDLCD, analysisse_.MALANAL, analysisse_.MALLINE, analysisse_.MALSTATUS as MALSTATUS10_, analysisse_.MALRLNK as MALRLNK10_, analysisse_.MALSCNID as MALSCNID10_, analysisse_.MALJOB as MALJOB10_, analysisse_.MALUSER as MALUSER10_, analysisse_.MALOPCODE as MALOPCODE10_, analysisse_.MALTMSTMP as MALTMSTMP10_ from PCMANLLN00 analysisse_ where analysisse_.MALDLCD=? and analysisse_.MALANAL=? and analysisse_.MALLINE=?
[12/8/05 14:56:27:766 IST] 45b9f8dc SystemOut O Hibernate: select analysisse_.MALDLCD, analysisse_.MALANAL, analysisse_.MALLINE, analysisse_.MALSTATUS as MALSTATUS10_, analysisse_.MALRLNK as MALRLNK10_, analysisse_.MALSCNID as MALSCNID10_, analysisse_.MALJOB as MALJOB10_, analysisse_.MALUSER as MALUSER10_, analysisse_.MALOPCODE as MALOPCODE10_, analysisse_.MALTMSTMP as MALTMSTMP10_ from PCMANLLN00 analysisse_ where analysisse_.MALDLCD=? and analysisse_.MALANAL=? and analysisse_.MALLINE=?
[12/8/05 14:56:28:826 IST] 45b9f8dc SystemOut O Hibernate: select analysisse_.MALDLCD, analysisse_.MALANAL, analysisse_.MALLINE, analysisse_.MALSTATUS as MALSTATUS10_, analysisse_.MALRLNK as MALRLNK10_, analysisse_.MALSCNID as MALSCNID10_, analysisse_.MALJOB as MALJOB10_, analysisse_.MALUSER as MALUSER10_, analysisse_.MALOPCODE as MALOPCODE10_, analysisse_.MALTMSTMP as MALTMSTMP10_ from PCMANLLN00 analysisse_ where analysisse_.MALDLCD=? and analysisse_.MALANAL=? and analysisse_.MALLINE=?
[12/8/05 14:56:29:871 IST] 45b9f8dc SystemOut O Hibernate: select analysisse_.MALDLCD, analysisse_.MALANAL, analysisse_.MALLINE, analysisse_.MALSTATUS as MALSTATUS10_, analysisse_.MALRLNK as MALRLNK10_, analysisse_.MALSCNID as MALSCNID10_, analysisse_.MALJOB as MALJOB10_, analysisse_.MALUSER as MALUSER10_, analysisse_.MALOPCODE as MALOPCODE10_, analysisse_.MALTMSTMP as MALTMSTMP10_ from PCMANLLN00 analysisse_ where analysisse_.MALDLCD=? and analysisse_.MALANAL=? and analysisse_.MALLINE=?
[12/8/05 14:56:30:978 IST] 45b9f8dc SystemOut O Hibernate: select analysisse_.MALDLCD, analysisse_.MALANAL, analysisse_.MALLINE, analysisse_.MALSTATUS as MALSTATUS10_, analysisse_.MALRLNK as MALRLNK10_, analysisse_.MALSCNID as MALSCNID10_, analysisse_.MALJOB as MALJOB10_, analysisse_.MALUSER as MALUSER10_, analysisse_.MALOPCODE as MALOPCODE10_, analysisse_.MALTMSTMP as MALTMSTMP10_ from PCMANLLN00 analysisse_ where analysisse_.MALDLCD=? and analysisse_.MALANAL=? and analysisse_.MALLINE=?
[12/8/05 14:56:32:038 IST] 45b9f8dc SystemOut O Hibernate: select analysisse_.MALDLCD, analysisse_.MALANAL, analysisse_.MALLINE, analysisse_.MALSTATUS as MALSTATUS10_, analysisse_.MALRLNK as MALRLNK10_, analysisse_.MALSCNID as MALSCNID10_, analysisse_.MALJOB as MALJOB10_, analysisse_.MALUSER as MALUSER10_, analysisse_.MALOPCODE as MALOPCODE10_, analysisse_.MALTMSTMP as MALTMSTMP10_ from PCMANLLN00 analysisse_ where analysisse_.MALDLCD=? and analysisse_.MALANAL=? and analysisse_.MALLINE=?
[12/8/05 14:56:33:176 IST] 45b9f8dc SystemOut O Hibernate: insert into PCMANLHD00 (MAHDESC, MAHEVTCODE, MAHMULTZN, MAHMULTDP, MAHSTATUS, MAHSCNID, MAHRLINKS, MAHCHANGE, MAHTEMPID, MAHTEMPYN, MAHINUSER, MAHPARTIAL, MAHLVDATE, MAHLADATE, MAHCRTUSER, MAHCRTDATE, MAHJOB, MAHUSER, MAHOPCODE, MAHTMSTMP, MAHLSVIEW, MAHDLCD, MAHANAL) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
[12/8/05 14:56:34:096 IST] 45b9f8dc SystemOut O Hibernate: insert into PCMANLLN00 (MALSTATUS, MALRLNK, MALSCNID, MALJOB, MALUSER, MALOPCODE, MALTMSTMP, MALDLCD, MALANAL, MALLINE) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
[12/8/05 14:56:34:377 IST] 45b9f8dc SystemOut O Hibernate: insert into PCMANLLN00 (MALSTATUS, MALRLNK, MALSCNID, MALJOB, MALUSER, MALOPCODE, MALTMSTMP, MALDLCD, MALANAL, MALLINE) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
[12/8/05 14:56:34:377 IST] 45b9f8dc SystemOut O Hibernate: insert into PCMANLLN00 (MALSTATUS, MALRLNK, MALSCNID, MALJOB, MALUSER, MALOPCODE, MALTMSTMP, MALDLCD, MALANAL, MALLINE) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
[12/8/05 14:56:34:377 IST] 45b9f8dc SystemOut O Hibernate: insert into PCMANLLN00 (MALSTATUS, MALRLNK, MALSCNID, MALJOB, MALUSER, MALOPCODE, MALTMSTMP, MALDLCD, MALANAL, MALLINE) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
[12/8/05 14:56:34:377 IST] 45b9f8dc SystemOut O Hibernate: insert into PCMANLLN00 (MALSTATUS, MALRLNK, MALSCNID, MALJOB, MALUSER, MALOPCODE, MALTMSTMP, MALDLCD, MALANAL, MALLINE) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
[12/8/05 14:56:34:377 IST] 45b9f8dc SystemOut O Hibernate: insert into PCMANLLN00 (MALSTATUS, MALRLNK, MALSCNID, MALJOB, MALUSER, MALOPCODE, MALTMSTMP, MALDLCD, MALANAL, MALLINE) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
[12/8/05 14:56:34:377 IST] 45b9f8dc SystemOut O Hibernate: insert into PCMANLLN00 (MALSTATUS, MALRLNK, MALSCNID, MALJOB, MALUSER, MALOPCODE, MALTMSTMP, MALDLCD, MALANAL, MALLINE) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

So hibernate is generating a select AND an insert for each item
of the set. This may be the problem.
Since you use saveOrUpdate and you mapping specifies
cascade="save-update" this is the intended behaviour
of hibernate. To speed up you can try save instead of saveOrUpdate
or replace cascade="save-update" with something else.
This certainly changes a lot and I am not an expert here.