0

Suppose I am running multiple column update on one row. Does the database engine physically write new data to the database if some of the columns contain the same values as existed previously in the database? If so, how can it be avoided?

I'm about to implement a job which will run an update on a large table and most of the values will be the same but still, it has to recalculate them all. I am wondering if the update will also rewrite each column even if there is no need to, because the storage media will degrade faster.

closed as too broad by Shawn Melton, billinkc, Marian, dezso, RolandoMySQLDBA Jul 23 '13 at 18:34

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

5

You should assume the UPDATE is always writing the new value, even when identical. Write the query to eliminate no-ops with an appropriate WHERE clause. Approach it as a large table update, ie. do it in batches and make it idempotent. Is the only safe approach that is not victimized by assumptions and implementation specific details.

Case in point if you test prior to SQL Server 2012 you may conclude that an update that generates an identical in-place row image does not update the page and generates no log, while SQL 2012 under identical conditions you may conclude that it does not update the page but does generate log.

2

I can't speak for all database engines, but most will perform an update if told to even if the result is no real change. MSSQL operates this way in some circumstances, though as pointed out in comments below it has since SQL2005 had some optimisations in the area, and I would be surprised if that isn't the case for most if no all.

To avoid the extra writes you would need to add extra conditional clauses to your UPDATE statements as needed. Ensure that this is what you want though: for instance if you have any auditing triggers operating on that table you might want the save operation to be seen by them even if no change is actually made to some (or all) of the rows.

  • 2
    SQL Server 2005 added an optimization to skip nonclustered index maintenance if none of the updated columns stored in the index actually change. – Paul White Jul 23 '13 at 20:15
  • @PaulWhite: but it would still update the actual table data, it just skips the index updates if I'm not mistaken – a_horse_with_no_name Jul 24 '13 at 12:16
  • 1
    @a_horse_with_no_name I wrote a whole blog post about the details of that. The details could change, of course :) – Paul White Jul 24 '13 at 14:20
1

I can't answer on the implementation for a specific database engine, as you haven't specified what exactly you are using. My experience is with Sql Server, so I can't speak for Oracle. However, you can use the where clause to update only when the value is different. The database engine will do the value comparison first, and only update rows which weren't excluded by the where clause.

An example:

UPDATE employees SET first_name = 'new_name'
WHERE COALESCE(first_name, '') <> COALESCE('new_name', '')

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