Search code examples
javajdbccommitbind-variables

Should I COMMIT after every execute batch?

I have a 1 trillion records file. Batch size is 1000 after which the batch is Executed.

Should I commit after each Batch ? Or Commit just once after all the 1 trillion records are executed in Batches of 1000 ?

{
// Loop for 1 Trillion Records
statement.AddBatch()

      if (++count % 1000 == 0) 
       {
       statement.executeBatch()
       // SHOULD I COMMIT HERE AFTER EACH BATCH ???
       }

} // End Loop
// SHOULD I COMMIT HERE ONCE ONLY ????
Solution

  • A commit marks the end of a successful transaction. So the commit should theoretically happen after all rows have been executed successfully. If the execution statements are completely independent, than every one should have it's own commit (in theory).

    But there may be limitations by the database system that require to split up the rows in several batches with their own commit. Since a database has to reserve some space to be able to do a rollback unless changes are committed, the "cost" of a huge transaction size may by very high.

    So the answer is: It depends on your requirements, your database and environment.