CURSOR Tests

During another day on twitter, I started friendly discussion on about which cursor options are faster versus another. Since I love a little challenge, I decided to recreate test scenario, but to be "fair" to the cursor since I have a soft spot for them since my Oracle days, I changed code just slightly by "fixing" few things that could hinder clean fight.

First thing I did was to place both cursors into stored procedures so I can easily call it, and next I modified code to write test result to a table instead of trying to calculate them by hand after each run. At the bottom of the page is the code to setup test.

I have done "cold" testing to remove extra factors that can contribute to inconclusive results. I ran each of the following query 20 times.

Query 1

DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS 
GO
exec dbo.Cursor_KeySetTest

Query 2

DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS 
GO
exec dbo.Cursor_FASTForward

Query 3

DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS 
GO
SET NOCOUNT ON;
SELECT TOP 0 * INTO vSWA FROM Sales.vStoreWithAddresses

DECLARE @StartTime datetime2 = SYSDATETIME()

INSERT INTO vSWA
SELECT  BusinessEntityID
                , Name
                , AddressType
                , AddressLine1
                , AddressLine2
                , City
                , StateProvinceName
                , PostalCode
                , CountryRegionName 
  FROM Sales.vStoreWithAddresses;

DECLARE @EndTime datetime2 = SYSDATETIME()
DECLARE @Duration INT = DATEDIFF(millisecond,@StartTime,@EndTime)

INSERT INTO TestTimes (CallType, Duration, TestTime)
VALUES ('Set Based',@Duration, SYSDATETIME())

GO
DROP TABLE vSWA;
GO

Now for the results just a simple aggregate query from the table that I created.

Test Result Query

SELECT CallType
 ,MIN(duration) as MinDuration
 ,MAX(duration) as MaxDuration
 ,AVG(duration) as AvgDuration
 ,COUNT(*) NumberOfTries
FROM dbo.TestTimes as t
GROUP BY CallType

Results

Call Type	MinDuration	MaxDuration	AvgDuration	NumberOfTries
Cursor_FASTForward	122	390	191	20
Cursor_KeySetTest	162	398	232	20
Set Based	77	275	98	20

---

Conclusion:

In my test which was done on my desktop with Windows 10, SQL Server 2016 Developer Edition, SET BASED approach won with average duration of 98 milliseconds. Second place goes to LOCAL FAST_FORWARD with 191 millisecond average time which is twice as slow as set based approach. Third place goes to LOCAL KEYSET cursor with average duration of 232 millisecond.

In my test scenario SET BASED still faster and will likely be faster for most scenarios, so far I have only encountered 1 scenario in my work experiences where cursor was actually faster and it had to do with TSQL based ETL loading very large tables with geospatial data. Cursors have their purpose and should be used with understanding that it can create performance issues if used incorrectly. Until I see some other information I will likely stick to using LOCAL FAST_FORWARD options which seem to provide fastest CURSOR times.

---

CREATE PROC [dbo].[Cursor_FASTForward]
AS 
    BEGIN
  SET NOCOUNT ON;
        SELECT TOP 0
                *
        INTO    vSWA
        FROM    Sales.vStoreWithAddresses ;

 
        DECLARE curSWA CURSOR LOCAL FAST_FORWARD FOR
        SELECT  BusinessEntityID
                , Name
                , AddressType
                , AddressLine1
                , AddressLine2
                , City
                , StateProvinceName
                , PostalCode
                , CountryRegionName 
       FROM Sales.vStoreWithAddresses ;
 
        DECLARE @BusinessEntityID INT
          , @Name Name
          , @AddressType Name
          , @AddressLine1 NVARCHAR(60)
          , @AddressLine2 NVARCHAR(60)
          , @City NVARCHAR(30)
          , @StateProvinceName Name
          , @PostalCode NVARCHAR(15)
          , @CountryRegionName Name ;
 
        OPEN curSWA
 
        DECLARE @StartTime datetime2 = SYSDATETIME();
        FETCH NEXT FROM curSWA INTO @BusinessEntityID, @Name, @AddressType, @AddressLine1,
                    @AddressLine2, @City, @StateProvinceName, @PostalCode,
                    @CountryRegionName ;
        WHILE ( @@fetch_status <> -1 ) 
            BEGIN
                IF ( @@fetch_status <> -2 ) 
                    BEGIN
                        INSERT  INTO vSWA
                                ( BusinessEntityID
                                , Name
                                , AddressType
                                , AddressLine1
                                , AddressLine2
                                , City
                                , StateProvinceName
                                , PostalCode
                                , CountryRegionName
                                )
                        VALUES  ( @BusinessEntityID
                                , @Name
                                , @AddressType
                                , @AddressLine1
                                , @AddressLine2
                                , @City
                                , @StateProvinceName
                                , @PostalCode
                                , @CountryRegionName
                                )
                    END
                FETCH NEXT FROM curSWA INTO @BusinessEntityID, @Name, @AddressType, @AddressLine1,
                    @AddressLine2, @City, @StateProvinceName, @PostalCode,
                    @CountryRegionName ;
            END

        DECLARE @EndTime datetime2 = SYSDATETIME()
  DECLARE @Duration INT = DATEDIFF(millisecond,@StartTime,@EndTime)
  INSERT INTO dbo.TestTimes (CallType, Duration, TestTime)
  VALUES (OBJECT_NAME(@@PROCID),@Duration, SYSDATETIME())
 
        CLOSE curSWA ;
        DEALLOCATE curSWA ;

        DROP TABLE vSWA ;

    END
GO
CREATE PROC [dbo].[Cursor_KeySetTest]
AS 
    BEGIN
  SET NOCOUNT ON;

        SELECT TOP 0
                *
        INTO    vSWA
        FROM    Sales.vStoreWithAddresses ;
 
        DECLARE curSWA CURSOR LOCAL KEYSET FOR
        SELECT  BusinessEntityID
                , Name
                , AddressType
                , AddressLine1
                , AddressLine2
                , City
                , StateProvinceName
                , PostalCode
                , CountryRegionName FROM Sales.vStoreWithAddresses ;
 
        DECLARE @BusinessEntityID INT
          , @Name Name
          , @AddressType Name
          , @AddressLine1 NVARCHAR(60)
          , @AddressLine2 NVARCHAR(60)
          , @City NVARCHAR(30)
          , @StateProvinceName Name
          , @PostalCode NVARCHAR(15)
          , @CountryRegionName Name ;
 
        OPEN curSWA
 
        DECLARE @StartTime datetime2 = SYSDATETIME();

        FETCH NEXT FROM curSWA INTO @BusinessEntityID, @Name, @AddressType, @AddressLine1,
                    @AddressLine2, @City, @StateProvinceName, @PostalCode,
                    @CountryRegionName ;
        WHILE ( @@fetch_status <> -1 ) 
            BEGIN
                IF ( @@fetch_status <> -2 ) 
                    BEGIN
                        INSERT  INTO vSWA
                                ( BusinessEntityID
                                , Name
                                , AddressType
                                , AddressLine1
                                , AddressLine2
                                , City
                                , StateProvinceName
                                , PostalCode
                                , CountryRegionName
                                )
                        VALUES  ( @BusinessEntityID
                                , @Name
                                , @AddressType
                                , @AddressLine1
                                , @AddressLine2
                                , @City
                                , @StateProvinceName
                                , @PostalCode
                                , @CountryRegionName
                                )
                    END
                FETCH NEXT FROM curSWA INTO @BusinessEntityID, @Name, @AddressType, @AddressLine1,
                    @AddressLine2, @City, @StateProvinceName, @PostalCode,
                    @CountryRegionName ;
            END

        DECLARE @EndTime datetime2 = SYSDATETIME()
  DECLARE @Duration INT = DATEDIFF(millisecond,@StartTime,@EndTime)
  INSERT INTO dbo.TestTimes (CallType, Duration, TestTime)
  VALUES (OBJECT_NAME(@@PROCID),@Duration, SYSDATETIME())
 
        CLOSE curSWA ;
        DEALLOCATE curSWA ;

 
        DROP TABLE vSWA ;

    END
GO
CREATE TABLE dbo.TestTimes
(
 ID INT IDENTITY(1,1) PRIMARY KEY
 ,CallType VARCHAR(150)
 ,Duration INT
 ,TestTime DateTime2
)
GO