MS SQL Cursors are Evil

Alright, they’re necessary at times, but for the most part you need to really avoid them. It’s not about the coding and simplicity of using them, it’s about the impacts they have in the backend and on performance.

There are many situations where you can come out with the same results in a set based query as opposed to using a cursor. Again, not all the time, but yes for the most part. Why is it something that I strongly feel you should avoid. It’s the same reason that temporary tables, a-typical parameters (parameter sniffing), and other performance problem situations should be avoided.

In a nutshell, when cursors are used, the stored procedure must be recompiled every so often and there is a ton of overhead involved with that. So don’t use them at all costs. Cursors are great for needing to identify other fields and data per row. Where a set based query can’t accomplish that. Use them sparingly.

Last Resort
Speaking of cursor overhead, if you have to use a cursor, then make sure you have applied the right attributes by asking yourself these questions.
1) Do you need to performan an update on the cursor data? If not, then add the READONLY attribute.
2) Do you need to traverse backwards on the cursor? If not, then add the FAST_FORWARD attribute.

By adding these attributes, you have greatly reduced the overhead of the cursor and make it a much more streamlined stored procedure. I would advise that you add these as a rule because it’s not very often that you would have to do these tasks.

Another option if you have to use Cursors … do not create or try to use temporary tables within the cursor loop. For the same reasons that you’ll want to minimize using temporary tables only because you must understand that the stored procedure will request a recompile every six (6) updates to a temporary table. That can hurt performance a lot.

Another option is to declare variables outside of the cursor loop and then use them in the loop. This helps with compiling and doing extra DECLARE work. It might be small, but it’s happening.

Leave a Reply

%d bloggers like this: