T-SQL Cursor vs Merge

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 programmatically.  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.  On a table with approx. 2500 records, the cursor took 124 milliseconds to finish.  The merge statement finished in 32 milliseconds.

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.

iPod game programming.

Two of my kids bought an iPod touch and I was impressed with the display and how well the touch screen responded to finger movements.  I wanted one too!  So I bought myself a present (an iPod touch 4th generation) and thought it might be fun to learn how to program it.  I had an idea for a game with a Jetpac.

I installed Xcode, Apple's free development suite and chose Cocos 2D as the graphics library after reading some good reviews.  Cocos is great.  It's easy to use, robust and comes with great 2D support.  It even allows you to use a physics engine in your game but I wanted to keep things simple and chose not to include this.  After all, a flying Jetpac is not rocket science...

I've done some game programming before on the PC using Visual C++ and DirectX and I was very impressed with the simplicity and beauty of the Xcode editor and compiler.  It comes with a useful iPod simulator (so you can quickly test the game while you are coding) but creating games is much more rewarding when you can play them on a real iPod.

Unfortunately this is not possible unless you sign up for Apple's IOS Developer Program which costs EUR 80 per year.  Needless to say, I signed up.  Another benefit of the IOS Developer Program is that it allows you to submit your apps to the iTunes store (after Apple has checked it).

It was fun learning the Cocos 2D library and the dialect of C that Apple calls Objective C.  I now have the beginnings of an actual game.  It's a simple platform game called 'Jetpac Piggy' because I like Jetpacs (and piggies).  Some of the highlights are:

• Parallax scrolling (background layers move at different speeds depending on how far away they're supposed to be - giving the impression of depth).
• 5 levels (so far).
• Realistic Jetpac thrust, created with a particle designer.
• Simple controls.  If you tilt the iPod left, space-piggy will fly left, and tilting it to the right will err... make it fly to the left some more.  Wait!  I can fix this! 
• There are 2 buttons.  One is for thrust, the other is for using one of the space shields.  Useful when you get too close to a space mine.
• iPod graphics are fast.  60 frames per second with 2 full-screen, scrolling backgrounds and various sprites and particles is not a problem at all.

Here is a screenshot (apologies to NASA for the egg-shaped planet - lets just say the visuals are distorted by the strong gravitational field on this particular planet):

More to follow...