cursor vs while loop in sql

Here's a short post regarding the use of CURSOR vs WHILE LOOP in T-SQL. I often hear from colleagues and read from articles that it's best to use while loops and avoid cursors as much as possible since cursor has some performance issues specially when looping on huge amount of data.

We'll, only one way to find out is to put these two solutions on a test!

To start with, start creating this table below; we will be using this to loop through records:

CREATE TABLE FamousCharacter
        (
           NameID int IDENTITY(1,1) PRIMARY KEY CLUSTERED, 
           Name nvarchar(50)
        )

Then, let's put some data and loop through these records. Run this script below to insert records to theFamousCharacter table we just created.

INSERT INTO FamousCharacter VALUES('Bart Simpson')
INSERT INTO FamousCharacter VALUES('Homer Simpson')
INSERT INTO FamousCharacter VALUES('Beast')
INSERT INTO FamousCharacter VALUES('Belle')
INSERT INTO FamousCharacter VALUES('Scooby Doo')
INSERT INTO FamousCharacter VALUES('Shaggy')
INSERT INTO FamousCharacter VALUES('Tom')
INSERT INTO FamousCharacter VALUES('Jerry')
INSERT INTO FamousCharacter VALUES('Sylvester')
INSERT INTO FamousCharacter VALUES('Tweety')
INSERT INTO FamousCharacter VALUES('Garfield')
INSERT INTO FamousCharacter VALUES('John')
INSERT INTO FamousCharacter VALUES('Mickey')
INSERT INTO FamousCharacter VALUES('Minnie')
INSERT INTO FamousCharacter VALUES('Peter Pan')
INSERT INTO FamousCharacter VALUES('Tinkerbell')
INSERT INTO FamousCharacter VALUES('Fred Flintstone')
INSERT INTO FamousCharacter VALUES('Wilma Flintstone')
INSERT INTO FamousCharacter VALUES('Daffy Duck')
INSERT INTO FamousCharacter VALUES('Bugs Bunny')
INSERT INTO FamousCharacter VALUES('Tarzan')
INSERT INTO FamousCharacter VALUES('Jane')
INSERT INTO FamousCharacter VALUES('Popeye')
INSERT INTO FamousCharacter VALUES('Olive')
INSERT INTO FamousCharacter VALUES('Mr. Potato head')
INSERT INTO FamousCharacter VALUES('Mrs. Potato head')
INSERT INTO FamousCharacter VALUES('Stitch')
INSERT INTO FamousCharacter VALUES('Lilo')
INSERT INTO FamousCharacter VALUES('Jack')
INSERT INTO FamousCharacter VALUES('Jill')
INSERT INTO FamousCharacter VALUES('Batman')
INSERT INTO FamousCharacter VALUES('Robin')
INSERT INTO FamousCharacter VALUES('Woody')
INSERT INTO FamousCharacter VALUES('Buzz')
INSERT INTO FamousCharacter VALUES('Pooh')
INSERT INTO FamousCharacter VALUES('Piglet')
INSERT INTO FamousCharacter VALUES('Shrek')
INSERT INTO FamousCharacter VALUES('Donkey')
INSERT INTO FamousCharacter VALUES('Road Runner')
INSERT INTO FamousCharacter VALUES('Wile E. Coyote')
INSERT INTO FamousCharacter VALUES('Superman')
INSERT INTO FamousCharacter VALUES('Ultimateman')
INSERT INTO FamousCharacter VALUES('Eric Theodore Cartman')
INSERT INTO FamousCharacter VALUES('Kenny McCormick')
INSERT INTO FamousCharacter VALUES('Casper')
INSERT INTO FamousCharacter VALUES('Richie Rich')
INSERT INTO FamousCharacter VALUES('Professor X')
INSERT INTO FamousCharacter VALUES('Magneto')
INSERT INTO FamousCharacter VALUES('Wolverine')
INSERT INTO FamousCharacter VALUES('Sabretooth')
INSERT INTO FamousCharacter VALUES('Jughead')
INSERT INTO FamousCharacter VALUES('Archie')
INSERT INTO FamousCharacter VALUES('Linus van Pelt')
INSERT INTO FamousCharacter VALUES('Lucy van Pelt')

These scripts inserts a total of 54 rows that we can test. Our objective is to see the performance difference between cursor and while loop. The small difference on scanning these set of rows would mean huge performance hits on larger tables having millions of rows.

Let's start!

Approach #1: Cursor
Run this script below and see the execution time it spent on the lower right corner of SQL Server status bar:

SET NOCOUNT ON
DECLARE my_cursor CURSOR FOR SELECT NameID,Name FROM FamousCharacter
DECLARE @id INT
DECLARE @name NVARCHAR(50)
OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @id,@name
    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT  (CAST(@id AS VARCHAR(5)) + '.)' + @name)       
        FETCH NEXT FROM my_cursor
    END
CLOSE my_cursor   
DEALLOCATE my_cursor








Total execution time using cursor:



Approach #2: While Loop
Run this script below and again, let's check the execution time spent using WHILE loop.

DECLARE @Rows INT, @id1 INT
DECLARE @name1 NVARCHAR(50)
SET @Rows = 1
SET @id1 = 0

WHILE @Rows > 0
BEGIN
    SELECT TOP 1 @id1 = NameID, @name1 = Name FROM FamousCharacter WHERE NameID >= @id1
    SET @Rows = @@ROWCOUNT
    PRINT  (CAST(@id1 AS VARCHAR(5)) + '.)' + @name1)
    SET @id1 += 1
END

Total execution time:


While loop obviously completed the execution in less than a second! Now to exaggerate a bit, I tried re-inserting the rows and duplicated it up to 379 rows. Here's the result:

Cursor completed execution in 29 seconds!

While completed in 16 seconds!