Using Hibernate to download 20K products, modify an object, and upgrade to db

Question

I use hibernate to update 20K products in my database.

I am currently working on 20K products, looking at them and changing some properties, and then updating the database.

So:

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

At the moment, everything is pretty slow compared to your standard jdbc, what can I do to speed things up?

I am sure that I am doing something wrong.

Answer 1

The correct place in the documentation for this type of treatment is Chapter 13. Batch processing .

There are some obvious errors in your current approach:

• You should not start / commit a transaction for each update.

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

   hibernate.jdbc.batch_size 20 

• you must flush() and then clear() session at regular intervals (each n entries, where n is equal to the hibernate.jdbc.batch_size parameter), or it will continue to grow and may explode (with OutOfMemoryException ) at some point.

The following is an example in 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 StatelessSession .

Another option is to use DML-style operations (in HQL!): UPDATE FROM? EntityName (WHERE where_conditions)? UPDATE FROM? EntityName (WHERE where_conditions)? . This is an 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 details (especially about how to handle version or timestamp property values ​​using the VERSIONED keyword).

Answer 2

If this is pseudo-code, I would recommend moving the transaction outside the loop, or at least have a double loop if all 20K products in one transaction are too many:

 load products foreach (batch) { try { session beginTransaction() foreach (product in batch) { product.saveOrUpdate() } session commit() } catch (Exception e) { e.printStackTrace() session.rollback() } } 

In addition, I would recommend that you use the UPDATE package instead of sending each one to the database. There is too much network traffic. Tie each piece in one batch and send them immediately.

Answer 3

I agree with the answer above about viewing the chapter on batch processing.

I also wanted to add that you should make sure that you download only what is needed for the changes you need to make for the product.

What do I mean, if a product eagerly loads a large number of other objects that are not important for this transaction, you should not load the combined objects - this will speed up the loading of products and, depending on their sustainability strategy, can also save your time when repeating again product.

Answer 4

The fastest way to do a batch update is to convert it to a single SQL statement and execute it as raw sql in a session. Something like

 update TABLE set (x=y) where w=z; 

Otherwise, you can try to make fewer transactions and make updates in batches:

 start session start transaction products = session.getNamedQuery("GetProducs") .setCacheMode(CacheMode.IGNORE) .scroll(ScrollMode.FORWARD_ONLY); count=0; foreach product update product if ( ++count % 20 == 0 ) { session.flush(); session.clear(); } } commit transaction close session 

See the Hibernate Community Docs for more information.