Let's say you are called in to troubleshoot a stored procedure that is performing poorly.

You dive in to investigate and this is what you find:


Ah ha! A cursor!  It seems we have identified the bottleneck: Clearly, the performance problems are because the code is not doing things in a set-based manner, but rather by processing rows one at a time using a dreaded cursor.  This cursor is opening up the Products table, looping through the rows one at a time, and calling the "DoSomething" stored procedure for each ProductID.  As we all know, cursors are not the way to go when writing SQL code; this cursor should eliminated and replaced with a cleaner, more efficient (and more socially acceptable!) solution.

So, how we do optimize this?  Well, a commonly suggested approach is to eliminate the CURSOR by replacing it with a WHILE loop:

    declare @ProductID int
    set @ProductID = -99999

    while (@ProductID is not null)
        begin
        set @ProductID = (select top 1 ProductID
                          from Products
                          where ProductID > @ProductID
                          order by ProductID asc)

        exec DoSomething @ProductID

        end

Instead of declaring a CURSOR to loop through the table, we now are using "set-based" code and our problems seem to be solved.  The cursor is gone, our code looks much cleaner, we've tested it and it works properly, so off to production it goes.  Another cursor has been busted!

Right?

Actually ... no.

You see, eliminating cursors is not about syntax.  It is not about searching for the word "cursor" in your code and just replacing it with a WHILE loop that does the same thing.  Optimizing and replacing cursors involves much more.  We can never optimize any cursor code until we look deeper into what exactly is happening when we "process" each of those rows.  In this case, we need to find out what that "DoSomething" procedure is actually doing. 

Suppose the DoSomething procedure is generating a report and sending an email to the "Product Manager" for each product that contains status information, and then logging this email message into a table somewhere.

If that is the case, what have we just gained by replacing our CURSOR?  

Honestly -- not much,  if anything at all.  Because of the task at hand, we may very well need to process rows in the Product table one-by-one to send our emails and generate the report, and the bottleneck here is not the cursor code at all, but rather the report generation and maybe sending the email.   Eliminating the cursor code probably gains us nothing here.  If you need to process rows one at a time, go ahead and use a cursor -- that's what they are there for!   Replacing a perfectly fine, simple cursor with a WHILE loop might even make your code longer, or more confusing, or even less efficient depending on circumstances. 

For example, what if we need to process the Products ordered by Region, then Product Name, for whatever reason.  Our cursor code is simple:


All that we needed  to change was our ORDER BY clause.  Now, how would we write this as a WHILE loop?  Is it possible?  Sure.  Will it be as simple and clean as using a cursor?  No, it won't. (Though ROW_COUNT() makes this much easier than it used to be)

Now, I am not here to say that cursors are "good", but if you really need to process rows one by one, go ahead and proudly use a cursor.   Replacing cursors isn't about processing rows one-by-one in a different way (i.e., using a WHILE loop instead), it is about not processing rows one-by-one at all!   

Let's consider another scenario: What if the DoSomething stored procedure is checking to see if the Product's ExpireDate is greater than today's date, and if so, it is updates the Status column for that Product to 'X'.

In that situation, what have we gained by rewriting ProcessProducts without a cursor, and using a WHILE loop instead?   The answer is, once again: nothing!  In fact, we potentially have once again made our code more confusing or even less efficient than a cursor might be!  Remember, the bottleneck isn't the cursor syntax -- it is the fact that we are processing rows one at a time.  Replacing the cursor with the WHILE loop didn't solve this problem, did it?  

So, looking now at both of the scenarios I presented for the DoSomething stored procedure, it should be clear that we did not fix anything by replacing the cursor in either case simply by writing a WHILE loop.  If that's all you are doing, don't bother replacing the cursor at all.  You haven't optimized anything.

As I said before, the art of replacing a cursor is not a find-and-replace syntax change operation -- it is a fundamental change in how you process your data.  As in the Product report generation and email example, it may be that we simply need to process rows one by one, and thus no further optimization is possible from a SQL point of view.  In situations like updating the Product table, however, we do not need to process the rows individually -- we can do everything in one single UPDATE statement.  Thus, in order to determine how to optimize the ProcessProducts stored procedure, we needed to dig deeper into entire process as a whole, which included examining the DoSomething stored procedure and determining the full scope of exactly what this "ProcessProducts" stored procedure is doing. 

So, if "DoSomething" is updating the Products table as specified, we now know that a good replacement for our cursor code doesn't result in a WHILE loop and calling a separate stored procedure over and over at all -- it results a true, set-based solution:

And THAT is how you optimize a cursor! No loops, no calling of another stored procedure for each row in a table, no "find-and-replace" cursor code removal.  We examined the entire process, and rewrote the entire process, to get it done quicker and shorter and faster without cursors or loops. 

Always remember: Replacing a cursor isn't about rewriting your syntax, it is about redesigning your algorithm.