Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site

http://developer.mimer.com


Enhancing Performance


The batch update functionality within JDBC 2 allows the Statement objects to support the submission of a number of update commands as a single batch.

The ability to batch a number of commands together can have significant performance benefits. The methods addBatch, clearBatch and executeBatch are used in processing batch updates.

The PreparedStatement example above could be simply rewritten to batch the commands.

For example:

 PreparedStatement pstmt;
 int [] affectedCount;
 
 pstmt = con.prepareStatement(
                    "UPDATE mimer_store.currencies"
                  + "   SET exchange_rate = exchange_rate * ?"
                  + "   WHERE code = ?");
                  
 pstmt.setFloat(1, 1.05f);
 pstmt.setString(2, "USD");
 pstmt.addBatch();
 
 pstmt.setFloat(1, 1.08f);
 pstmt.setString(2, "GBP");
 pstmt.addBatch();
 
 affectedCount = pstmt.executeBatch();
 

The Mimer SQL database server executes each command in the order it was added to the batch and returns an update count for each completed command.

If an error is encountered while a command in the batch is being processed then a BatchUpdateException is thrown (see Error Handling) and the unprocessed commands in the batch are ignored.

In general it may be advisable to treat all the commands in the batch as a single transaction, allowing the application to have control over whether those commands that succeeded are committed or not.

Set the Connection object's auto-commit mode to off to group the statements together in a single transaction. The application can then commit or rollback the transaction as required.

Calling the method clearBatch clears a Statement object's list of commands.

Using the Close method to close any of the Statement objects releases the database and JDBC resources immediately. It is recommended that Statement objects be explicitly closed as soon as they are no longer required.



Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
dbtechnology@upright.se
Mimer SQL Documentation TOC PREV NEXT INDEX