0

I have a merge statement that either needs to insert about 65 rows worth of data, or update rows. At the moment I'm updating all columns when a match is found. But I don't need to.

Would there be a significant performance increase if i only updated a few columns on WHEN MATCHED THEN UPDATE vs updating all columns?

3
  • 1
    Oracle? SQL Server? DB2? .... Commented Feb 14, 2018 at 11:31
  • 1
    The less you update the less log you generate. So you can gain something here, I believe.
    – kirchner
    Commented Feb 14, 2018 at 11:32
  • Paul White wrote about non-updating updates a while back, if that helps. Commented Feb 14, 2018 at 12:43

1 Answer 1

3

My first thought was: no, the whole row will get re-written whether you update one column or many, in both the log and data files. This should be true for simple un-indexed columns at least but there could be complications.

It may make a difference if the "extra" columns are indexed: will the engine be bright enough to know it doesn't need to make changes for what is effectively no change? Of course if the update results in the row moving to a different page because it is variable length and the update causes it to no longer fit forcing a page split, then all the indexes will need updating anyway. Another complication might be your own code in triggers if your database engine supports identifying which columns were altered (UPDATE() and COLUMNS_UPDATED() in SQL Server): if your code is dependent on that does the engine identify which columns really changed or just list those that were written to?

This will all be dependent on which database engine you are using, so you will need to specify that in your question text and/or tags to get less generic advice.

1
  • +1 and not only triggers. Any constraints (CHECK, FK, UNIQUE, etc) that the updated column is involved will have to be checked as well when a column is updated. And if there are referencing FKs with cascade actions enabled, the actions might have to performed as well, even if they don't change the actual values (not sure about this, might be implementation detail, per DBMS). Commented Feb 14, 2018 at 11:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.