Batch with INSERT VALUES when using Hibernate


#1

Is there any version of Hibernate that supports bulk load batching using INSERT VALUES instead?

Since Hibernate 3 has anything been done to improve batching performance with Oracle?


#2

Batching is one thing, bulk operations is another.

Hibernate supports batch INSERT, UPDATE or DELETE statements.

As for Bulk Insert, only the INSERT INTO FROM:

insert into TargetEntity(prop1, prop2) 
select prop1, prop2 
from SourceEntity
where ...

However, you are never limited to Hibernate HQL or JPQL. You can always use native SQL if you need. Otherwise, why do you think both JPA and Hibernate allow you to run native SQL queries?


#3

Sure, but it sounds more complex to manage the foreign keys when you have relationships in the batch as well.


#4

That’s why you need to use Batch Inserts. Check out this article for more details.


#5

Sure however this approach has been proven to be more performant


#6

The author on that blog tries to emulate what MySQL already offers via rewriteBatchedStatements connection configuration property.

As for that being “proven” to be more performant, that’s far from the truth.

Never trust a benchmark that runs 10 iterations and derives the results from that. You should do warming up with thousands of iterations and let it run for minutes to be sure which one is really faster.

More, batching can benefit from execution plan reuse on same RDBMS, this method does not. Especially on Oracle or SQL Server.


#7

Yes I would agree there’s a hard parse taking place for each statement and that’s always been a concern and generally is poor practice.

I already own your book.

With hibernate how many records should you be able insert into an Oracle RAC. Assume the table has say 100 columns what kind of benchmark should I be expecting?

I you use primary keys generated by sequences using HILO what value should you set your sequence cache to?

We have triggers that fire that maintain audit columns so that adds some overhead no doubt.

Another article on the same topic


#8

Pooled and pooled-lo are proffered to the legacy HILO.

As for the your question, Hibernate will handle just as many inserts as Oracle and the JDBC driver can handle.