From 10.2 JDBC developers guide Oracle update batching is a more efficient model because the driver knows ahead of time how many operations will be batched. In this sense, the Oracle model is more static and predictable. With the standard model, the driver has no way of knowing in advance how many operations will be batched. In this sense, the standard model is more dynamic in nature.
From 11.2 JDBC developers guide Oracle recommends that you use JDBC standard features when possible. This recommendation applies to update batching as well. Oracle update batching is retained primarily for backwards compatibility.
From 12.1 JDBC developers guide Starting from Oracle Database 12c Release 1 (12.1), Oracle update batching is deprecated. Oracle recommends that you use standard JDBC batching instead of Oracle update batching.
As the Oracle mode batching is depreciated the comparison only holds for lower versions of the JDBC drivers. The test related to this post was carried out using 11.2 JDBC driver. The java code used for the test is given at the end of the post. Two tests were carried out. First involving updating number of rows in a table with standard, standard implementation of oracle batching and oracle batching. Standard batching test only execute the update batch once. The standard implementation mimics what the Oracle standard does by executing the update batch at fixed intervals (500 and 1000 updates at a time). Finally Oracle batching will use the values of 500 and 1000 for batch values. This test is to check if there's any considerable difference in the timing of the updates. Result table is given below.
| # Updates | Standared Batching | Standared Batching - 500 | Standared Batching - 1000 | Oracle Batching - 500 | Oracle Batchnig - 1000 |
| 100 | 0.35 | 0.35 | 0.36 | 0.36 | 0.36 |
| 200 | 0.62 | 0.62 | 0.65 | 0.63 | 0.63 |
| 500 | 1.43 | 1.41 | 1.47 | 1.43 | 1.45 |
| 1000 | 2.76 | 2.73 | 2.88 | 2.81 | 2.81 |
| 2000 | 5.72 | 5.38 | 5.64 | 5.54 | 5.68 |
| 5000 | 13.55 | 13.43 | 13.99 | 13.67 | 13.79 |
| 10000 | 27.82 | 26.58 | 28.05 | 27.31 | 27.58 |
| 25000 | 80.01 | 75.43 | 78.65 | 76.42 | 76.93 |
| 50000 | 165.24 | 158.99 | 164.32 | 159.13 | 159.04 |
| 75000 | 243.25 | 242.92 | 248.73 | 242.17 | 242.79 |
| 100000 | 330.92 | 330.84 | 333.34 | 327.65 | 331.22 |
There's not much difference in terms of time it takes to complete all the updates whether it is via standard batching or oracle batching. Scatter plot shown below.
The next test involved updating a column with a blob (size 8KB). The results table is given below.
| # Updates | Standared Batching | Standared Batching - 500 | Standared Batching - 1000 | Oracle Batching - 500 | Oracle Batchnig - 1000 |
| 100 | 1.47 | 1.53 | 1.50 | 1.68 | 1.76 |
| 200 | 2.79 | 2.96 | 2.91 | 3.14 | 3.19 |
| 500 | 6.88 | 7.37 | 7.14 | 7.68 | 7.87 |
| 1000 | 13.60 | 14.70 | 14.20 | 15.30 | 15.50 |
| 2000 | 27.31 | 29.25 | 30.13 | 30.07 | 30.41 |
| 5000 | 68.30 | 74.10 | 74.89 | 77.23 | 74.82 |
| 10000 | 143.31 | 151.05 | 143.14 | 151.68 | 152.43 |
| 25000 | 348.83 | 351.34 | 365.54 | 384.77 | 394.99 |
| 50000 | 701.67 | 727.28 | 707.79 | 857.79 | 859.15 |
There's not much difference in the update time between the different standard batching tests.
However there's some advantage when using standard batching compared to oracle standard as the number of updates are 25,000 or over. But the increase number of batching size requires more memory and this situation may be uncommon. For lower values again there's not much difference in terms of time saved.
These tests have shown that batching mode (Oracle or standard) does not have a real influence on the time taken for the updates. As mentioned earlier as of 12c the Oracle standard batching is depreciated and if these results hold true for 12c driver as well then there shouldn't be any performance degradation.
Test java code used
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import oracle.jdbc.pool.OracleDataSource;
/**
*
* @author Asanga
*/
public class Update {
public static void main(String[] args) {
try {
OracleDataSource dataSource = new OracleDataSource();
dataSource.setURL("jdbc:oracle:thin:@192.168.0.66:1521:ent11g2");
dataSource.setUser("asanga");
dataSource.setPassword("asa");
// String SQL = "update x set b = ?, c = ? where a = ?"; // update blob
String SQL = "update x set b = ? where a = ?";
Connection con = dataSource.getConnection();
con.setAutoCommit(false);
long t1 = System.nanoTime();
// byte[] lob = new byte[8*1024];
// lob[10] =10;
// lob[8000]=20;
PreparedStatement pr = con.prepareStatement(SQL);
// ((OraclePreparedStatement)pr).setExecuteBatch(1000);
for(int i =1; i <= 75000; i++){
StringBuilder b = new StringBuilder("khf").append(i);
pr.setString(1, b.toString());
// pr.setBytes(2,lob );
pr.setInt(2, i);
pr.addBatch();
if((i > 0) && (i%1000 == 0)){
int[] ret =pr.executeBatch();
System.out.println(ret.length);
}
// pr.executeUpdate();
}
int[] ret = pr.executeBatch();
// ((OraclePreparedStatement)pr).sendBatch();
con.commit();
long t2 = System.nanoTime();
System.out.println("returned "+ret.length+" time : "+(t2-t1));
// System.out.println("time : "+(t2-t1));
pr.close();
con.close();
} catch (SQLException ex) {
Logger.getLogger(Update.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
