I also wanted to compare running a cursor to running a WHILE loop that continually SELECTs the next value to be processed. I write this code quite a bit to avoid writing cursors and I was curious if it was really helping out. My test in this case was to SELECT all the distinct rates in the table and count and sum them. My WHILE loop looked like this:
SELECT @AMT = MIN(RATE) FROM TST_DATA WHERE RATE_ID = @RATE_ID AND RATE_PFX = 'H2' AND RATE > @AMT WHILE @AMT IS NOT NULL BEGIN PRINT CONVERT(VARCHAR, @AMT) SELECT @AMT = MIN(RATE) FROM TST_DATA WHERE RATE_ID = @RATE_ID AND RATE_PFX = 'H2' AND RATE > @AMT SET @SUM_AMT = @SUM_AMT + @AMT SET @RECS = @RECS + 1 END
I compared this to a cursor that looked almost identical. The cursor averaged 28ms per run and the WHILE loop averaged 1,493ms per run or over 50 times slower! If you look at the code you'll notice that the script is really written in favor of the cursor. Each SELECT statement in the WHILE loop had to scan the subset of data since I wasn't selecting indexed primary key values. However, even coding this to use indexed sequential primary key values I don't think you could overcome the performance deficit. I also think a smaller data set would have closed the performance gap.
In conclusion I'd encourage you really think through situations where you want to use a cursor. In almost all cases they will incur a performance penalty. But there are those rare occassions where they can make things really easy.
Final Note: In my last example I used a WHILE loop to sum up records. I do realize this could have been accomplished in a single SELECT statement. I think almost all my examples could have been simplified. It was difficult to write code simple enough to explain but complicated enough to illustrate the points. The code you see is my compromise."