"Cursors Run Just Fine"

Adam Machanic — Sat, 13 Oct 2007 13:42:00 GMT

I found Linchi's recent post on use of cursors in the TPC-E test to be quite interesting. The question is, why are cursors used in the test when the commonly accepted notion within the SQL Server community is that cursors are a bad thing?

I've posted in the past about situations where cursors were actually faster than set-based queries. But in this case I just don't see it; cursoring over an input set to do an update? There's no way that's going to be faster.

Greg Linwood commented in Linchi's post that "indexed cursors run just fine for most purposes". And although I have loads of respect for Greg and his opinions, I just can't agree in this case. I did a few tests on my end just to make sure, and indexed or not, even for the simplest of of queries, cursors perform at least a few times more slowly than their set-based equivalents. Greg mentioned in this comment that the SQL Server engine executes even set-based queries "internally using cursor style processing", but a loop in the query processor's code is clearly not the same as a T-SQL cursor.

The query processor is optimized internally to process data without having to pass it around to different spots in memory or switch context, whereas with a cursor the data is transferred into local variables and your code has to constantly ask the query processor to go back and get some more. This is extremely expensive, which is why even in my experiments with situations where you can see superior performance with cursors, I found that a SQLCLR cursor--which doesn't have to do nearly as much work as a T-SQL cursor--is vastly superior.

I'll close with a simple example. The following two batches each run in AdventureWorks, and indexes are irrelevant in both cases. See for yourself which is faster.

--Set-based
SELECT SUM(Quantity)
FROM Production.TransactionHistory
GO

--Cursor-based
DECLARE @q INT
INT @t INT
SET @t = 0

DECLARE c CURSOR
LOCAL FAST_FORWARD
FOR
SELECT Quantity
FROM Production.Transactionhistory

OPEN c

FETCH NEXT FROM c INTO @q

WHILE @@FETCH_STATUS = 0
BEGIN
SET @t = @t + @q

FETCH NEXT FROM c INTO @q
END

CLOSE c
DEALLOCATE c

SELECT @t
GO

Adam Machanic : cursors

"Cursors Run Just Fine"