Today we’ll have a look at Microsoft SQL Server programming and specifically the CURSOR and MERGE statements.
A cursor can be used to loop through a set of rows programmatic-ally. Their use is discouraged because they tend to perform poorly. It’s usually faster to work with a set of records instead of ‘walking through’ a table, one row at a time.
Let’s have a look at the difference in performance between a cursor and the merge statement.
Imagine a table with stock prices (dbo.Securities) that is updated with new prices every day.
After each update, we want to move SecurityID, Price and PriceDate to the dbo.PriceHistory table.
We will need to check if the combination of SecurityID and PriceDate exists in the target table. If it doesn’t then we will INSERT a new row. If it does exist then we will have to UPDATE the existing record.
We could write a stored procedure that uses a cursor as follows:
CREATE PROCEDURE dbo.SP_InsertPriceHistory AS
SET NOCOUNT ON;
DECLARE @MySecID int;
DECLARE @MyDate date;
DECLARE @MyPrice numeric(12,4);
DECLARE MyCur CURSOR FAST_FORWARD
FOR SELECT Securityid, Price, PriceDate
FROM dbo.Securities ORDER BY SecurityID;
OPEN MyCur;
FETCH NEXT FROM MyCur INTO @MySecID, @MyPrice, @MyDate;
WHILE @@FETCH_STATUS = 0
BEGIN
–Check if record exists
IF EXISTS (SELECT * FROM dbo.PriceHistory
WHERE PriceDate = @MyDate and SecurityID = @MySecID)
BEGIN
–Record exists – Update
UPDATE dbo.PriceHistory SET PriceDate = @MyPrice
WHERE (SecurityID = @MySecID AND PriceDate = @MyDate);
END
ELSE
BEGIN
–Record does not exist – Insert
INSERT INTO dbo.PriceHistory (SecurityID, PriceDate, Price)
VALUES(@MySecID, @MyDate, @MyPrice);
END
FETCH NEXT FROM MyCur INTO @MySecID, @MyPrice, @MyDate;
END
CLOSE MyCur;
DEALLOCATE MyCur;
We can achieve the same result using the Merge statement. The stored procedure might look like this:
CREATE PROCEDURE dbo.SP_InsertPriceHistory AS
SET NOCOUNT ON;
MERGE INTO dbo.PriceHistory AS TGT
USING dbo.Securities AS SRC
ON SRC.SecurityID = TGT.SecurityID AND
SRC.PriceDate = TGT.PriceDate
WHEN MATCHED THEN
UPDATE SET TGT.Price = SRC.Price;
WHEN NOT MATCHED THEN
INSERT VALUES(SRC.SecurityID, SRC.PriceDate, SRC.Price);
SQL server will check for a match between the source and target table by comparing both the SecurityID and PriceDate columns.
If there is a match then it will run the code after WHEN MATCHED THEN
If there is no match then it means that no price is stored for this particular security on this date and it will run the code after WHEN NOT MATCHED THEN
Not only is this code easier to read, it is also much faster.
For more details on Merge have a look at Merge on Microsoft technet.
PS we can slightly improve the performance of both stored procedures by checking if an update is really necessary. We can do this by comparing the price of the source with the price of the target table. If the price is the same then we can skip the update.
serenians 