1

I have an Update statement which update several columns. Some of those columns are nvarchar(max) type and not always need to be updated.

I would like to avoid updating unnecessary columns, so, the simplest solution I come up is the following: 

UPDATE table1 SET column1 = @col1, Column2= @col2, ...
TextColumn = CASE SomeVar WHEN 0 THEN @NewVal ELSE TextColumn 
WHERE idTable = @idTable

Does any one know if SQL is smart enough to avoid updating the TextColumn if it is going to be updated with itself?

The second option is just to split the Update as follows:

    UPDATE table1 SET column1 = @col1, Column2= @col2 ...
    WHERE idTable = @idTable

   IF Condition 
    UPDATE TextColumn = @TextVal WHERE idTable = @idTable

I do not know if there is a better alternative, maybe it is possible to dynamically choose the columns in the update sentence.

I will appreciate any suggestion about this, my main target is improve the performance of the Update

This question is similar to this one. However, that question does not address the posibility of spliting the Update statement in two sentences in order to avoid unnecessary updates. Moreover, in this case the column to update is nvarchar which involves a large amount of text which brings other performance considerations.

Ayorus
  • 149
  • 1
  • 1
  • 7
  • I believe any existing SQL database will update all columns referenced in the UPDATE statement, whether the new value is different from the old one or not. – mustaccio May 22 '19 at 15:33
  • @mustaccio almost all. MySQL checks first and doesn't update if the two values are identical. – ypercubeᵀᴹ May 22 '19 at 15:35
  • First I would make a test including TextColumn in the update and one without. Compare the times and see whether it makes a significant difference (my guess is that it doesn't). Only if it does consider implementing an optimization. – Olivier Jacot-Descombes May 22 '19 at 15:35
  • @ypercubeᵀᴹ It should be noted though that, while certain MySQL storage engines might skip copying identical column values, the entire row in the engine's format will still be written back to the page (with "non-changing" columns possibly having moved to new offsets due to other columns' length changes), and the entire page will still be written to disk at some point, so I'm not exactly sure why this "optimization" is even necessary. – mustaccio May 22 '19 at 17:07
  • @mustaccio I guess it's useful when all the columns updated in a row do not change. UPDATE t SET a = 20 ; If half the rows already have 20, it'll update only the other half of the possibly million rows. – ypercubeᵀᴹ May 22 '19 at 18:15
  • ... which is easily dealt with by .. where a <> 20, which will also completely avoid reading the unnecessary rows as opposed to discarding them later on, after value comparison. MySQL developers just create more work for themselves by encouraging sloppy coding practices among MySQL users... – mustaccio May 22 '19 at 18:41

0 Answers0