JDBC + Java - Batch updates doubts

Question

I am using Oracle Database 12c with oracle.jdbc.driver.OracleDriver.

How batch insert is working? I know that is grouping statements, but what exactly if happening during preparedStatement.executeBatch? Is it executing only 1 insert per batch?

What approach for executing batches is better. This one with execute outside loop:

PreparedStatement ps = c.prepareStatement("insert into some_tab(id, val) values (?, ?);");
for (int i = 0; i < 3000; i++) {
  ps.setLong(1, i);
  ps.setString(2, "value" + i);
  if ((i + 1) % 3 == 0) {
    ps.addBatch();
  }
}

ps.executeBatch();

Or this - with execution in loop:

PreparedStatement ps = c.prepareStatement("insert into some_tab(id, val) values (?, ?);");
for (int i = 0; i < 3000; i++) {
  ps.setLong(1, i);
  ps.setString(2, "value" + i);
  ps.addBatch();
  if ((i + 1) % 3 == 0) {
    ps.executeBatch();
  }
}

ps.executeBatch();

Maybe this will help - https://stackoverflow.com/questions/2993251/jdbc-batch-insert-performance (actually that depends on which DB you are using) — Eran, Apr 11 '18 at 12:47
One will only add 1/3 of the insert queries into a batch. The other will execute the batch every 3 iteration. — AxelH, Apr 11 '18 at 12:47
How batch inserts work is not really answerable, because exactly how batched inserts work depends on the database and its driver. — Mark Rotteveel, Apr 11 '18 at 12:49
@MarkRotteveel I am using I am using Oracle Database 12c with oracle.jdbc.driver.OracleDriver. — Wicia, Apr 11 '18 at 13:07

AxelH · Answer 1 · 2018-04-11T13:12:46.140

What approach for executing batches is better.

PreparedStatement.addBatch insert the current parameters into the batch

Adds a set of parameters to this PreparedStatement object's batch of commands.

preparedStatement.executeBatch send the current batch to the DB.

Submits a batch of commands to the database for execution

So your codes don't have the same logic. One will only add 1/3 of the insert queries into the batch. The other will execute the batch every 3 iteration.

I would sugget a mix of both, add each iteration into the batch, and every # iteration, execute it :

while(...){
    ...
    ps.addBatch();    
    if ((i + 1) % 3 == 0) { // 3 is just the example, this can be much higher
       ps.executeBatch();
    }
}
//Send the rest if the loop ended with `(i + 1) % 3 != 0`
ps.executeBatch();

Note that a batch of 3 is probably not necessary, you can increase the value drastically, but I don't really know a way to "estimate" the size of a batch to be efficient, I usually use a batch of 1000 items but this is not to take for granted...

what exactly if happening during preparedStatement.executeBatch? Is it executing only 1 insert per batch?

I always see a batch like a package.

you write a label with the address (create PreparedStatement)
you take a box and stick the address on it
you fill the box with parameters (multiple addBatch)
if the box is too small, you send the current box (executeBatch)
if there is still items to send, restart at point 2

The idea is to limit the number of communication from the JDBC and the DB using a package/batch.

How this is working under the hood is not really answerable and should not really be a concerned.

I appreciate @YCF_L , yours was fine too, I didn't saw any problem with both being present ;) — AxelH, Apr 11 '18 at 13:11
You should use preparedStatement.addBatch() and preparedStatement.sendBatch() String dml = "UPDATE EMPLOYEES SET SALARY = ? WHERE EMPLOYEE_ID = ?”;  pstmt = connection.prepareStatement(dml);  for(int i = 0; i < NUM_ROWS_TO_INSERT; ++i) {  int empId = getEmployeeId(employeeIdList);  pstmt.setInt(1, getNewSalary(empId));  pstmt.setInt(2, empId);  pstmt.addBatch();   } pstmt.sendBatch(); — Nirmala, Apr 21 '18 at 01:49

JDBC + Java - Batch updates doubts

1 Answers1