Batch SQL Insert/Update Features Now Available
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%.
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,
I tried this feature for MDO and SQL queries and it works pretty well . One of the prerequisite to use this feature is to always use the typed parameter .
I have lot of scenarios where I have to build the SQL query dynamically . In that case its hard to use typed parameters.
It would be great if we can enhance this feature to support untyped parameter as well .
[phone removed by Moderator]
Untyped parameters are not supported. Queries using untyped parameters have their values substituted directly in the query, rather than assigned to the prepared statement. So in the case of batched queries, the initial compiled statement contains the values first assigned.
It may be that we need to add some validation or a better error message for using untyped parameters for this AddBatch mode. We will address this in an upcoming patch.
is there any way to insert a NULL value for a string or Double using the Add Batch Mode .
I am getting below exception when I try yo use nullnumber function for a parameter of type Double .