When Eliminataing Cursors Hurts Performance

Not every non-cursor so9lution is performant. I think there are at least two different situations:

1. You open a cursor and use it to modify the underlying table.
2. You open a cursor, do something, but you do not touch the underlying
at all.


I think that we need to distinguish between these two cases. I think
the popular rule of thumb that "cursors should be avoided whatever it
takes" not always applies in case 2.
Have a look at this simple script (Table Numbers stored numbers from 1
to 10000):


DROP PROCEDURE CursorTest
go
CREATE PROCEDURE CursorTest
AS
DECLARE @KeepLooping INT, @n INT, @d1 DATETIME, @d2 DATETIME
DECLARE Test1 CURSOR LOCAL FORWARD_ONLY
FOR SELECT Number
FROM dbo.Numbers
ORDER BY Number


SET @KeepLooping = 1


SET @d1 = GETDATE()


OPEN Test1


WHILE @KeepLooping = 1 BEGIN
FETCH Test1 INTO @n
IF @@FETCH_STATUS <> 0 BEGIN
SET @KeepLooping = 0
END
END


SET @d2 = GETDATE()
SELECT 'CursorTest' testname, DATEDIFF(ms, @d1, @d2) duration


CLOSE Test1
DEALLOCATE Test1
go
DROP PROCEDURE LoopTest
go
CREATE PROCEDURE LoopTest
AS
DECLARE @n INT, @d1 DATETIME, @d2 DATETIME


SET @n = -1
SET @d1 = GETDATE()


WHILE @n IS NOT NULL BEGIN
SELECT @n = MIN(Number)
FROM dbo.Numbers WHERE Number > @n
END
SET @d2 = GETDATE()
SELECT 'LoopTest' testname, DATEDIFF(ms, @d1, @d2) duration
go
EXEC LoopTest
EXEC CursorTest
EXEC LoopTest
EXEC CursorTest
EXEC LoopTest
EXEC CursorTest


testname duration
-------- -----------
LoopTest 30000


(1 row(s) affected)


testname duration
---------- -----------
CursorTest 106


(1 row(s) affected)


testname duration
-------- -----------
LoopTest 32190


(1 row(s) affected)


testname duration
---------- -----------
CursorTest 106


(1 row(s) affected)


testname duration
-------- -----------
LoopTest 29736


(1 row(s) affected)


testname duration
---------- -----------
CursorTest 106


(1 row(s) affected)