CODEWITHSUNDEEP
sql-serversql-updatequery-performance
SezMe
SezMe

Reputation: 527

Performance Concern for Update Statement

I have an update procedure as follows:

UPDATE Repairs
SET TypeID = CASE WHEN @TypeID IS NULL THEN TypeID ELSE @TypeID END,
    Directions = CASE WHEN @Directions IS NULL THEN Directions ELSE @Directions END,
    LastUpdate = SYSDATETIME()
WHERE RepairID = @RepairID

I don't have a concurrency concern but Directions can be a pretty long NVarChar string. Is SQL Server smart enough not to copy the existing data onto itself or should I look for another approach?

Upvotes: 0

Views: 67

Answers (2)

sepupic
sepupic

Reputation: 8687

Is SQL Server smart enough not to copy the existing data onto itself Yes it is smart enough.

When you modify a row, SQL Server logs only a modified bytes, so when for example you change an int value from 1 to 24, SQL Server modifies only 1 byte of 4 (int value is 4 bytes long)

The change made can be written to log in two ways, as LOP_MODIFY_ROW or as LOP_MODIFY_COLUMNS, but anyway if your nvarchar value is not changed, only fixed-length part of the row will be logged (other fields you change are of fixed-length types)

It could be different in case your string was nchar(not nVARchar), in this case the order of columns in table definition matters.

And in case you did not modify LastUpdate too, there would be nothing logging at all.

If your question is not about logging but about data pages, of course if there is any change to a row (and you always modify at least LastUpdate field), the page immediately is marked as dirty page. This means that this page is different from what is stored on disk and the next checkpoin operation will write it to disk.

SQL Server is not writing every modified row to disk, the minimum amount of data that can be written to disk is a page(8Kb), but it's not immediately written to disk as soon as it was modified, it's checkpoint instead(ok, or eager writer, or lazy writer to be precise) to write dirty pages to disk periodically.

Upvotes: 2

Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14077

This could be rewritten as follows:

UPDATE Repairs
SET
    TypeID = ISNULL(@TypeID, TypeID),
    Directions = ISNULL(@Directions, Directions),
    LastUpdate = SYSDATETIME()
WHERE RepairID = @RepairID;

But it's not going to make any noticeable difference. If it's just these few columns, you then might have three separate cases to make these updates:

IF @TypeID IS NOT NULL
AND @Directions IS NOT NULL
    UPDATE Repairs
    SET
        TypeID = ISNULL(@TypeID, TypeID),
        Directions = ISNULL(@Directions, Directions),
        LastUpdate = SYSDATETIME()
    WHERE RepairID = @RepairID;
ELSE IF @TypeID IS NOT NULL
    UPDATE Repairs
    SET
        TypeID = ISNULL(@TypeID, TypeID),
        LastUpdate = SYSDATETIME()
    WHERE RepairID = @RepairID;
ELSE IF @Directions IS NOT NULL
    UPDATE Repairs
    SET
        Directions = ISNULL(@Directions, Directions),
        LastUpdate = SYSDATETIME()
    WHERE RepairID = @RepairID;
ELSE
    THROW 51000, 'Nothing to update mate!', 1;

But I doubt that this is going to make any significant difference too. It just adds too much boilerplate to your code. Keep with what you have or ISNULL()s

Upvotes: 1

Related Questions