Bulk inserts with Spring / Hibernate

Question

Hello,

I'm using in my application a mix Spring/Hibernate (nothing original). For a given feature, I have to import the content of a CSV file into a table of my Oracle DB. For now, I juste create the objects, I do

   HibernateTemplate.saveOrUpdate

on each of them (I need to retrieve their newly allocated Id)

Then the transaction occurs at the end of the method, using the Spring transaction API.

Everything works fine, except performance, which is correct for some 5000's objects, but not for 100 000...

So I look for ideas to accelerate this stuff. I've heard of bulk inserts with Hibernate, but could not find any solid reference. Can anybody give me some ideas to perform this import with greater performance?

Answer 1

You might also consider using StatelessSession as it is designed for bulk operations.

Answer 2

Something simple you might try is to flush and clear the session say every 100 objects...

so execute

session.flush();
session.clear();

every 100 or 1000 inserts.

That will flush and clear the hibernate session and stop it growing too big (possibly why your 100 000 objects are taking so long).

Furthermore if you're using identity identifier generator hibernate will silently turn batch inserts off. Batch inserts will improve performance. You'd also need to specify the hibernate.jdbc.batch_size configuration property equivalent to your 100 at a time number.

Manning's Java Persistence with Hibernate was the source of this (great book - saved my skin numerous times).

Answer 3

Sometimes an ORMapper is not the right hammer for the nail. Especially batch operations are often more performantly executed using plain old JDBC. This of course depends on a variety of conditions but you should at least see this as an option and compare performance of both approaches.

Answer 4

It's not purely a database insert performance issue; if you are creating tens of thousands of objects and not performing a flush, the Hibernate session will grow until you run out of memory.