Better to do inside or outside the loop?

Question

Maybe there is no simple answer to this question, but I ask that someone have, if not a simple answer, at least an insight.

I have had several cases when I create a cycle that goes through many records in the database table that perform some updating, and where I could legally make one big commit at the end or commit each record as it is processed, i.e. make one at a time, would not create data integrity problems.

Is there a clear case that is better?

What came to mind was that I had one such program, which I recently switched from one big commit to a bunch of small commits, because it was a rather long program - about 80 minutes - and it didn’t work halfway through bad data . I fixed the problem and restarted it, but then she had to start all over again from the very beginning, when I could just process previously unprocessed records.

I noticed that when I made this change, the runtime was about the same.

Answer 1

Assuming that the ability to roll back all persistence is not required (in this case there is only one answer: commit outside), commit inside the loop keeps the transaction log less, but requires more database accesses. Leaving the loop is the exact opposite. This depends more quickly on the average number of operations and the amount of data that must be completed as a whole. For a subroutine that stores about 10-20 entries, pass out the loop. For records measuring 1 m-2 m, I did this in batches.

Answer 2

I think the answer is that you need to roll back everything if it fails? If so, put the transaction outside, otherwise put it inside. Of course, I almost never wrote a loop to do the update anyway, except to handle fairly large batches of records. If you take a series of lines in turn, there are better, more efficient methods.

Answer 3

In terms of performance, it is usually better to make one big latch at the end (let network traffic, as a rule, work less for the database).

This, of course, depends on many factors, such as indexing in a table, amount of data, etc.

What should be your decision, it is important how important each update is - should it be a transaction in itself? Does updating many elements make sense? What happens if the cycle fails?

By answering these questions, you will get the right way to do this in your application for this process - you can use various methods of processing commit, depending on the context of the application.