Should I COMMIT after every execute batch?

Question

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 ????

You have to ask yourself whether you would miss the 1000 rows if something bad was to happen. — Scary Wombat, Jul 16 '14 at 05:27
Won't it be better to commit after less than 1tr rows,say 1000, 5000 ? You don't want to lose time and effort due to lost connections and such, right ? I am assuming that those tr rows are not considered as a single transaction per business rules. — Erran Morad, Jul 16 '14 at 05:28
That depends on your (business) requirements. If all inserts/update should be in a single transaction than you don't have a choice (and I don't believe you have 1,000,000,000,000 records in a single file) — , Jul 16 '14 at 05:28
i think this is related to requirement, if db operation are not related to each other then you should commit after every 1000 batch — coreJavare, Jul 16 '14 at 05:28
Also the transaction for 1,000,000,000,000 is going to be rather huge — Scary Wombat, Jul 16 '14 at 05:28
What is your fallback plan if there is some issue with the batch? If something goes wrong what will you do with the batch? Reprocessing time will be huge if you do single commit and will be hard to fix the problematic batch. — Ambrish, Jul 16 '14 at 05:30
@a_horse_with_no_name ... Im saying there are huge recs. Its a 7.11 GB file — Noman K, Jul 16 '14 at 05:31

score 4 · Accepted Answer · answered Jul 16 '14 at 05:35

4

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.

answered Jul 16 '14 at 05:35

mschenk74

3,561
1
21
34

1

Hi Mschenk74, If I perform a commit only once at the end, will the DB even be able to insert 1T rows at once ? Even if the UNDO Buffer of DB allows 7GIG space to hold all the rows from the ExecuteBatch() ? – Noman K Jul 16 '14 at 23:59
1

As I wrote-it depends on the database. But I guess that most databases will have problems with this size. – mschenk74 Jul 17 '14 at 04:37

score 0 · Answer 2 · answered Oct 13 '21 at 11:13

Mostly it depends what you want to achieve, usually you need to compromise on something to achieve something. For example, I am deleting 3 million records that are no longer being accessed by my users using a stored procedure.

If I execute delete query all at once, a table lock gets escalated and my other users start getting timeout issues in our applications because the table has been locked by SQL Server (I know the question is not specific to SQL Server but could help debug the problem) to give the deletion process better performance, If you have such a case, you will never go for a bigger batch than 5000. (See Lock Escalation Threshold)

With my current plan, I am deleting 3000 rows per batch and only key lock is happening which is good, I am committing after half a million records are processed.

So, if you do not want simultaneous users hitting the table, you can delete the huge number of records if your database server has enough log space and processing speed but 1 Trillion records are a mess. You better proceed with a batch wise deletion or if 1 Trillion records are total records in the table and you want to delete all of those records, then I'd suggest go for a truncate table.

Should I COMMIT after every execute batch?

2 Answers2