Hi,

I'am looking for a fast solution to insert many incoming data-pakets in a MySQL database.

I'am using Hibernate 3.1.3 and MySQL 5.0.18.

The destination table contains 5 columns with type DOUBLE and a ID column BIGINT with auto-increment.
In my application I use Hibernate for the other database operations, so I like to use it for this problem too.
Now I search for the optimal solution.

I'd tried the following versions:

I generate a list with 20.000 ojects to store in the database.

Solution1: Hibernate Batch-Insert

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

for ( int i=0; i<list.size(); i++ )
{
Message message = (Message) list.get(i);
session.save(message);

if ( i % 30 == 0 ) //30 auch als JDBC batch size eingestellt
{
session.flush();
session.clear();
}
}
tx.commit();
session.close();

Solution2: Hibernate Stateless Session

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

for ( int i=0; i<list.size(); i++ )
{
Message message = (Message) list.get(i);
session.save(message);
}
tx.commit();
session.close();

I also tried the inserts with JDBC, with standard inserts and with prepared SQL. Additionally I tried a batch with iBATIS.

For the 20.000 objects i get the following durations:

System 1: P4-D with 1GB RAM

Hibernate Batch-Insert: 11.4s
Hibernate Stateless: 8.8s
JDBC Standard: 4.9s
JDBC Prepared SQL: 3.7s
iBATIS: 4.6s

System 2: Dual-Xeon with 4GB RAM

Hibernate Batch-Insert: 8.3s
Hibernate Stateless: 4,4s
JDBC Standard: 4.6s
JDBC Prepared SQL: 2.2s
iBATIS: 2.3s

Does anyone knows a faster solution using Hibernate?

Thanks,
Carsten

Hi Carsten

I think that it is safe to say that Hibernate was not designed / meant for bulk inserts.

In my project we had a process where we needed to insert around 2 million rows in different tables as fast as possible, this was not really possible to do with Hibernate. For one it was too slow and two it used lots of memory.

We have decided to use direct JDBC inserts (prepared and batched ofcause), wich performs much better.

This was before Hibernate 3 (hibernate 2.1 i think), i have not testet performance on Hibernate 3.

I am pretty sure that the Hibernate team didnt recommend using Hibernate for processing large amounts of data prior to version 3. It looks like they changed their view on that in v 3 http://www.hibernate.org/hib_docs/v3/re ... batch.html

But I would stay with direct JDBC for large amounts of inserts, since this will be faster and save you from a lot of headache with memory problems.

Regards
Timm

I would like to know this also.
we need to insert data into several tables very fast, and I'm wondering if using StatelessSession is the best we can get with Hibernate.
unfortunately, the documentation does not comment on this - what is the best way to perform mass insert.

The example provided in section 13.1 of the documentation:


does not work when need to create a group of records, which should be LINKED to other tables (typical "failed to lazily initialize a collection of role" error happens, if flushing and clearing the session in the middle of process). I already complained about this on the forum.

I found a way to improve the performance of hibernate batch insert , almost the same performance as SQL/JDBC.

Why hibernate insert batch seems very slow?
Hibernate will generate two statement when you use sequence generator. Like:
select TABLE_SEQ.nextval from dual;
insert into TABLE(SID,name) value(?,?);

select TABLE_SEQ.nextval from dual;
insert into TABLE(SID,name) value(?,?);

......
......

I found that it's the main reason for low performance.

And, the hint we got from hibernate ref said:"Note that Hibernate disables insert batching at the JDBC level transparently if you use an identiy identifier generator. ".

so, change your generator class may improve the performance extremely .

<generator class="seqhilo"> <param name="sequence">M_CONT_DETAIL_SEQ</param> <param name="max_lo">100</param>
</generator>


this will cause hibernate generate 100 SID with the same one sequence.

so, the sql will be

select TABLE_SEQ.nextval from dual;
select TABLE_SEQ.nextval from dual;

insert into TABLE(SID,name) value(?,?);
insert into TABLE(SID,name) value(?,?);
insert into TABLE(SID,name) value(?,?);
insert into TABLE(SID,name) value(?,?);
insert into TABLE(SID,name) value(?,?);
insert into TABLE(SID,name) value(?,?);
insert into TABLE(SID,name) value(?,?);

hello Carsten,

I am also looking at faster options for inserting into a table under heavy load.

I 'm using Hibernate 3.3.1.GA and PostgreSQL, version: 8.3.7. So almost same techonolgies and requirements.

Your findings were really helpful to me to start with. I am curious to know what was your final conclusion. Which option did u finally opt for?

Please do reply as it will correct my understandings.

Thanks.
Shaguf