7

This question already has an answer here:

Question: In SQL Server 2016, does updating a column to the same value (e.g. updating a column from 'john' to 'john') produce the same amount of transaction-logging as when updating a column to a different value? Read below for more details.

We have several SQL Agent jobs running on a schedule. These jobs select data from a source-table (replicated data, linked servers), transform it, then insert/update/deletes the rows of the local target-table accordingly.

We have been through various strategies while trying to find the best way to achieve this. We've tried

  • Updating target from source using MERGE
  • Updating target from source using UPDATE to update all columns
  • Updating target from source using a single UPDATE-statement per target-column

Now, I'm only a junior DBD and my understanding of how the transaction-log works is very limited. That being said, my seniors have concluded that we cannot use the MERGE or UPDATE statements where all columns are processed in the same statement since it creates excessive logging. The argument for this is that when you perform an UPDATE-statement in SQL Server, when you set the a column-value and the new value equals the old value, it is still marked as an update in the transaction-log. This apparently becomes costly when you perform lots and lots of pointless SET-operations.

In the following example, we update the first_name and last_name of the target-table using values from the source table, joined by id.

-- create source- and target-table
CREATE TABLE [#tgt] (
    [id] Int PRIMARY KEY,
    [first_name] NVarchar(10),
    [last_name] NVarchar(10)
)
CREATE TABLE [#src] (
    [id] Int PRIMARY KEY,
    [first_name] NVarchar(10),
    [last_name] NVarchar(10)
)

-- fill some dummy-data
INSERT INTO [#tgt]([id], [first_name], [last_name])VALUES(1, 'john', 'lennon')
INSERT INTO [#src]([id], [first_name], [last_name])VALUES(1, 'john', 'cena')

-- update target-table with values from source-table
UPDATE 
    [T]
SET 
    [T].[first_name] = [S].[first_name],
    [T].[last_name] = [S].[last_name]
FROM
    [#tgt] AS [T]
    JOIN [#src] AS [S] ON [S].[id] = [T].[id]

DROP TABLE [#tgt]
DROP TABLE [#src]

This example does not check whether any values has actually changed. If we ignore NULL-checking and sane fallbacks for a moment, this could be checked in one of the following ways:

-- Example #1: updates all rows where first-name or last-name has changed
UPDATE ..
SET ..
FROM ..
WHERE [T].[first_name] <> [S].[first_name] OR [T].[last_name] <> [S].[last_name]

-- Example #2: updates all rows, sets target-value to source-value if value has changed
UPDATE ..
SET 
    ISNULL(NULLIF([S].[first_name], [T].[first_name]), [T].[first_name]),
    ISNULL(NULLIF([S].[last_name], [T].[last_name]), [T].[last_name])
FROM ..

In Example #1, the SET-operation will update all columns, even if only 1 column has changed.

In Example #2, the SET-operation will update all columns for all rows, falling back to the old value if the value is unchanged.

In both examples, all columns are hit by the SET-operation, and, according to my seniors, this creates an unnecessary/problematic amount of transaction-logging when done frequently.

The same applies for the MERGE-statement. Even if you check a matched row for changes, all columns are hit by the update.

MERGE [#tgt] AS tgt
USING [#src] AS src
ON (tgt.id = src.id)  
WHEN MATCHED AND ([tgt].[first_name] <> [src].[first_name] OR [tgt].[last_name] <> [src].[last_name])
THEN UPDATE SET 
        [tgt].[first_name] = [src].[first_name], 
        [tgt].[last_name] = [src].[last_name];

So what do we do? Use a single UPDATE-statement for each column we wish to update. In this case:

-- first_name
UPDATE [T]
SET [T].[first_name] = [S].[first_name]
FROM
    [#tgt] AS [T]
    JOIN [#src] AS [S] ON [S].[id] = [T].[id]
    WHERE [T].[first_name] <> [S].[first_name]

-- last_name
UPDATE [T]
SET [T].[last_name] = [S].[last_name]
FROM
    [#tgt] AS [T]
    JOIN [#src] AS [S] ON [S].[id] = [T].[id]
    WHERE [T].[last_name] <> [S].[last_name]

Now, there are a couple of cons to this approach:

  • All the update-statements must be executed in the same transaction in order to ensure a row is not left half-updated.
  • It really sucks to write all the code (imagine tables with 50+ columns)

It feels like there must be a smarter way around this, and I would appreciate any clarification and corrections to the statements made in this post. Like mentioned earlier, I'm just trying my best to understand why it has to be this way.

Apologies for the lengthy post and thanks in advance.

marked as duplicate by mustaccio, Marco, Evan Carroll, RLF, Paul White sql-server Mar 11 '17 at 4:15

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

2

my seniors have concluded that we cannot use the MERGE or UPDATE statements where all columns are processed in the same statement since it creates excessive logging.

Well, that's nice of them to conclude that. But, have they provided any evidence, or their test script(s), showing this behavior? I would be interested in seeing such a test ;-)

The argument for this is that when you perform an UPDATE-statement in SQL Server, when you set the a column-value and the new value equals the old value, it is still marked as an update in the transaction-log.

This is one of those cases where a little bit of knowledge is misleading. Yes, updating a column to the exact same value is considered an update, just like testing for columns being updated via the UPDATE() function will return 1 as long as the column is in the SET statement, regardless of the value changing or not.

BUT, the missing pieces are:

  1. If none of the columns are changing in value, then that row is not actually updated. And if no rows are updated at all, then the only Transaction Log activity is two records: a LOP_BEGIN_XACT to mark the beginning of the Transaction, and a LOP_COMMIT_XACT to mark the end of the Transaction. But no actual data pages or index pages are modified. This assumes that "Row(s) affected" > 0, yet nothing actually changed.

  2. If all rows are filtered out such that no rows are updated (i.e. "Row(s) affected" = 0), then there is no Tran Log activity.

  3. If any of the columns are changing in value, then additional columns being set to their existing value looks the same in the Transaction Log as not specifying the columns that are not changing in value.

  4. Every query (unless grouped with others in an explicit Transaction) is its own Transaction, and every Transaction in the Transaction Log has, at bare minimum, the 2 entries: one for the BEGIN, and one for either COMMIT or ABORT.

Ergo:

  • Your two options of "Example 1" and "Example 2" are nearly the same as far as the Tran Log is concerned. If there is at least one row to update then they should be the same. But if there are no rows where any columns are changing in value, then "Example 1" (with the WHERE clause) will result in less Tran Log activity since there will be no entries whereas in "Example 2" (all rows "updated") there will be the BEGIN and COMMIT entries. So, I would recommend using the WHERE clause as it is being explicit in your intentions, and will result in slightly less Tran Log activity.

  • Following the advice of your "seniors" is guaranteed to result in more Tran Log activity, not to mention decreased performance. Why? Because:

    • In some cases the same row will be flagged for update if both first name and last name have changed. Even if you wrap both UPDATE statements into a single Explicit Transaction to reduce inconsistency as well as extra BEGIN / END log entries, you will still be updating the row multiple times in some cases, and each modification is logged.
    • Even though the data rows are in memory already, it still takes more time to rescan them per each UPDATE statement.
  • It is always better to know for sure and to see it for yourself rather than rely on conjecture or what someone else claims. To that end, you should test your various options, including the two separate updates suggested by your seniors, and after each test, check via:

    SELECT   *
    FROM     sys.fn_dblog(NULL, NULL) tl
    ORDER BY tl.[Transaction ID] DESC; -- most recent first
    

P.S. I did my initial testing on SQL Server 2012 (SP3) Developer Edition. I then tested again on SQL Server 2016 (RTM) Express Edition and the behavior was the same.

P.P.S. Logically, [T].[first_name] = ISNULL(NULLIF([S].[first_name], [T].[first_name]), [T].[first_name]) is no different than [T].[first_name] = [S].[first_name], it's just wrapped in more functions. But if both columns are 'A', then updating that with an 'A' from the same table as opposed to an 'A' from the other table is the exact same operation.

P.P.P.S. When checking for any differences in string fields, you really need to use a binary Collation, else there could be changes in case-only (or width or combining characters, etc) such that the column's Collation will compare the values as being the same. I do realize that you mentioned those were simplified examples, but I am just making sure that this aspect is not overlooked :-). Hence:

WHERE [T].[first_name] <> [S].[first_name] OR [T].[last_name] <> [S].[last_name]

becomes:

WHERE [T].[first_name] <> [S].[first_name] COLLATE Latin1_General_100_BIN2
OR    [T].[last_name] <> [S].[last_name] COLLATE Latin1_General_100_BIN2

And:

ISNULL(NULLIF([S].[first_name], [T].[first_name]), [T].[first_name]),
ISNULL(NULLIF([S].[last_name], [T].[last_name]), [T].[last_name])

becomes:

ISNULL(NULLIF([S].[first_name] COLLATE Latin1_General_100_BIN2, [T].[first_name]), [T].[first_name]),
ISNULL(NULLIF([S].[last_name] COLLATE Latin1_General_100_BIN2, [T].[last_name]), [T].[last_name])
0

What is your logging level? You do know simple will clear the transaction log between statements?

If you want to minimize logging then multiple updates

UPDATE [T]
   SET [T].[first_name] = [S].[first_name]
  FROM [#tgt] AS [T]
  JOIN [#src] AS [S] 
    ON [T].[id] = [S].[id]
   AND [T].[first_name] != [S].[first_name];

UPDATE [T]
   SET [T].[first_name] = [S].[first_name]
  FROM [#tgt] AS [T]
  JOIN [#src] AS [S] 
    ON [T].[id] = [S].[id]
   AND [T].[first_name] is null and [S].[first_name] is not null;

UPDATE [T]
   SET [T].[first_name] = null
  FROM [#tgt] AS [T]
  JOIN [#src] AS [S] 
    ON [T].[id] = [S].[id]
   AND [T].[first_name] is not null and [S].[first_name] is null;

Then same thing for last_name

I know it seems long but it will minimize logging
The multiple updates are typically more efficient than OR

Uh, oh I just read same transaction

One or the other is going to be different a lot

What about a view

Just use the view during the update - this view should be very efficient

select case when [S].[ID] == null 
                 then [T].[first_name]
                 else [S].[first_name]
            end as [first_name]
     , case when [S].[ID] == null 
                 then [T].[last_name]
                 else [S].[last_name]
            end as [last_name]
from      [#tgt] [T] 
left join [#src] [S]
  on [t].[ID] = [S].[ID]

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