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:
- 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.
- 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
- 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.
- 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.
- Do not rely blindly on my performance results. Remember, the one thing that is always true when working with SQL Server is: “it depends”.