SQL Server 2008 MERGE will improve performance


One of the new features of SQL Server 2008 is the MERGE statement. Basically MERGE will insert data  to a target table from a source if the data is not there other wise it will update/delete from the target table. Using the MERGE statement will actually improves the performance a lot.

1. To further illustrate I have created a table called Mobiles as follows.

 

   CREATE TABLE Mobiles (
   ProductID   INT PRIMARY KEY,

   ManufacturerName     NVARCHAR(200), 

    Price             INT

)

 

2. I inserted the following sample data into the table 

 

INSERT
INTO Mobiles VALUES (1,‘Nokia’,10000),

                    (2,‘Samsung’,2000),

                    (3,‘Motorola’,3000)    

3. Next I created a table type which will be used as a table valued input parameter for the stored procedures that I create.

 

CREATE TYPE MobileType AS  TABLE

(

    MobileID         INT PRIMARY KEY,

    MobileName     NVARCHAR(200),

    MobilePrice     INT

)

 GO

4. Next I created a stored procedure which will insert/update the details of the Mobiles. This procedure accepts the MobileType table valued parameter that I created in the previous step as input.

CREATE
PROCEDURE UpdateMobileDetails

@Mobiles MobileType READONLY

AS

 UPDATE M
SET
M.ManufacturerName=LM.MobileName,
M.Price=LM.MobilePrice
FROM Mobiles M
INNER
JOIN @Mobiles LM
ON M.ProductID=LM.MobileID
 

INSERT
INTO Mobiles
(
    ProductID,
    ManufacturerName,
    Price
)

SELECT

   LM.MobileID,

    LM.MobileName,
    LM.MobilePrice
FROM Mobiles M
LEFT OUTER JOIN @Mobiles LM
    ON M.ProductID=LM.MobileID
WHERE M.ProductID IS NULL    

This procedure will update the records if they are already existing in the table otherwise it will insert them

5. You can achieve the same functionality using the MERGE statement of the SQL Server 2008.

CREATE
PROCEDURE UpdateMobileDetails_Merge

@Mobiles MobileType READONLY

AS

 MERGE Mobiles M
USING @Mobiles LM ON M.ProductID = LM.MobileId
WHEN MATCHED THEN
UPDATE
SET
M.ManufacturerName=LM.MobileName,
M.Price=LM.MobilePrice    
 WHEN NOT MATCHED THEN
INSERT

(

    ProductID,

    ManufacturerName,

    Price

)

VALUES(

    LM.MobileID,

    LM.MobileName,

    LM.MobilePrice    );    

If the record matches it updates, if it is not matched it will insert into the table.

6. Next I opened two separate query windows to test these two procedures and analyze the performances. I created the following test scripts in these two windows.

–To test the normal procedure

    –Test script

DECLARE @ltblMobiles MobileType

INSERT
INTO @ltblMobiles VALUES (1,‘Nokia N Series’,20000),

                        (4,‘Sony Ericssion’,5000)                                

EXEC UpdateMobileDetails    

@Mobiles=@ltblMobiles    

 

SELECT
*
FROM Mobiles

 

To test the MERGE procedure

DECLARE @ltblMobiles MobileType

INSERT
INTO @ltblMobiles VALUES (6,‘BENQ’,12000),

                        (7,‘iPhone’,5000)

                                

EXEC UpdateMobileDetails_Merge    

@Mobiles=@ltblMobiles    

 

SELECT
*
FROM Mobiles

7. Now go to the Object Explorer. Right Click the database where you have created the table in Step 1.

Right Click database->Reports->Standard Reports ->Object Execution Statistics.

Object Execution statics window will open in a new tab. In that you can see the one which uses MERGE has less Logical I/O.

 


 

 

 

Advertisements
This entry was posted in SQL Server. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s