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

People avoid cursor because they generally are more difficult to write than a simple while loops, however, a while loop can be expensive because your constantly selecting data from a table, temporary or otherwise.

With a cursor, which is readonly fast forward, the data is kept in memory and has been specifically designed for looping.

This article highlights that an average cursor runs 50 times faster than a while loop.

Answer 2

The 'Best Practice' of avoiding cursors in SQL Server dates back to SQL Server 2000 and earlier versions. The rewrite of the engine in SQL 2005 addressed most of the issues related to the problems of cursors, particularly with the introduction of the fast forward option. Cursors are not neccessarily worse than set-based and are used extensively and successfully in Oracle PL/SQL (LOOP).

The 'generally accepted' that you refer to was valid, but is now outdated and incorrect - go on the assumption that fast forward cursors behave as advertised and perform. Do some tests and research, basing your findings on SQL2005 and later

Answer 3

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://sqlblog.com/blogs/adam_machanic/archive/2007/10/13/cursors-run-just-fine.aspx

Answer 4

If You want a even faster cursor than FAST FORWARD then use a STATIC cursor. They are faster than FAST FORWARD. Not extremely faster but can make a difference.

Answer 5

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.

Answer 6

"If You want a even faster cursor than FAST FORWARD then use a STATIC cursor. They are faster than FAST FORWARD. Not extremely faster but can make a difference."

Not so fast! According to Micro$oft: "Typically, when these conversions occurred, the cursor type degraded to a ‘more expensive’ cursor type. Generally, a (FAST) FORWARD-ONLY cursor is the most performant, followed by DYNAMIC, KEYSET, and finally STATIC which is generally the least performant."

from: http://blogs.msdn.com/b/mssqlisv/archive/2006/06/23/644493.aspx