Why are batch updates so slow?

jbarnum · **Joined:** Wed May 11, 2005 11:57 pm **Posts:** 80

I am fetching about 100 records at one point in my application. At another point in the app, those records are all modified and written back to the database. Currently, this is taking about 98 seconds. I can see each individual update statement scrolling by when I turn on SQL logging.

I tried adding these settings to my hibernate.cfg.xml file:
<property name="hibernate.jdbc.batch_size">100</property>
<property name="hibernate.jdbc.batch_versioned_data">true</property>

(we are using versioned records)

This did speed up the process somewhat - Now all of the SQL updates immediately print, but it still takes about 48 seconds after they print before the operation finishes.

Here is the Hibernate code:

Code:

   public void testBatchUpdate() throws PropertyVetoException {
      Session s = db.getThreadSession();
      Transaction tx = s.beginTransaction();
      try {
         List list = s.createQuery( "SELECT receipt FROM TranscriptionReceipt as receipt" ).list();
         System.out.println( "List size: " + list.size() );
         DebugTimer dt = new DebugTimer( "Updating batch" ); //The following loop takes 23 milliseconds
         for( Iterator it = list.iterator(); it.hasNext(); ) {
            TranscriptionReceipt receipt = (TranscriptionReceipt)it.next();
            Integer randomInt = new Integer( (int)( Math.random() * 100 ) );
            receipt.setTranscriptionistId( randomInt.toString() );
            System.out.println( "Changing transcriptionistId to " + randomInt );
         }
         dt.markTime( "Committing transaction" ); //Committing takes *** 46380ms ***
         tx.commit();
         dt.markTime( "Closing session" ); //Closing the session takes 2ms
         s.close();
         dt.stop();
      } finally {
         s.close();
      }
   }

Doing the same operation with raw JDBC and prepared statements takes about 1.4 seconds.

Here is the raw JDBC code:

Code:

   public void testRawJdbcBatchUpdate() throws ClassNotFoundException, SQLException {
      Class.forName( "com.mysql.jdbc.Driver" );
      Connection connection = DriverManager.getConnection( "jdbc:mysql://myServer/myDb", "username", "password" );
      DebugTimer dt = new DebugTimer( "Fetching results" ); //The fetch takes 496ms
      ResultSet rs = connection.createStatement().executeQuery( "SELECT * FROM TranscriptionReceipt" );
      Collection ids = new LinkedList();
      while( rs.next() ) {
         ids.add( rs.getObject("id") );
      }
      rs.close();

      dt.markTime( "Creating prepared statement and all batches" ); //Creating all the batches takes 475ms
      PreparedStatement ps = connection.prepareStatement( "UPDATE TranscriptionReceipt SET transcriptionistId=? WHERE ID=?" );
      for( Iterator it = ids.iterator(); it.hasNext(); ) {
         Integer randomInt = new Integer( (int)( Math.random() * 100 ) );
         ps.setObject( 1, randomInt );
         System.out.println( "Changing transcriptionistId to " + randomInt );
         ps.setObject( 2, it.next() );
         if( it.hasNext() ) ps.addBatch();
      }
      
      dt.markTime( "Executing batches" ); //Executing the prepared statement takes 487ms
      ps.executeUpdate();
      dt.stop();
   }

Hibernate version:
I am running Hibernate 3.05. We cannot upgrade to a more recent version because of patches that we've made to the code, and because the project is near completion.

Mapping documents:

Code:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping default-access="field" package="com.prosc.portalvideo.authmodel">
   <class lazy="false" name="TranscriptionReceipt">
      <id name="id" type="java.lang.Long" unsaved-value="null">
         <generator class="native"/>
      </id>
      <version name="version"/>
      <property name="dateTranscriptionCompleted" type="java.util.Date"/>
      <property name="transcriptionistId" type="java.lang.String"/>
      <property name="qualityAssuranceId" type="java.lang.String"/>
      <property name="qtUrl" type="java.lang.String"/>
      <property name="numberOfCharacters" type="java.lang.Integer"/>
      <property name="dateAvailableForTranscription" type="java.util.Date"/>
      <many-to-one column="centralMediaFileId" name="centralMediaFile" not-null="false"/>
      <many-to-one column="transcriptionVendorId" name="transcriptionVendor" not-null="true"/>
   </class>
</hibernate-mapping>

Name and version of the database you are using:
MySQL 5

msj4u · **Joined:** Wed Apr 25, 2007 11:44 pm **Posts:** 59

dude try to replace this line

Code:

with this

Code:

and then compare the differences

jbarnum · **Joined:** Wed May 11, 2005 11:57 pm **Posts:** 80

That helped slightly (43 seconds instead of 48), but that might just be because I'm testing it from a different, slightly faster internet connection than my original test. I don't think that it's fundamentally changing the way Hibernate is doing the batch update.

I forgot to mention in my original post that the application is not running on the same LAN as the MySQL database, which is why it's so critical to optimize the JDBC operations.

christian · **Posted:** Fri Jun 22, 2007 11:13 am

Use a StatelessSession to retrieve the objects, they are then considered detached. Now call session.update(o) on each to reattach it to a new regular Session. That should eliminate the dirty checking of Hibernate of each instance against the stored snapshot. What I don't believe is that this is responsible for 40 seconds difference. I recommend using a profiler first to find out what is really taking time. You can also consider logging SQL at the JDBC level with a tool like this that wraps the driver: http://www.irongrid.com/

jbarnum · **Joined:** Wed May 11, 2005 11:57 pm **Posts:** 80

Regarding the StatelessSession suggestion, I can't do that because in the real application, these are long-lived objects which are used extensively and relate with many other peristent objects.

However, I don't think that it's dirty checking that's causing the slowdown, because I tried the exact same Hibernate test with a local MySQL database and it was very fast. The problem comes when interacting with a remote database which is not on the LAN.

Also, when I do the raw JDBC test and call executeUpdate() on the PreparedStatement for each object (instead of calling addBatch() with a single call to executeUpdate() at the end ), it is very slow, comparable to Hibernate, which reinforces my hypothesis that it's nothing to do with any local processing that Hibernate is doing.

Therefore, I'm guessing that Hibernate is making individual separate calls to PreparedStatement.executeUpdate() instead of addBatch(). Is that guess correct? If not, do I need to configure something to enable the addBatch() feature in Hibernate?

christian · **Posted:** Fri Jun 22, 2007 1:13 pm

Enable DEBUG logging in Hibernate. I thought you did this already.