How bad is overupdating?

Question

I have the following query, I was wondering how bad is CASE construct, It forces DB Engine to overwrite E.EAOpID with the value that is already contained in E.EAOpID when it is not null

UPDATE E
SET E.EAOpID =  CASE
                    WHEN E.EAOpID IS NULL THEN @operationID
                    ELSE E.EAOpID
                END,
    E.AverageCapacity = E.AverageCapacity + 1,
    E.Average = E.Average - (E.Average - E.Value) / E.AverageCapacity
FROM
(
    SELECT E.EAOpID, E.AverageCapacity, E.Average, P.Value
    FROM Probes AS P
    INNER JOIN Estimates AS E ON P.EstimateID = E.ID
    WHERE P.EAOpID = @operationID
) AS E;

Maybe it is cheaper to split this UPDATE into two UPDATES:

1

UPDATE E
SET E.EAOpID = @operationID
FROM
(
    SELECT E.EAOpID, E.AverageCapacity, E.Average, P.Value
    FROM Probes AS P
    INNER JOIN Estimates AS E ON P.EstimateID = E.ID
    WHERE   P.EAOpID = @operationID
        AND E.EAOpID IS NULL -- Additional statement here
) AS E;

2

UPDATE E
SET E.AverageCapacity = E.AverageCapacity + 1,
    E.Average = E.Average - (E.Average - E.Value) / E.AverageCapacity
FROM
(
    SELECT E.EAOpID, E.AverageCapacity, E.Average, P.Value
    FROM Probes AS P
    INNER JOIN Estimates AS E ON P.EstimateID = E.ID
    WHERE P.EAOpID = @operationID
) AS E;

Why don't you measure the performance of each using Profiler. That is really the only way to tell for sure what your query performance will be and how many reads/writes will be done. — Randy Minder, Jul 13 '11 at 17:52
Ok I see, thanks (There are currently no indexes, but it's not a problem to add them, I was concerned about theoretical issues) — Lu4, Jul 13 '11 at 18:10
In addition to Randy's point about testing, I think you'll find that in a lot of cases (unless there are many indexes that reference that column), checking if the value is different is going to be at least as expensive as just overwriting it without checking - especially if you end up having to overwrite it in a good number of instances anyway. — Aaron Bertrand, Jul 13 '11 at 18:52
Regarding "checking if the value is different" It's not about speed, It's about the logic of the query, I need to check it because I don't want it to be changed only in separate cases — Lu4, Jul 13 '11 at 21:10

score 1 · Answer 1 · answered Jul 13 '11 at 20:02

Updating a row even if no values have changed involves very few resources. You could impose more load just by checking for the changes. You might I suppose consider an optimization like this when your system is highly refined and you're riding close to the edge, but it would be low on the list.

score 1 · Answer 2 · answered Jul 13 '11 at 20:08

Two updates would use almost twice as many resources, since you'd be reading/updating the same set of rows. Beter to have everything in one query. I don't know how to measure how much extra processing is required by the case statement, but I do know the following performs the same amount of work using less coding logic. Change it from

UPDATE E
 SET E.EAOpID =  CASE
                     WHEN E.EAOpID IS NULL THEN @operationID
                     ELSE E.EAOpID
                 END, 
 (etc)

to

UPDATE E
 SET E.EAOpID = isnull(E.EAOpID, @operationID), 
 (etc)

(You can use coalesce instead of isnull if it makes you happy.)

Yes, I also think that using two updates is more resource consumptive operation, thanks for ISNULL(...) by the way... — Lu4, Jul 13 '11 at 21:05

score 0 · Answer 3 · answered Jul 13 '11 at 19:44

The more features of SQL Server you use on the table, the more overhead there is, even with a no-op UPDATE.

Operations and extra reads are performed beyond index maintenance.

Triggers (triggers are fired), Foreign keys (integrity is still checked), Constraints (rules are checked), etc.

As an example: add a constraint to a table that would fail with some of the existing values, but using "WITH NOCHECK" to create. Update an existing column to itself; the update fails on the constraint even though the value did not change.

How bad is overupdating?

1

2

3 Answers3