2

I'm trying improve the performance of a complicated stored procedure. Inside the stored procedure is the following MERGE statement. I'm not very familiar with the MERGE syntax.

The [Contact].[PhoneNumber] table looks like this:

CREATE TABLE [Contact].[PhoneNumber]
(
    [Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [PhoneNumber] [nvarchar](255) NOT NULL
    [InsertedAtDateTimeUTC] [datetime2](7) NOT NULL DEFAULT(sysutcdatetime())
)

The merge looks like this:

;WITH _ContactPhoneNumbers ([PhoneNumber]) AS 
(
    SELECT DISTINCT ([#ChannelData].[CallerAni])
    FROM [#ChannelData]
)
MERGE [Contact].[PhoneNumber] WITH (HOLDLOCK) _target
USING [_ContactPhoneNumbers] _source ON [_target].[PhoneNumber] = [_source].[PhoneNumber]

WHEN NOT MATCHED THEN
    INSERT ([PhoneNumber])
    VALUES ([PhoneNumber])

WHEN MATCHED THEN
    UPDATE 
        SET [_target].[PhoneNumber] = [_source].[PhoneNumber]
        OUTPUT INSERTED.[Id], [_source].[PhoneNumber] INTO #Contact_PhoneNumber_Output ([Id], [PhoneNumber]);   

I read this as:

If the PhoneNumber does not exist in the [Contact].[PhoneNumber] table the merge inserts a new row into the [Contact].[PhoneNumber] and returns the new Id. If PhoneNumber does exist then the merge updates the PhoneNumber to be the same value, and returns the Id for the existing row.

Is the UPDATE redundant?

If it's redundant I'll take it out and see if it increases the performance. There are several MERGE statements in the stored procedure which follows the same pattern (with the UPDATE) so it could be a useful gain.

The Id is returned even if it exists, as it's used as an FK in a further insert.

  • 1
    The WHEN MATCHED .. UPDATE seems redundant but you could have tried it ;) You could do the same thing with a much simple INSERT .. WHERE NOT EXISTS or an INSERT .. SELECT ... EXCEPT .. – ypercubeᵀᴹ Oct 30 '16 at 0:46
  • [PhoneNumber] in VALUES ( [PhoneNumber] ) needs to be qualified, because both _source and _target have got a column by that name. – Andriy M Oct 30 '16 at 9:03
5

Something needs to be in the WHEN MATCHED THEN clause, otherwise you don't get the Id back when the row exists.

However, you don't need to update the target table.

DECLARE @dummy int;

;WITH    _ContactPhoneNumbers ( [PhoneNumber] )
              AS ( SELECT    DISTINCT
                            ( [#ChannelData].[CallerAni] )

                   FROM     [#ChannelData]
                 )
        MERGE [PhoneNumber]  WITH ( HOLDLOCK ) _target
        USING [_ContactPhoneNumbers] _source
        ON [_target].[PhoneNumber] = [_source].[PhoneNumber]
        WHEN NOT MATCHED THEN
            INSERT ( [PhoneNumber] )
            VALUES ([PhoneNumber] )
        WHEN MATCHED THEN
            UPDATE SET @dummy = 0
        OUTPUT
            INSERTED.[Id] ,                
            [_source].[PhoneNumber]
        INTO #Contact_PhoneNumber_Output ( [Id], [PhoneNumber] );

Given the information in Use Caution with SQL Server's MERGE Statement, I'm going to ditch the MERGE completely. There's loads of LCK_M_RS_U contention too so I think I'll be better off with simpler SQL which I understand better.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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