Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

for performance reasons, we're considering changing our standard stored procedure for saving data (INSERT and UPDATE combined in a single stored proc):

ALTER PROCEDURE [dbo].[spCustomerSave]
    (
        @CustomerID int = null,
        @CustomerName nvarchar(50),
        @New_ID int output
    )
    AS
    BEGIN

    IF EXISTS(SELECT 1 FROM tblCustomer WHERE CustomerID = @CustomerID)
        BEGIN
            UPDATE tblCustomer
            SET 
                CustomerName = @CustomerName
            WHERE
                CustomerID = @CustomerID;
                SELECT @New_ID = @CustomerID;
        END
    ELSE
        BEGIN
            INSERT INTO tblCustomer(
                Taalnaam)
            VALUES(
                @CustomerName)
                SELECT @New_ID = scope_identity();
        END
    END

into a syntax using the MERGE statement:

ALTER PROCEDURE [dbo].[spCustomerSave]
    (
        @CustomerID int = null,
        @CustomerName nvarchar(50),
        @New_ID int output
    )
    AS
    BEGIN

    SELECT @New_ID = @idTaal;

    MERGE dbo.tblCustomer as target
        USING (SELECT @CustomerID, @CustomerName) as source (CustomerID, CustomerName)
        ON (target.CustomerID = source.CustomerID)
        WHEN MATCHED THEN
            UPDATE SET 
                CustomerName = @CustomerName
        WHEN NOT MATCHED THEN
            INSERT (CustomerName)
            VALUES(source.CustomerName);
                SELECT @New_ID = scope_identity();
    END

Question:

  • is this going to benefit us performance wise? (avoiding the initial SELECT on the primary key)

  • is this proper use of the MERGE statement? Most examples show MERGE statements in a scenario to execute multiple DML operations.

share|improve this question
add comment

migrated from stackoverflow.com Nov 21 '11 at 19:53

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 2 down vote accepted

I haven't done any comparative testing of the two (yet) nor seen any articles on the topic. There is an Optimizing MERGE Statement Performance article on Technet but this doesn't include any comparisons with the update/insert syntax.

I can however suggest an improvement over your original syntax which eliminates the IF EXISTS lookup:

UPDATE 
    dbo.tblCustomer
SET 
    CustomerName = @CustomerName
WHERE
    CustomerID = @CustomerID;

IF (@@ROWCOUNT = 1)
BEGIN
    SELECT @New_ID = @CustomerID;
END
ELSE
BEGIN
    INSERT 
        dbo.tblCustomer
        (Taalnaam)
    VALUES
        (@CustomerName);

    SELECT @New_ID = SCOPE_IDENTITY();
END

You may also be interested in Mythbusting: Concurrent Update/Insert Solutions, which includes some examples of MERGE usage.

share|improve this answer
add comment

I'd suspect that your table would have to be incredibly large and improperly indexed or have hundreds-to-thousands of concurrent users running this proc (the merge uses 1 less lock) to really see any sort of performance difference between the 2 statements.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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