How do you enable batch inserts in hibernate?

Question

With hibernate, when I attempt to enable batch inserts with

  <property name="jdbc.batch_size">50</property>

I get the following output:

 [...] cfg.SettingsFactory INFO  - JDBC batch updates for versioned data: disabled
 [...] cfg.SettingsFactory INFO  - Order SQL inserts for batching: disabled

And then this:

 [...] jdbc.AbstractBatcher DEBUG - Executing batch size: 1

never more than batch size: 1 basically.

Am I missing a setting?

score 16 · Answer 1 · edited Aug 01 '23 at 07:09

To enable batching for both INSERT and UPDATE statements, you need to set all the following Hibernate properties:

spring.jpa.properties.hibernate.jdbc.batch_size=30
spring.jpa.properties.hibernate.order_inserts=true
spring.jpa.properties.hibernate.order_updates=true

If you can use a SEQUENCE, then you should not use IDENTITY entity identifier generator, since it disables batch fetching.

If you cannot use a SEQUENCE (e.g. MySQL), then try using a separate mechanism to enable batch inserts (e.g. JDBC) instead of using the TABLE generator which does not scale and has a high-performance penalty.

score 6 · Accepted Answer · edited May 23 '17 at 12:17

6

Turns out what was missing in this case was:

<property name="order_inserts">true</property>

ref: https://forum.hibernate.org/viewtopic.php?p=2374413, https://stackoverflow.com/a/5240930/32453 Or possibly hibernate.order_inserts.

Now I see

 [...] cfg.SettingsFactory INFO  - Order SQL inserts for batching: enabled
 ...
 [...] Executing batch size: 2

Much more frequently (anything greater than 1 basically means it's successfully doing batch inserts).

hibernate.jdbc.batch_versioned_data may also be useful.

jdbc:mysql://localhost:3306/batch?rewriteBatchedStatements=true type connection strings might also be related somehow.

https://forum.hibernate.org/viewtopic.php?p=2374413 and also see Hibernate batch size confusion

edited May 23 '17 at 12:17

Community

1
1

answered Aug 17 '12 at 18:35

rogerdpack

62,887
36
269
388

1

This is not 100% correct: even the batch size of 1 means that Hibernate is using the Batching API of JDBC and, I've tested this, it's **much** faster than the regular API even at size 1. Tested with Oracle and Postgresql. – Marko Topolnik Aug 17 '12 at 18:43
1

Ok I suppose this means that initially for me batch processing was "turned on" but not as effective as it otherwise would be. Good to know. – rogerdpack Aug 17 '12 at 18:46

How do you enable batch inserts in hibernate?

2 Answers2

Linked