CODEWITHSUNDEEP
javahibernatejpastored-proceduresbatch-insert
pranav
pranav

Reputation: 431

Hibernate Batch update vs Stored Procedure

We have some 100,000 records to be inserted into DB. Would it be a good idea to do this in Hibernate Batch inserts? What should be the policy for choosing batch size?

Or else we should call a stored procedure for bulk inserts? Could not get any information on passing a array of objects from Hibernate to DB stored procedure.

Or a mix and match of both would speed up the process. Like calling Stored Proc in batches?

Upvotes: 1

Views: 1465

Answers (1)

Vlad Mihalcea
Vlad Mihalcea

Reputation: 153840

For efficient batch inserts make sure you enable the following properties:

<property name="hibernate.order_updates" value="true"/>
<property name="hibernate.order_inserts" value="true"/>
<property name="hibernate.jdbc.batch_versioned_data" value="true"/>
<property name="hibernate.jdbc.batch_size" value="50"/>

Also make sure you don't use IDENTITY for your entity @Id column. The IDENTITY generator disabled batch processing.

I suggest you trying the enhanced generators, especially the pooled-lo optimizer in case you use a DB that supports sequences (Oracle, PostgreSQL, SQL Server 2008).

You can call stored procedures from Hibernate but it's much more useful for selecting data than for batch inserts.

If the suggested Hibernate batch support is not enough, you should probably give a try to native stored procedures.

Any method you choose you need to be aware of long running transactions. A long running transactions hold locks and the undo/redo database logs might grow and make your transaction even slower.

Upvotes: 2

Related Questions