Cursors and While loops reply

Earlier in the year David Curlewis ( t | b ) wrote a blog post titled "cursors and while loops". If you haven't read that article please go read it now as this is basically a reply or follow-up to that post.

In his article, David showed that for the test he was using the cursor was far and away the higher performing query. The WHILE loop took 30+ seconds whereas the cursor query took closer to 0.5 seconds.

I spotted a potential improvement to make the WHILE loop behave more in line with what I expected and I think what David expected.

Lets recap

Since the hardware I am running on is no doubt different to David's and I ran my tests on SQL Server 2012 with cumulative update 2 (11.0.2325) there are going to be some timing differences. I believe I have used the same AdventureWorks version of SQL 2008 (get it from CodePlex) and I have used the scripts David used.

For each test result I have taken the average of 5 tests.

Rather than repeat ALL the cursor tests that David did I've selected the one that performed the fastest: a GLOBAL STATIC CURSOR. As I said: go read his article if haven't yet.

The CURSOR approach

------------------------------------------------
-- CURSOR
------------------------------------------------
--DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]
--     [ FORWARD_ONLY | SCROLL ]
--     [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
--     [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
--     [ TYPE_WARNING ]
CHECKPOINT;
DBCC DROPCLEANBUFFERS;
GO
SET NOCOUNT ON;
GO
DECLARE @SalesOrderID INT, @LineCount INT;
DECLARE SOID CURSOR GLOBAL STATIC TYPE_WARNING
FOR
    SELECT  SalesOrderID
    FROM    Sales.SalesOrderHeader
    WHERE   OnlineOrderFlag = 1;
 
OPEN SOID;
FETCH NEXT FROM SOID INTO @SalesOrderID;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @LineCount = ISNULL(@LineCount,0) + ISNULL((
            SELECT  COUNT(*)
            FROM    Sales.SalesOrderDetail
            WHERE   SalesOrderID = @SalesOrderID
            ), 0);
 
    FETCH NEXT FROM SOID INTO @SalesOrderID;
END
CLOSE SOID;
DEALLOCATE SOID;
IF @LineCount <> 60398
BEGIN
    RAISERROR('Bad Total',16,1);
    PRINT @LineCount;
END
GO