With Spring Transactions with Hibernate, how can you get 2000+ inserts to not slow down in the same transaction?

Question

I have a curious little problem. I have a service that needs to create 2000 records in the database, minimum on various tables. While it would be nice to have them run in the same transaction, the performance gets to be really bad around 400-600 inserts.

For some reason, the inserts go slower and slower. I suppose that Hibernate needs to do some kind of dirty checking... and as the object graphs get more and more complex, it stands to reason that Hibernate has more and more work to do.

This gets really bad because we have one service that basically imports 200 megs of XML data into the database, resulting in about 70,000 inserts. This takes about 1.5 hours to run.

So in Spring, is there a way where I can say, "Keep the hibernate session open, because we want lazy loading and stuff to work... but don't put anything in a transaction?"

Answer 1

You should read the following entry from the Hibernate documentation. When you do things in a single transaction, they are all part of the same Hibernate session. None of your objects are persisted to the database until the session is flushed, so they are all kept around in memory. You need to periodically flush the session and clear it. The documentation gives plenty of details. If you really do not need all the semantics of a Hibernate session, use the stateless session interface.

Also, make sure you are using the JDBC batching parameter. If you do not use it, then Hibernate will write a single row to the database at a time as opposed to writing multiple rows in batches. You may want to experiment with different batch sizes depending on your database and driver.

Keep in mind that Hibernate has a lot of overhead associated with it for object tracking and dirty checking. If the performance with the tweaks does not meet your requirements, you may want to consider using your Hibernate mapping files to generate the raw SQL, but inserting the data using raw JDBC.