Hibernate - how to verify if batch insert is really performed

Question

Technology stack: Oracle database 11.2.0.2, Java 1.6, Hibernate 3.6.6.Final.

I am new to hibernate, apologize if this is trivial.

The following code was supposed to put some optimization:

Transaction tx = session.beginTransaction();
for (int i = 0; i < 10; i++) {
   POJO pojo = new POJO(i);
   session.save(pojo);
}
tx.commit();

hibernate.cfg.xml has the following entry

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

How can I verify, if hibernate really batches all these inserts? If it performs 10 inserts than there is no gain. One idea is to put the jdbc plain query right after save() that checks if the record was added to db:

String query = "retrieve previously added element"
PreparedStatement stmt = session.connection().prepareStatement(query.toString());
Result rs = statement.executeQuery();
/** check contents of rs */

In my case it returns a non empty set with previously added element. Does it mean anything? How else can I check if batching works.

thanks in advance

Laurentium

add the transactional method code after refactor – Semifinal

I am afraid I don't understand. – Adamski

Answer 1

You need to add "BatchingBatch" logger to your logging provider.

org.hibernate.engine.jdbc.batch.internal.BatchingBatch

Than you will be able to see in the logs something like:

2018-02-20 17:33:41.279 DEBUG 6280 --- [           main] o.h.e.jdbc.batch.internal.BatchingBatch  : Executing batch size: 19

Unless you see this message,batching is not working.

Tested with Hibernate Version:5.2.12

Dermoid

@Frans - I was on hibernate 3.6.6 – Adamski

Answer 2

To check what is actually flushed to the database then configure your logging properties as follows:

log4j.rootLogger=info, stdout
# basic log level for all messages
log4j.logger.org.hibernate=debug

# SQL statements and parameters
log4j.logger.org.hibernate.SQL=debug
log4j.logger.org.hibernate.type.descriptor.sql=trace

and add this to your hibernate.cfg.xml

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

Then you can see what is actually sent to the db..

With batch you should have output like:

insert into Pojo (id , titel) values (1, 'val1') , (2, 'val2') ,(3, 'val3')

Additionally here is a good post that has some tips about how to most efficiently take advantage of the batch processing: article

For example you might consider flushing after every ${jdbc.batch_size} saves.

Transaction tx = session.beginTransaction();
for ( int i=0; i<100000; i++ ) {
    Customer customer = new Customer(.....);
    Cart cart = new Cart(...);
    customer.setCart(cart) // note we are adding the cart to the customer, so this object 
     // needs to be persisted as well
    session.save(customer);
    if ( i % 20 == 0 ) { //20, same as the JDBC batch size
        //flush a batch of inserts and release memory:
        session.flush();
        session.clear();
    }
}
tx.commit();

Semifinal

Thanks. Unfortunately I am still seing single insert statements. – Adamski

do you flush after ${jdbc.batch_size} saves? – Semifinal

The INSERT statement will only be rewritten into a big bulk INSERT if your JDBC driver supports that (I know MySQL can do this). That's a separate concern from batching inserts, however. You can see single inserts in the log but they can still be sent as a batch to the server. – Grandpa

For MYSQL also (Version 5.x), it shows single inserts in the log but in stats log, it displays the (n) batch statements executed – Turnkey

@Turnkey Will it show only single insert statement list in MYSQL processlist also? Or it should show execution of completer batch? – Abyss

Answer 3

In spring boot application.yml I've added the rows below to see if batching is working

logging:
  level:
    org.hibernate:
      engine.jdbc.batch.internal.BatchingBatch: DEBUG

As result I see such rows in logs

org.jboss.logging.DelegatingBasicLogger: Executing batch size: 30

Panayiotis

Answer 4

In case of Hibernate 6 you need to set org.hibernate.orm.jdbc.batch logger to trace level. (I tested it with Hibernate 6.5.0.Final.)

Logback configuration example:

<logger name="org.hibernate.orm.jdbc.batch" level="trace"/>

Sample log output:

2024-05-05 23:00:25 TRACE org.hibernate.orm.jdbc.batch - Executing JDBC batch (3 / 10) - `TestEntity#INSERT`

The log entry is written by the method: org.hibernate.engine.jdbc.batch.internal.BatchImpl#performExecution

I haven't found this info in the Hibernate ORM user guide.

Subsidiary