Execute batch update faster

Question

In an old project, I have a table with 3 indices on 3 columns, I am executing batch queries (~= 5000 update queries) like UPDATE mytable set mycolumn = 'blah' when myIndexedColumn = 'someId'.

The executeBatch command, takes 1h:30 approximately, I use oracle 11g database, and Java 6, Spring batch also. The concerned table holds 1 700 000 rows.

Statement ps = null;
Connection connection = null;
    try {
        int counter = 0;
        connection = myDatasource.getConnection();
        connection.setAutoCommit(false);

        ps = connection.createStatement();

        for (String request : myListOfRequests) {
            ps.addBatch(request);       
        }
        ps.executeBatch();
        connection.commit();

    } catch (SQLException ex) {
        LOGGER.error("My Errors : {}", ex.getMessage());
    } finally {

        if (ps != null) {
            ps.clearBatch(); 
            ps.close(); 
        }

        if (connection != null) connection.close();
    }

I have dropped the indices, but I didn't noticed a significant difference. I cannot use modern technologies. Also deleting and rebuilding a new table is not a secured task. So have you an idea how I can improve this task?

What about a [prepared statement](http://tutorials.jenkov.com/jdbc/batchupdate.html) with a batch of parameters instead of a batch of statements (also [here for spring](https://www.tutorialspoint.com/springjdbc/springjdbc_batch_operation.htm))? Saves the parsing for each of them on the db side, and network traffic too. — Curiosa Globunznik, Oct 22 '20 at 14:33
and be sure that there are indexes on the columns, and that the optimizer uses them, if as you say, just updating 5k rows out of 1.7M rows. — gsalem, Oct 22 '20 at 15:15

score 1 · Accepted Answer · answered Oct 25 '20 at 08:41

Solution, originally posted by jawad abbassi in the question:

Thanks to @curiosa, I have used preparedStatement instead of a statement in this way:

PreparedStatement ps = null;
Connection connection = null;
String sql =  "UPDATE MYTABLE SET COLUMN1 = ? WHERE COLUMN2 = ?";

try {

    connection = myDataSource.getConnection();
    connection.setAutoCommit(false);
    ps = connection.prepareStatement(sql);

    for (MyBean bean : myListOfBeans) {

        ps.setBigDecimal(1, bean.getColumn1());
        ps.setString(2, bean.getColumn2());
        ps.addBatch();

    }
    ps.executeBatch();

} catch (SQLException ex) {
    LOGGER.error("My errors : {}", ex.getMessage());
} finally {
    if (ps != null) {
        connection.commit();
        ps.close(); 
        connection.close();
    }
}

Execute batch update faster

1 Answers1