SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = 'SQL Server' : Curious cursor optimization options

I don't like cursors.  Usually there are ways to avoid them, more so in SQL 2005.  But if they exist in your environment and are causing performance problems, take a look at this article.

If you have to optimize a cursor for performance, keep the following considerations in mind:

1. Always try to replace the cursor by a set-based equivalent first. If you fail to see how, do not hesitate to ask in one of the SQL Server newsgroups.
2. If you are really stuck with a cursor, then do NOT rely on the default options. They will result in the slowest of all possible option combinations
3. If you think that the FAST_FORWARD option results in the fastest possible performance, think again. I have not found one single test case where it was faster than, or even as fast as, a STATIC cursor.
4. Do NOT use the WHERE CURRENT OF syntax of the UPDATE command. Using a regular WHERE clause with the primary key values will speed up your performance by a factor of two to three.
5. Do not rely blindly on my performance results. Remember, the one thing that is always true when working with SQL Server is: “it depends”.

SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = 'SQL Server' : Curious cursor optimization options