Considerations regarding addBatch(String)

Question

Next to the addBatch() method of PreparedStatement there is also an addBatch(String) method in the Statement class.

I want to process a series of different sql statements and am looking for some clarification on what addBatch(String) means performance-wise. Is it safe (and fast) to use this method or is it better to group similar sql-statements in Java and execute them in groups?

Answer 1

Batch Processing allows you to group related SQL statements into a batch and submit them with one call to the database.

When you send several SQL statements to the database at once, you reduce the amount of communication overhead, resulting in improving performance.

addBatch(String sql) is defined in the interface java.sql.Statement and addBatch() is defined in the java.sql.PreparedStatement is one of the subinterface which extends Statement. (Other is java.sql.CallableStatement)

According to JAVA Doc

addbatch(String sql) method cannot be called on a PreparedStatement or CallableStatement and sql - typically this is a SQL INSERT or UPDATE statement.

addBatch() is preferred If you want bulk insertion or bulk update operations.

There are few blocking factors on batch exections which are

DB make/version.
JDBC driver make/version.

For Example: If you're using MySQL, use the latest driver add rewriteBatchedStatements=true to your connection string to make statement.addBatch() actually create batch inserts.

Also, You should make sure that auto commit is false. Maintain a counter which and reaching on that counter executeBatch which help when Available heap memory in Java is constraint.

Example:

conn.setAutoCommit(false);  
PreparedStatement pStmt = conn.prepareStatement("INSERT INTO mytable (field1,field2) VALUES (?,?)");

for all values:
  pStmt.setString(1,val1);
  pStmt.setString(2,val2);
  pStmt.addBatch();    
  if ( i % 1000 == 0) {  
            pstmt.executeBatch();// Execute every 1000 items  
  } 
pstmt.executeBatch(); 
conn.commit();