SQL UPDATE: Multiple columns in one statement or multiple single-column statements?

Question

Is it more efficient to execute one UPDATE statement with multiple columns

UPDATE myTable 
SET [col1] = [col1] + 1, 
[col2] = [col2] + 1,
[col3] = [col3] + 1,
... 
[colN] = [colN] + 1

or multiple UPDATE statements with a single column each?

UPDATE myTable SET [col1] = [col1] + 1
UPDATE myTable SET [col2] = [col2] + 1
UPDATE myTable SET [col3] = [col3] + 1
...
UPDATE myTable SET [colN] = [colN] + 1

Answer 1

This may vary with the particular implementation of any one database engine, but I'm going to cast my vote that a single update will generally be better performing than successive updates. The DB need only find the desired record once, update the relevant values, and be done with it.

Now, given that there are plan caches, indexes, optimizers, and other elements at hand to make things smarter on the fly, it may well be that the multi-update version will nearly approach the performance of the former, but my expectation is that, even under the best of circumstances, the former will perform better.

Answer 2

There is no question whatsoever that the single statement is more efficient. Every update statement needs to fetch the record into memory, perform the updates on the record, and then write out the results.

By having multiple updates, you have to read in the record multiple times and write it out multiple times. With one statement, you do this once.

In addition, databases record changes to a log file, to maintain integrity of the data. Each update gets logged, regardless of the number of fields being changed.

The situation becomes even worse when indexes, tables larger than memory, constraints, and triggers are involved.

The second version should take approximately N times the first version, where N is the number of columns being modified.