[CAM-8327] Switch on MyBatis batch processing mode Created: 16/Oct/17  Updated: 23/Feb/18  Resolved: 07/Nov/17

Status: Closed
Project: camunda BPM
Component/s: engine
Affects Version/s: None
Fix Version/s: 7.8.0, 7.8.0-alpha6

Type: Task Priority: L3 - Default
Reporter: Svetlana Dorokhova Assignee: Yana Vasileva
Resolution: Fixed Votes: 0
Labels: None
Remaining Estimate: 0 minutes
Time Spent: Not Specified
Original Estimate: 0 minutes

Issue Links:
Related
is related to CAM-8351 ORA-01000 on Oracle 12 with MyBatis B... Closed

 Description   

MyBatis has the special mode of pprocessing SQL statements in batches.

The task is:

  • to switch it on
  • to measure performance improvement


 Comments   
Comment by Svetlana Dorokhova [ 19/Oct/17 ]

Performance test consisted of the following:

1. FULL history level
2. Process looks like this:

BpmnModelInstance process = Bpmn.createExecutableProcess("process")
      .startEvent()
      .serviceTask()
      .camundaClass(NoopDelegate.class.getName())
      .camundaAsyncAfter()
      .serviceTask()
      .camundaClass(UpdateVarDelegate.class.getName())
      .camundaAsyncAfter()
      .serviceTask()
      .camundaClass(DeleteVarDelegate.class.getName())
      .endEvent()
      .done();

3. Process is started with 500 process variables. 1st Service task does nothing, but the transaction is committed after it is completed (asyncAfter). 2nd service task update all 500 process variables with new values and the transaction is committed afterwards. 3rd service task removes all the variables and process finishes.

The testing was done with the help of QA Performance test suite. It was running the test in 2 threads with 50 repeations. The result are the following:

- Oracle 12 Mysql 5.7.17 SQL Server 2014
SIMPLE mode 196696 135528 264003
BATCH mode 58012 128714 94147

It looks like MySQL does not distinguish batch and not batch operations, but SQL Server and Oracle do.

Comment by Svetlana Dorokhova [ 20/Oct/17 ]

Won't work for Oracle < 12.

http://docs.oracle.com/cd/E11882_01/java.112/e16548/oraperf.htm#JJDBC28754

For a prepared statement batch, it is not possible to know the number of rows affected in the database by each individual statement in the batch. Therefore, all array elements have a value of -2. According to the JDBC 2.0 specification, a value of -2 indicates that the operation was successful but the number of rows affected is unknown.

https://docs.oracle.com/database/121/JJDBC/oraperf.htm#JJDBC28773

For a prepared statement batch, the array contains the actual update counts indicating the number of rows affected by each operation.

This means that we won't be able to use optimistic locking with Batch processing switched on on Oracle earlier than v. 12.

Generated at Wed Sep 18 18:41:23 CEST 2019 using JIRA 6.4.6#64021-sha1:33e5b454af4594f54560ac233c30a6e00459507e.