SQL Server Fast Forward Cursors

Question

It is generally accepted that the use of cursors in stored procedures should be avoided where possible (replaced with set based logic etc). If you take the cases where you need to iterate over some data, and can do in a read only manner, are fast forward (read only forward) cursor more or less inefficient than say while loops? From my investigations it looks as though the cursor option is generally faster and uses less reads and cpu time. I haven't done any extensive testing, but is this what others find? Do cursors of this type (fast forward) carry additional overhead or resource that could be expensive that I don't know about.

Is all the talk about not using cursors really about avoiding the use of cursors when set-based approaches are available, and the use of updatable cursors etc.

Thanks

Answer 1

While a fast forward cursor does have some optimizations in Sql Server 2005, it is not true that they are anywhere close to a set based query in terms of performance. There are very few situations where cursor logic cannot be replaced by a set-based query. Cursors will always be inherently slower, due in part to the fact that you have to keep interrupting the execution in order to fill your local variables.

Here are few references, which would only be the tip of the iceberg if you research this issue:

http://www.code-magazine.com/Article.aspx?quickid=060113

http://dataeducation.com/re-inventing-the-recursive-cte/

Answer 2

This answer hopes to consolidate the replies given to date.

1) If at all possible, used set based logic for your queries i.e. try and use just SELECT, INSERT, UPDATE or DELETE with the appropriate FROM clauses or nested queries - these will almost always be faster.

2) If the above is not possible, then in SQL Server 2005+ FAST FORWARD cursors are efficient and perform well and should be used in preference to while loops.