CASE vs Multiple UPDATE queries for large data sets - Performance

The CASE version.

This is because there is a good chance you are altering the same row more than once with the individual statements. If row 10 has both condition_1 and condition_y then it will need to get read and altered twice. If you have a clustered index this means two clustered index updates on top of whatever the other field(s) that were modified were.

If you can do it as a single statement, each row will be read only once and it should run much quicker.

I changed a similar process about a year ago that used dozens of UPDATE statements in sequence to use a since UPDATE with CASE and processing time dropped about 80%.

It seems logic to me that on the first option SQL Server will go through the table only once and for each row, it will evaluate the condition.

On the second, it will have to go through all table 4 times

So, for a table with 1000 rows, on the first option on the best case scenario we are talking about 1000 evaluations and worst case, 3000. On the second we'll always have 4000 evaluations

So option 1 would be the faster.

As pointed out by Mitch, try making a temp table filling it with all the data you need, make a different temp table for each column (field) you want to change. You should also add an index to the temp table(s) for added performance improvement.

This way your update statement becomes (more or less):

UPDATE tbl_name SET field_name = COALESCE((SELECT value FROM temp_tbl WHERE tbl_name.conditional_field = temp_tbl.condition_value), field_name),    field_name2 = COALESCE((SELECT value FROM temp_tbl2 WHERE tbl_name.conditional_field2 = temp_tbl2.condition_value), field_name2)

and so on..

This should give you good performance while scaling up for large volumes of updates at once.