Batch SQL Insert/Update Features Now Available

[Former Member]

This blog post is meant to introduce some new features available in 14.0 SP7, and coming up for 15.0 SP5.  Some customers have requested additional functionality to support batch insert/updates for SQL queries executed by MII. We have found multiple customer implementations that rely on inserts and updates in iterative loops, and being able to submit these queries in a batch request to the database would help performance.  Our tests have shown that this feature helps improve performance by about 30%.

Implementation

This has been implemented via the addBatch/executeBatch methods of JDBC.  To utilize this feature, your JDBC driver must support these methods.  The methods were added to the Java interface in Java 5.  If the JDBC driver used does not support this, a suitable error will be displayed.

There were several additions made to help implement this: 

• The IDBC connector has a new mode, AddBatch.  This can be used to replace the Command mode. 
• The MDO connector has three new modes, AddBatchInsert, AddBatchUpdate, and AddBatchDelete.  These are meant to replace the Insert, Update, and Delete modes.
• The SQL Transaction action blocks have two addition, SQL Transaction ExecuteBatch and SQL Transaction ClearBatch.  ExecuteBatch submits the internal statement batch to the database, and ClearBatch clears the same batch and does not submit it. 

The SQL Transaction actions must be used in conjunction with the AddBatch query template mode.  The SQL Transaction actions maintain the SQL Connection and PreparedStatement objects and allow them to span action block calls.  The following screenshot gives an example of how the action blocks work together:

You can see from this that the SQL Transaction is started with the Begin action block, then the AddBatch action has the SQL Transaction passed in via link.  The loop runs, updating the parameters for the AddBatch action block for each row to be inserted.  Then at the end of the loop, the ExecuteBatch action block is called, and then the transaction is committed and ended.

The MDO modes have the same implementation detail as AddBatch, they can be chained and added to the SQL Transaction built.

These changes were implemented as part of note 2178396, please follow this note for release details and further support information.  This note is currently released for 14.0 SP7 P0, and is being upported for 15.0 SP5, due for release in Fall 2015.

One other point, this functionality was previously available via the JTA custom actions, which were available on the SDN.  The functionality provided by those actions is similar to this collection of features, with some differences.  This functionality supports easy upgrading of existing calls to Command queries in MII transactions just by changing the mode of the query template to AddBatch and using the SQL Transaction action blocks to manage the SQL connection.  In addition, the JTA actions on the SDN are not part of core MII and are not supported by SAP.

I invite feedback on this new functionality, feel free to offer comments or suggestions below.  Thanks for reading,

Mike