Hi,


My project's object model persistence is implemented using Hibernate. Now we are developing an import layer on top of the object model to import different kinds of raw data into object model db.

This requires persisting lots of objects ( rows) that may go up to 75 million rows. So I used hibernate batch insert concept (save it to session and flust/commit to db for every 5000 objects). This improved the performance but still not in acceptable limits.

I am using MYSQL as my database and MYSQL performance tuning suggests that multi-row inserts in one single sql statment (bulk insert) is lot faster than multiple single insert statements.

How can I configure bulk insert in Hibernate? I had read couple of posts but not completely clear whether it is supported or not or does Hibernate team has any ideas to support this?

If not this, is there anyway I can improve the performance of inserts using Hibernate as persistence layer?

Here's a couple of ideas for you:

-StatelessSession can be a bit faster than a normal Session, you should give that a shot
-Are you persisting objects which have Hibernate managed relationships? If so, this may be preventing the batching from working properly.
-Are you sure auto-commit is turned off on the db?
-Try flushing every 5k but committing every 50k or 100k.
-Are you using a generated sequence? If so, you will need to use a caching sequence strategy
-Is your app multi-threaded? You will see better performance if you can run multiple threads at once.

What kind of throughput are you seeing?

I also need to create many objects very fast. these objects should be linked to other objects mapped to tables. if the linked entity does not exist, I'm creating a new entity, and then create my main object.
so, I'm trying to use "batch" mode to speed up the process. I'm using this code:


and that's what I have on Hibernate 3.1:


the problem dissapears if I remove

but then every next N rows insertion speed becomes slower and slower:
e.g. first 50 entities are created in 0.2 sec,
then next 50 entities - 0.4 sec,
then 0.7, etc. the time grows up significantly!
I have to create about 2000 objects in one working cycle, so without clear() operation it works VERY (!) slow when the row index becomes more than, say, 500-800.

I'm using session.flush() to use JDBC batch mode effectively, as supposed in one of the tutorials. when ~50 objects are created in JVM, I'm using flush(), so they are written to DB. it works fine, and the problem is with session.clear() at this moment.
I have read about Session.setFlushMode(), but I don't want to change the default mode.

note: without clear() method, everything works fine for me, but slower and slower with each 100 results. and with clear() I get this Exception.
I think I need to find the root problem of that exception.

I know that another option could be using a stateless session, but the documentation says:

so, if I try to work with linked objects, I get another error:



does this mean that I can't use stateless session in my case and should revert back to stateful and fight with that "Found two representations of same collection" error?

Found two representations of same collection: com....SuiteBean.tests
org.hibernate.HibernateException: Found two representations of same collection: com.....SuiteBean.tests
at org.hibernate.engine.Collections.processReachableCollection(Collections.java:153)
at org.hibernate.event.def.FlushVisitor.processCollection(FlushVisitor.java:37)
at org.hibernate.event.def.AbstractVisitor.processValue(AbstractVisitor.java:101)
at org.hibernate.event.def.AbstractVisitor.processValue(AbstractVisitor.java:61)
at org.hibernate.event.def.AbstractVisitor.processEntityPropertyValues(AbstractVisitor.java:55)
at org.hibernate.event.def.DefaultFlushEntityEventListener.onFlushEntity(DefaultFlushEntityEventListener.java:124)
at org.hibernate.event.def.AbstractFlushingEventListener.flushEntities(AbstractFlushingEventListener.java:195)
at org.hibernate.event.def.AbstractFlushingEventListener.flushEverythingToExecutions(AbstractFlushingEventListener.java:76)
at org.hibernate.event.def.DefaultAutoFlushEventListener.onAutoFlush(DefaultAutoFlushEventListener.java:35)
at org.hibernate.impl.SessionImpl.autoFlushIfRequired(SessionImpl.java:954)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1526)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
at org.hibernate.impl.CriteriaImpl.uniqueResult(CriteriaImpl.java:305)


The above exception looks like an exception that should get when querying, not while committing, so this might be raised from DBManager.getTest method.

When the session got cleared using clear() method, it might be requesting db for the collection of tests and found some how some duplicate collections and you were requesting a uniqueResult. I think you need to check your 'getTest' method and the relations.

I hope this helps.

yes, this happens when the call to uniqueResult() is performed, and the cache is automatically flushed.
here's the code for DBManager.getTest():


I don't understand how it can happen - WHY does it find two collections? it looks like a real bug.

back to stateless session - do I have to keep off using it if I need to update collections (assign tests to suites in my case)?

Hi alskar,

Here we are diverting the topic from original question. Can you please create new post for your problem to discuss further?

well, I think these are the typical problems, which you have when you try to perform a quick mass-insert.

Did you try any of my suggestions? We just went through the same thing and those were what we found to help.