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