performance issue with bulk update

prfnsh · **Joined:** Wed Mar 01, 2006 3:45 pm **Posts:** 9

I'm doing some data processing using Hibernate. Following is the basic logic:

1. the data to be processed are read from a file in one shot
2. select pojos from database as data are being processing
3. basic on the data, the pojos may be updated or created
4. all the pojos are kept in memory and in the end, I do following:

Code:

      Iterator modified_iter = modifiedItems.iterator();
      while(modified_iter.hasNext()) {
          session.saveOrUpdate((Items) modified_iter.next());

The reason why I use "saveOrUpdate" is because the items are either updated or created (transient). I actually used a helper to wrap the "session" object. but to be clear, I'm using "session" here.

In a single transaction, the number of items will be 1k-2k. I turned on "show_sql" option and found that the "insert" operations are very fast but the "update" statements took unbelievably long time - about 15min for about 1k update statements. The "update" statements were only fired at the commit(flush) time.

Hibernate version: 3

Name and version of the database you are using: sqlserver 2000 with WebSphere embedded ConnectJDBC driver for MS SQL Server (XA)

Mapping documents:

Code:

      <property name="hibernate.transaction.factory_class">
         org.hibernate.transaction.JTATransactionFactory
      </property>
      <property name="hibernate.connection.pool_size">25</property>
      <property name="hibernate.connection.isolation">1</property> 
      <property name="hibernate.prepare_sql">true</property>
      <property name="hibernate.statement_cache.size">10000</property>
      <property name="transaction.manager_lookup_class">         org.hibernate.transaction.WebSphereTransactionManagerLookup
      </property> 
      <property name="hibernate.jdbc.batch_size">1000</property>
      <property name="hibernate.cache.use_second_level_cache">false</property>

Strangely, I tried to reproduce the problem on a different db server and appserver (exactly the same configuration, except that they are internal instances while the problematic instances were on the customer's network) and didn't see the problem. I'm wondering if anybody has seen this before. Or could sb. give me some pointer as to how I should diagnose this problem?

Thanks a lot.

kgignatyev · **Posted:** Wed Mar 01, 2006 7:39 pm

Could be table size and index presence related. To update a record the server has to locate it first, ant the time to get the record depends on availability of an index to speed up access and table size. Unfortunate tablespace organization could also contribute, transaction isolation level and activity of other processes in the same database.

prfnsh · **Joined:** Wed Mar 01, 2006 3:45 pm **Posts:** 9

thanks for the tip.

But the Hibernate generated sql is like:

Code:

update mytable
set
col1 = ?,
col2 = ?,
...
coln = ?
where
table_id = ?

so it's using the pk index of the table. I verified that by looking at the execution plan...

i changed the update batch size to 20. in the log, i saw 20 update statements per shot so the flushing is working. but every flush holds about 20 seconds before the next flush...

i'll pursue further into the db so see if it's a db problem. but are there other factors in Hibernate that may affect the performance of the db round trip?

thanks a lot.

kgignatyev · **Posted:** Thu Mar 02, 2006 1:28 am

It is perfect SQL, I do not think H is guilty, I would rather try to find problems on server side or JDBC driver, something about prepared statements handling perhaps