TechQA.

Hibernate Batch Insert slows down other inserts afterwards

267 views Asked by At

I am using Hibernate 5.2.5.Final and the following code for a batch insert of a few thousand bookings where the budget of each booking is the same and already existed in the DB before the insert:

public List<Long> batchInsertBookings(List<Booking> bookings, String resource) {
  List<Long> generatedIds = new ArrayList<>();
  router.setDataSource(resource);

  try (StatelessSession statelessSession = em.unwrap(Session.class).getSessionFactory().openStatelessSession()) {
    int batchSize = 20;
    int numberOfEntities = bookings.size();
    UserTransaction userTransaction = (UserTransaction) new InitialContext().lookup("java:comp/UserTransaction");
    userTransaction.begin();

    for (int i = 0; i < numberOfEntities; i += batchSize) {
      int endIndex = Math.min(i + batchSize, numberOfEntities);
      List<Booking> batch = bookings.subList(i, endIndex);

      // Save the batch data
      for (Booking entity : batch) {
        statelessSession.insert(entity);
      }

      // Retrieve the generated IDs after flush
      List<Long> batchIds = batch.stream()
              .map(IObject::getId)
              .collect(Collectors.toList());
      generatedIds.addAll(batchIds);
    }
      userTransaction.commit();
    } catch (Exception e) {
      Log.debug(e.getMessage(), getClass().getName());
    }
    return generatedIds;
  }

The insert-method is called in a new thread since the batch insert should happen in the background. The batch insert finishes after a reasonable amount of time and the bookings appear in the database. Other than that no further log entries regard the batch insert. After the batch insert one can still use the backend and the fetch operations for data are still comparably fast to before the insert. Although now, if I insert another entity (f.e. with an elementcollection or associations) the insert does not really finish. This regards the main thread as well as the new thread I created for the batch insert. An example for an insert I perform after the batch insert is the insert of a ChangeLog-entity with an element collection diff of strings, where there is a string for each of the thousands of bookings (which worked really fast before). Here the logs are as follows:

2023-07-26 14:51:49 [DEBUG] org.hibernate.SQL insert into diff_REV (REVTYPE, REVEND, REVEND_TSTMP, REV, changeLog_id, diff) values (?, ?, ?, ?, ?, ?)
2023-07-26 14:51:49 [DEBUG] o.h.e.j.b.i.BatchingBatch Executing batch size: 1
2023-07-26 14:51:49 [DEBUG] org.hibernate.SQL select diff_rev0_.REV as REV1_57_, diff_rev0_.changeLog_id as changeL2_57_, diff_rev0_.diff as diff3_57_, diff_rev0_.REVTYPE as REVTYPE4_57_, diff_rev0_.REVEND as REVEND5_57_, diff_rev0_.REVEND_TSTMP as REVEND_T6_57_ from diff_REV diff_rev0_ where diff_rev0_.changeLog_id=? and diff_rev0_.diff=? and (diff_rev0_.REVEND is null) for update
2023-07-26 14:51:50 [DEBUG] org.hibernate.SQL insert into diff_REV (REVTYPE, REVEND, REVEND_TSTMP, REV, changeLog_id, diff) values (?, ?, ?, ?, ?, ?)
2023-07-26 14:51:50 [DEBUG] o.h.e.j.b.i.BatchingBatch Executing batch size: 1
2023-07-26 14:51:50 [DEBUG] org.hibernate.SQL select diff_rev0_.REV as REV1_57_, diff_rev0_.changeLog_id as changeL2_57_, diff_rev0_.diff as diff3_57_, diff_rev0_.REVTYPE as REVTYPE4_57_, diff_rev0_.REVEND as REVEND5_57_, diff_rev0_.REVEND_TSTMP as REVEND_T6_57_ from diff_REV diff_rev0_ where diff_rev0_.changeLog_id=? and diff_rev0_.diff=? and (diff_rev0_.REVEND is null) for update
2023-07-26 14:51:51 [DEBUG] org.hibernate.SQL insert into diff_REV (REVTYPE, REVEND, REVEND_TSTMP, REV, changeLog_id, diff) values (?, ?, ?, ?, ?, ?)
2023-07-26 14:51:51 [DEBUG] o.h.e.j.b.i.BatchingBatch Executing batch size: 1
2023-07-26 14:51:51 [DEBUG] org.hibernate.SQL select diff_rev0_.REV as REV1_57_, diff_rev0_.changeLog_id as changeL2_57_, diff_rev0_.diff as diff3_57_, diff_rev0_.REVTYPE as REVTYPE4_57_, diff_rev0_.REVEND as REVEND5_57_, diff_rev0_.REVEND_TSTMP as REVEND_T6_57_ from diff_REV diff_rev0_ where diff_rev0_.changeLog_id=? and diff_rev0_.diff=? and (diff_rev0_.REVEND is null) for update

where we can see that for each string element in the element collection it takes 1 second to persist, which is definetly not normal, since if I dont do the batch insert and still persist such an entity it persists the full entity in a short time.

It is worth noticing that my Booking entity is associated to a lot of other enties (where for example Account has a good amount of other associations). The most nested are:

1 Account   (account)      <->  (mainBudget) Budget 0..1
0..1 Budget (parentBudget) <->  (subBudget) Budget *
1 Budget (budget) <-> (bookings) Booking *

An example for a OneToMany- and a ManyToOne-mapping for the associations looks like this:

In Booking.java:

@ManyToOne(fetch=FetchType.LAZY, optional= false)
@JoinColumn(name="budget_id")
@BatchSize(size = 50)
private Budget budget;

In Budget.java:

@OneToMany(targetEntity = Booking.class, mappedBy = "budget")
@Fetch(value = FetchMode.SUBSELECT)
private List<Booking> booking;

What I noticed is that if I batch insert an entity which has no associations the insert after the batch inserts seem to work. I don't understand why this problem occurs and neither in the performance nor in the query logs I can find why. The query log only shows the queries which are to expect - i.e. batch inserts for the bookings and then after that the ChangeLog inserting as shown above.

What I tried so far:

  1. I added the @BatchSize-annotations to all associations since it should be a solution for the N+1 queries problem if it occurs in my case.
  2. I added connection pooling (as can be seen in my persistence.xml below)
  3. I used different methods to batch insert. The one I posted here is using a stateless sessions. I also tried using mehtod with a normal session with a custom transaction and another one with @Transactional and the EntityManager doing all the session work and getting flushed and cleared, or just flushed, after each batch.
  4. I rebuilt the booking entity without the nested associations above (with just a simple one), which is how I found out, that it works faster then.
  5. I fully fetched the bookings before inserting them (in case lazy loading is the issue), although I did not unproxy the accounts, since they are always subclasses and there is some problem with unproxying them and then reassigning them to the entity. It is also notable that if I don't load the level 1 associations for the booking before the insert I get a LazyInitializationException.
  6. I commented out the ChangeLog persisting and let the new thread finish, although later the same problem with inserting also occurs in the main thread of the application.
  7. I did everything without the ConnectionPooling at first, neither way it worked.
  8. I removed all Cascading, but it should and did not matter since the budget entity for the booking already existed in the database before the insert.

My persistence.xml for batch inserting and connection pooling looks like this:

  <property name="hibernate.connection.url" value="jdbc:mysql://localhost:3306/testdb"/>
  <property name="hibernate.connection.url" value="jdbc:mysql://localhost:3306/testdb?rewriteBatchedStatements=true"/>
  <property name="hibernate.connection.username" value="admin"/>
  <property name="hibernate.connection.password" value="pass"/>
  <property name="hibernate.jdbc.batch_size" value="20"/>
  <property name="hibernate.order_inserts" value="true"/>
  <property name="hibernate.order_updates" value="true"/>
  <property name="hibernate.jdbc.batch_versioned_data" value="true" />
  <property name="hibernate.generate_statistics" value="true"/>
  <property name="hibernate.default_batch_fetch_size" value="20"/>
  <property name="hibernate.connection.provider_class" value="com.zaxxer.hikari.hibernate.HikariConnectionProvider"/>
  <property name="hibernate.hikari.dataSourceClassName" value="com.mysql.cj.jdbc.MysqlDataSource"/>
  <property name="hibernate.hikari.dataSource.url" value="jdbc:mysql://localhost:3306/testdb"/>
  <property name="hibernate.hikari.dataSource.user" value="admin"/>
  <property name="hibernate.hikari.dataSource.password" value="pass"/>
  <property name="hibernate.hikari.maximumPoolSize" value="10"/>

and I log the following (if you have better ways of logging more relevant data please also let me know):

  <logger name="org.hibernate.SQL" level="DEBUG" />
  <logger name="org.hibernate.engine.jdbc.batch.internal.BatchingBatch" level="DEBUG" />
  <logger name="org.hibernate.engine.internal.StatisticalLoggingSessionEventListener" level="info"/>

Why could this happen?

Information for answer from @Rick James:

Those are the SQL Queries for one batch of bookings.

2023-07-27 11:24:05 [DEBUG] org.hibernate.SQL select tbl.next_val from hibernate_sequences tbl where tbl.sequence_name=? for update
2023-07-27 11:24:05 [DEBUG] org.hibernate.SQL update hibernate_sequences set next_val=?  where next_val=? and sequence_name=?
2023-07-27 11:24:05 [DEBUG] org.hibernate.SQL insert into Booking (changeDate, moneyValue, text, budget_id, date, isActive, dateOfCompensation, reason_id, type, receiptDate, area, originalMoneyValue, dateOfBooking, manualChanges, recorder, dateOfRecording, yearOfBusiness, targetDiscount, costType, creditor, project, reference, discount, status, tax, stkz, cancelationNumber, paymentReason, DTYPE, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'PlainBooking', ?)
2023-07-27 11:24:05 [DEBUG] org.hibernate.SQL select tbl.next_val from hibernate_sequences tbl where tbl.sequence_name=? for updatePlain
2023-07-27 11:24:05 [DEBUG] org.hibernate.SQL update hibernate_sequences set next_val=?  where next_val=? and sequence_name=?Plain
2023-07-27 11:24:05 [DEBUG] org.hibernate.SQL insert into Booking (changeDate, moneyValue, text, budget_id, date, isActive, dateOfCompensation, reason_id, type, receiptDate, area, originalMoneyValue, dateOfBooking, manualChanges, recorder, dateOfRecording, yearOfBusiness, targetDiscount, costType, creditor, project, reference, discount, status, tax, stkz, cancelationNumber, paymentReason, DTYPE, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'PlainBooking', ?)

... 17*3 more of the same rows here (i.e. one batch of 20 selects, updates and inserts)

2023-07-27 11:24:05 [DEBUG] org.hibernate.SQL select tbl.next_val from hibernate_sequences tbl where tbl.sequence_name=? for updatePlain
2023-07-27 11:24:05 [DEBUG] org.hibernate.SQL update hibernate_sequences set next_val=?  where next_val=? and sequence_name=?Plain
2023-07-27 11:24:05 [DEBUG] org.hibernate.SQL insert into Booking (changeDate, moneyValue, text, budget_id, date, isActive, dateOfCompensation, reason_id, type, receiptDate, area, originalMoneyValue, dateOfBooking, manualChanges, recorder, dateOfRecording, yearOfBusiness, targetDiscount, costType, creditor, project, reference, discount, status, tax, stkz, cancelationNumber, paymentReason, DTYPE, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'PlainBooking', ?)
2023-07-27 11:24:05 [DEBUG] o.h.e.j.b.i.BatchingBatch Executing batch size: 20

The SHOW CREATE TABLE Booking statement gives the following output. It is to note here, that there are different types of bookings (depending on the DTYPE) with different type of attributes. I renamed all the irrelevant attributes to someAttributeX. I.e. the entities that I am trying to persist which are referred to here as PlainBooking do not have these attributes.

'Booking', 'CREATE TABLE `Booking` (
`DTYPE` varchar(31) NOT NULL,
`id` bigint NOT NULL,
`changeDate` datetime DEFAULT NULL,
`moneyValue` bigint NOT NULL,
`text` varchar(255) DEFAULT NULL,
`date` datetime DEFAULT NULL,
`isActive` bit(1) NOT NULL,
`debitor` varchar(255) DEFAULT NULL,
`someOtherAttribute1` bigint DEFAULT NULL,
`someOtherAttribute2` bigint DEFAULT NULL,
`someOtherAttribute3` bigint DEFAULT NULL,
`project` varchar(255) DEFAULT NULL,
`someOtherAttribute4` datetime DEFAULT NULL,
`someOtherAttribute5` varchar(255) DEFAULT NULL,
`status` int DEFAULT NULL,
`someOtherAttribute6` datetime DEFAULT NULL,
`dateOfCompensation` datetime DEFAULT NULL,
`type` varchar(255) DEFAULT NULL,
`receiptDate` datetime DEFAULT NULL,
`area` varchar(255) DEFAULT NULL,
`originalMoneyValue` bigint DEFAULT NULL,
`dateOfBooking` datetime DEFAULT NULL,
`manualChanges` bit(1) DEFAULT NULL,
`recorder` varchar(255) DEFAULT NULL,
`dateOfRecording` datetime DEFAULT NULL,
`yearOfBusiness` datetime DEFAULT NULL,
`targetDiscount` bigint DEFAULT NULL,
`costType` varchar(255) DEFAULT NULL,
`creditor` varchar(255) DEFAULT NULL,
`reference` varchar(255) DEFAULT NULL,
`discount` bigint DEFAULT NULL,
`tax` bigint DEFAULT NULL,
`stkz` varchar(255) DEFAULT NULL,
`cancelationNumber` varchar(255) DEFAULT NULL,
`paymentReason` varchar(255) DEFAULT NULL,
`someOtherAttribute7` datetime DEFAULT NULL,
`someOtherAttribute8` varchar(255) DEFAULT NULL,
`someOtherAttribute9` varchar(255) DEFAULT NULL,
`someOtherAttribute10` int DEFAULT NULL,
`someOtherAttribute11` bit(1) DEFAULT NULL,
`someOtherAttribute12` varchar(255) DEFAULT NULL,
`someOtherAttribute13` varchar(255) DEFAULT NULL,
`someOtherAttribute14` datetime DEFAULT NULL,
`budget_id` bigint NOT NULL,
`someOtherAssociation_id` bigint DEFAULT NULL,
`reason_id` bigint DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `FK2acd5rbfcfpxoy64in0keam4q` (`budget_id`),
KEY `FKm5a1xte805xlbr4t36glj1mq2` (`someOtherAssociation_id`),
KEY `FKlj3hrmasc3m12pyt2k9sqie5j` (`reason_id`),
CONSTRAINT `FK2acd5rbfcfpxoy64in0keam4q` FOREIGN KEY (`budget_id`) REFERENCES `Budget` (`id`),
CONSTRAINT `FKlj3hrmasc3m12pyt2k9sqie5j` FOREIGN KEY (`reason_id`) REFERENCES `Reason` (`id`),
CONSTRAINT `FKm5a1xte805xlbr4t36glj1mq2` FOREIGN KEY (`someOtherAssociation_id`) REFERENCES `SomeOtherAssociation` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci'

A single insert:

2023-07-29 14:48:44 [DEBUG] org.hibernate.SQL insert into Booking_REV (REVTYPE, REVEND, REVEND_TSTMP, changeDate, changeDate_MOD, moneyValue, moneyValue_MOD, text, text_MOD, date, date_MOD, isActive, isActive_MOD, budget_id, budget_MOD, labels_MOD, dateOfCompensation, dateOfCompensation_MOD, type, type_MOD, receiptDate, receiptDate_MOD, area, area_MOD, originalMoneyValue, originalMoneyValue_MOD, dateOfBooking, dateOfBooking_MOD, manualChanges, manualChanges_MOD, recorder, recorder_MOD, dateOfRecording, dateOfRecording_MOD, yearOfBusiness, yearOfBusiness_MOD, targetDiscount, targetDiscount_MOD, costType, costType_MOD, creditor, creditor_MOD, project, creditor_MOD, reference, reference_MOD, discount, discount_MOD, status, status_MOD, tax, tax_MOD, stkz, stkz_MOD, cancelationNumber, cancelationNumber_MOD, paymentReason, paymentReason_MOD, orderNumber_MOD, reason_id, reason_MOD, receiptNumbers_MOD, accountString_MOD, DTYPE, id, REV) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'PlainBooking', ?, ?)

Logging org.hibernate on DEBUG level for batch insert:

2023-07-30 00:49:29 [DEBUG] org.hibernate.SQL select tbl.next_val from hibernate_sequences tbl where tbl.sequence_name=? for update
2023-07-30 00:49:29 [DEBUG] org.hibernate.SQL update hibernate_sequences set next_val=?  where next_val=? and sequence_name=?
2023-07-30 00:49:29 [DEBUG] o.h.r.t.b.j.i.JtaIsolationDelegate Surrounding JTA transaction resumed [org.apache.geronimo.transaction.manager.TransactionImpl@243dfe80]
2023-07-30 00:49:29 [DEBUG] o.h.e.j.b.i.AbstractBatchImpl Reusing batch statement
2023-07-30 00:49:29 [DEBUG] org.hibernate.SQL insert into Booking (changeDate, moneyValue, text, budget_id, date, isActive, dateOfCompensation, reason_id, type, receiptDate, area, originalMoneyValue, dateOfBooking, manualChanges, recorder, dateOfRecording, yearOfBusiness, targetDiscount, costType, creditor, project, reference, discount, status, tax, stkz, cancelationNumber, paymentReason, DTYPE, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'PlainBooking', ?)
... 19*3 more of the same lines
2023-07-30 00:49:29 [DEBUG] o.h.e.j.b.i.BatchingBatch Executing batch size: 20
2023-07-30 00:49:29 [DEBUG] o.h.r.j.i.LogicalConnectionManagedImpl Initiating JDBC connection release from afterTransaction
2023-07-30 00:49:29 [DEBUG] o.h.e.j.i.JdbcCoordinatorImpl HHH000420: Closing un-released batch
2023-07-30 00:49:29 [DEBUG] o.h.r.t.b.j.i.JtaIsolationDelegate Surrounding JTA transaction suspended [org.apache.geronimo.transaction.manager.TransactionImpl@64f0214a]
2023-07-30 00:49:29 [DEBUG] org.hibernate.SQL select tbl.next_val from hibernate_sequences tbl where tbl.sequence_name=? for update
2023-07-30 00:49:29 [DEBUG] org.hibernate.SQL update hibernate_sequences set next_val=?  where next_val=? and sequence_name=?
2023-07-30 00:49:29 [DEBUG] o.h.r.t.b.j.i.JtaIsolationDelegate Surrounding JTA transaction resumed [org.apache.geronimo.transaction.manager.TransactionImpl@64f0214a]
2023-07-30 00:49:29 [DEBUG] org.hibernate.SQL insert into Booking (changeDate, moneyValue, text, budget_id, date, isActive, dateOfCompensation, reason_id, type, receiptDate, area, originalMoneyValue, dateOfBooking, manualChanges, recorder, dateOfRecording, yearOfBusiness, targetDiscount, costType, creditor, project, reference, discount, status, tax, stkz, cancelationNumber, paymentReason, DTYPE, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'PlainBooking', ?)
2023-07-30 00:49:29 [DEBUG] o.h.r.t.b.j.i.JtaTransactionCoordinatorImpl Hibernate RegisteredSynchronization successfully registered with JTA platform
2023-07-30 00:49:29 [DEBUG] o.h.r.t.b.j.i.JtaIsolationDelegate Surrounding JTA transaction suspended [org.apache.geronimo.transaction.manager.TransactionImpl@64f0214a]
2023-07-30 00:49:29 [DEBUG] org.hibernate.SQL select tbl.next_val from hibernate_sequences tbl where tbl.sequence_name=? for update
2023-07-30 00:49:29 [DEBUG] org.hibernate.SQL update hibernate_sequences set next_val=?  where next_val=? and sequence_name=?
2023-07-30 00:49:29 [DEBUG] o.h.r.t.b.j.i.JtaIsolationDelegate Surrounding JTA transaction resumed [org.apache.geronimo.transaction.manager.TransactionImpl@64f0214a]
2023-07-30 00:49:29 [DEBUG] o.h.e.j.b.i.AbstractBatchImpl Reusing batch statement
2023-07-30 00:49:29 [DEBUG] org.hibernate.SQL insert into Booking (changeDate, moneyValue, text, budget_id, date, isActive, dateOfCompensation, reason_id, type, receiptDate, area, originalMoneyValue, dateOfBooking, manualChanges, recorder, dateOfRecording, yearOfBusiness, targetDiscount, costType, creditor, project, reference, discount, status, tax, stkz, cancelationNumber, paymentReason, DTYPE, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'PlainBooking', ?)
2023-07-30 00:49:29 [DEBUG] o.h.r.t.b.j.i.JtaIsolationDelegate Surrounding JTA transaction suspended [org.apache.geronimo.transaction.manager.TransactionImpl@64f0214a]
2023-07-30 00:49:29 [DEBUG] org.hibernate.SQL select tbl.next_val from hibernate_sequences tbl where tbl.sequence_name=? for update
2023-07-30 00:49:29 [DEBUG] org.hibernate.SQL update hibernate_sequences set next_val=?  where next_val=? and sequence_name=?
2023-07-30 00:49:29 [DEBUG] o.h.r.t.b.j.i.JtaIsolationDelegate Surrounding JTA transaction resumed [org.apache.geronimo.transaction.manager.TransactionImpl@64f0214a]
2023-07-30 00:49:29 [DEBUG] o.h.e.j.b.i.AbstractBatchImpl Reusing batch statement
2023-07-30 00:49:29 [DEBUG] org.hibernate.SQL insert into Booking (changeDate, moneyValue, text, budget_id, date, isActive, dateOfCompensation, reason_id, type, receiptDate, area, originalMoneyValue, dateOfBooking, manualChanges, recorder, dateOfRecording, yearOfBusiness, targetDiscount, costType, creditor, project, reference, discount, status, tax, stkz, cancelationNumber, paymentReason, DTYPE, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'PlainBooking', ?)
2023-07-30 00:49:29 [DEBUG] o.h.r.t.b.j.i.JtaIsolationDelegate Surrounding JTA transaction suspended [org.apache.geronimo.transaction.manager.TransactionImpl@64f0214a]
2023-07-30 00:49:29 [DEBUG] org.hibernate.SQL select tbl.next_val from hibernate_sequences tbl where tbl.sequence_name=? for update
2023-07-30 00:49:29 [DEBUG] org.hibernate.SQL update hibernate_sequences set next_val=?  where next_val=? and sequence_name=?
2023-07-30 00:49:29 [DEBUG] o.h.r.t.b.j.i.JtaIsolationDelegate Surrounding JTA transaction resumed [org.apache.geronimo.transaction.manager.TransactionImpl@64f0214a]
2023-07-30 00:49:29 [DEBUG] o.h.e.j.b.i.AbstractBatchImpl Reusing batch statement
2023-07-30 00:49:29 [DEBUG]org.hibernate.SQL insert into Booking (changeDate, moneyValue, text, budget_id, date, isActive, dateOfCompensation, reason_id, type, receiptDate, area, originalMoneyValue, dateOfBooking, manualChanges, recorder, dateOfRecording, yearOfBusiness, targetDiscount, costType, creditor, project, reference, discount, status, tax, stkz, cancelationNumber, paymentReason, DTYPE, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'PlainBooking', ?)
2023-07-30 00:49:29 [DEBUG] o.h.r.t.b.j.i.JtaIsolationDelegate Surrounding JTA transaction suspended [org.apache.geronimo.transaction.manager.TransactionImpl@64f0214a]
2023-07-30 00:49:29 [DEBUG] org.hibernate.SQL select tbl.next_val from hibernate_sequences tbl where tbl.sequence_name=? for update
2023-07-30 00:49:29 [DEBUG] org.hibernate.SQL update hibernate_sequences set next_val=?  where next_val=? and sequence_name=?
2023-07-30 00:49:29 [DEBUG] o.h.r.t.b.j.i.JtaIsolationDelegate Surrounding JTA transaction resumed [org.apache.geronimo.transaction.manager.TransactionImpl@64f0214a]
2023-07-30 00:49:29 [DEBUG] o.h.e.j.b.i.AbstractBatchImpl Reusing batch statement
2023-07-30 00:49:29 [DEBUG] org.hibernate.SQL insert into Booking (changeDate, moneyValue, text, budget_id, date, isActive, dateOfCompensation, reason_id, type, receiptDate, area, originalMoneyValue, dateOfBooking, manualChanges, recorder, dateOfRecording, yearOfBusiness, targetDiscount, costType, creditor, project, reference, discount, status, tax, stkz, cancelationNumber, paymentReason, DTYPE, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'PlainBooking', ?)
2023-07-30 00:49:29 [DEBUG] o.h.r.t.b.j.i.JtaIsolationDelegate Surrounding JTA transaction suspended [org.apache.geronimo.transaction.manager.TransactionImpl@64f0214a]
2023-07-30 00:49:29 [DEBUG] org.hibernate.SQL select tbl.next_val from hibernate_sequences tbl where tbl.sequence_name=? for update
2023-07-30 00:49:29 [DEBUG] org.hibernate.SQL update hibernate_sequences set next_val=?  where next_val=? and sequence_name=?
2023-07-30 00:49:29 [DEBUG] o.h.r.t.b.j.i.JtaIsolationDelegate Surrounding JTA transaction resumed [org.apache.geronimo.transaction.manager.TransactionImpl@64f0214a]
2023-07-30 00:49:29 [DEBUG] o.h.e.j.b.i.AbstractBatchImpl Reusing batch statement
2023-07-30 00:49:29 [DEBUG] org.hibernate.SQL insert into Booking (changeDate, moneyValue, text, budget_id, date, isActive, dateOfCompensation, reason_id, type, receiptDate, area, originalMoneyValue, dateOfBooking, manualChanges, recorder, dateOfRecording, yearOfBusiness, targetDiscount, costType, creditor, project, reference, discount, status, tax, stkz, cancelationNumber, paymentReason, DTYPE, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'PlainBooking', ?)
2023-07-30 00:49:29 [DEBUG] o.h.e.j.b.i.BatchingBatch Executing batch size: 6
2023-07-30 00:49:29 [DEBUG] o.h.r.j.i.LogicalConnectionManagedImpl Initiating JDBC connection release from afterTransaction
2023-07-30 00:49:29 [DEBUG] o.h.e.j.i.JdbcCoordinatorImpl HHH000420: Closing un-released batch
java mysql performance hibernate batch-processing
Original Q&A
1

There are 1 answers

12
Rick James On

Multi-threading is likely to be counter-productive because of needing multiple connections. ("Connection pooling" addresses a different issue.)

A batch should be between 100 and 1000 items. More than that gets into "diminishing returns" and may encounter other things (big buffers, etc) that may slow it down.

For further discussion, please provide SHOW CREATE TABLE and a sample of the batched query.

More

47 columns is a lot. Consider breaking out things like "booking" and its associated columns; discount/cost/credit/etc info; some of the "attributes"; clumps of things that are often NULL; etc.

"the budget of each booking is the same" -- sounds like this is an excellent case for breaking the booking stuff into another table.

12 columns are BIGINT (8 bytes each); probably none need to be any bigger than INT (4 bytes, max +/-2 billion). This is only a small issue, but shrinking the data may help avoid the conflicts.

Let's see a sample INSERT. (You can anonymize any sensitive data.)

Related Questions in JAVA

Related Questions in MYSQL

Related Questions in PERFORMANCE

Related Questions in HIBERNATE

Related Questions in BATCH-PROCESSING

Popular Questions

Popular Tags

javascript python java c# php android html jquery c++ css ios sql mysql r reactjs node.js arrays c asp.net json python-3.x ruby-on-rails .net sql-server swift django angular objective-c pandas excel

Trending Questions