0

I came across a scenario where I have to insert 20 rows into a database (SQLite). The approach I followed was to create an update method which would take a String and a float and set them in a PreparedStatement. I used to call this update method 20 times to insert all the rows. This is my update method:

void update(String variableName, int value) throws SQLException {
    String query = "INSERT OR REPLACE INTO Table1 (STRINGNAME, VALUE) VALUES(?,?)";
    try (Connection connection = getDataSource().getConnection(); 
         PreparedStatement pst = connection.prepareStatement(query);) {
        pst.setString(1, variableName);
        pst.setInt(2, value);
        pst.executeUpdate();
    }
}

I got to know that there is a provision to add multiple queries in a batch and execute the whole batch using addBatch and executeBatch methods provided by PreparedStatement.

As per my understanding, addBatch will add an SQL query to the current list of commands for the Statement object and executeBatch will execute all the queries in the list. Isn't this equivalent to calling the update method multiple times (Other than maybe the initialisation of PreparedStatement)? Is there any performance advantage in using addbatch and executeBatch over executing the update method multiple times?

Update

I tested both the scenarios: one with batch update and one with a modified version of the update method for 20000 queries.

updateCommon, modified update method with common PreparedStatment:

void updateCommon(String variableName, int value, PreparedStatement pst) throws SQLException {      
    pst.setString(1, variableName);
    pst.setInt(2, value);
    pst.executeUpdate();
}

updateBatch method, where data is added to batch:

void updateBatch(String variableName, float value, PreparedStatement pst) throws SQLException {     
    pst.setString(1, variableName);
    pst.setInt(2, value);
    pst.addBatch();
}
Testing

updateCommon test was done as follows:

try {
    Connection conn = getDataSource().getConnection();
    PreparedStatement pst = conn.prepareStatement(query);
    for (int i = 0; i < 1000; i++) {
        updateCommon("String1" + i, 1, pst);
        /*
        * till 20
        */
        updateCommon("String20" + i, 20, pst);
    }
}

updateBatch test was done as follows:

try (Connection conn = getDataSource().getConnection(); PreparedStatement pst = conn.prepareStatement(query); ) {
    for (int i = 0; i < 1000; i++) {
        updateBatch("String1" + i, 1, pst);
        /*
        * till 20
        */
        updateBatch("String20" + i, 20, pst);
    }
    pst.executeBatch();
}
Result:

Time taken by updateCommon method for completion of test: 117.2 seconds
Time taken by updateBatch method for completion of test: 267.6 seconds

This further makes the question more interesting as it seems like, direct update with a common PreparedStatement is almost twice faster in this specific use case.

Therefore, Is there any advantage in using addBatch() and executeBatch() over executing multiple queries sequentially?

mtm
  • 504
  • 1
  • 7
  • 25
  • Based on my testing, it seems that there is a loss if we use batch. Check the update in the question. – mtm Aug 03 '21 at 17:21
  • So can it be concluded that we can't definitely say which would give better performance as it depends completely on which Database we are using? – mtm Aug 04 '21 at 00:58

1 Answers1

0

Yes there is an performance advantage.
Calling your update-method 20 times will execute 20 Insert-Statements.

Using the addBatch/executeBatch way, only one Insert-Statement gets executed which will insert 20 Lines at once, this is faster.

Allthough you might not recognize a difference with 20 Lines, you will with several 1000 Lines.

frank
  • 1,007
  • 1
  • 6
  • 13
  • I did some testing using both the cases. For multi-update, I used a common connection and a common prepared statement. In that case, multi-update has almost double the speed when executing 20000 instructions. – mtm Aug 03 '21 at 16:50