using Hibernate to loading 20K products, modifying the entity and updating to db

Question

I am using hibernate to update 20K products in my database.

As of now I am pulling in the 20K products, looping through them and modifying some properties and then updating the database.

so:

load products

foreach products
   session begintransaction
   productDao.MakePersistant(p);
   session commit();

As of now things are pretty slow compared to your standard jdbc, what can I do to speed things up?

I am sure I am doing something wrong here.

Answer 1

The right place to look at in the documentation for this kind of treatment is the whole Chapter 13. Batch processing.

Here, there are several obvious mistakes in your current approach:

• you should not start/commit the transaction for each update.

• you should enable JDBC batching and set it to a reasonable number (10-50):

  hibernate.jdbc.batch_size 20
  

• you should flush() and then clear() the session at regular intervals (every n records where n is equal to the hibernate.jdbc.batch_size parameter) or it will keep growing and may explode (with an OutOfMemoryException) at some point.

Below, the example given in the section 13.2. Batch updates illustrating this:

Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();

ScrollableResults customers = session.getNamedQuery("GetCustomers")
    .setCacheMode(CacheMode.IGNORE)
    .scroll(ScrollMode.FORWARD_ONLY);
int count=0;
while ( customers.next() ) {
    Customer customer = (Customer) customers.get(0);
    customer.updateStuff(...);
    if ( ++count % 20 == 0 ) {
        //flush a batch of updates and release memory:
        session.flush();
        session.clear();
    }
}

tx.commit();
session.close();

You may also consider using the StatelessSession.

Another option would be to use DML-style operations (in HQL!): UPDATE FROM? EntityName (WHERE where_conditions)?. This the HQL UPDATE example:

Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();

String hqlUpdate = "update Customer c set c.name = :newName where c.name = :oldName";
// or String hqlUpdate = "update Customer set name = :newName where name = :oldName";
int updatedEntities = s.createQuery( hqlUpdate )
        .setString( "newName", newName )
        .setString( "oldName", oldName )
        .executeUpdate();
tx.commit();
session.close();

Again, refer to the documentation for the details (especially how to deal with the version or timestamp property values using the VERSIONED keyword).