What do cursors do internally?

Question

I was optimizing a stored procedure written in cursors (DECLARE cur_name CURSOR...

The client saw this SP ran for more than a day so, my manager assigned this to me.

Upon searching, I used table variable and a while loop to iterate each record.

To my surprise, the difference between the two was huge.

Someone told me that while loop is essentially, a CURSOR.

But when I used table variable with a WHILE LOOP, it ran very very very fast.

Now, what do cursors do internally?

Answer 1

Cursors are loops, but they can be more complex for the database engine to implement depending on the options you chose.

There are many options for cursors in TSQL that can have a significant impact on performance depending on the SELECT that feeds the cursor with information (see the TSQL docs for detail). The options will change what locks and other resources are needed, could cause parts of the query to be rerun, and so forth. Also your inner loop may be doing things that affect the behaviour of the cursor by updating base tables it reads from in a way that makes the database engine need to do more work.

If we could see the two bits of code you are talking about we could be more specific.

By running the initial query into a table variable and looping through that you are essentially emulating a STATIC FAST_FORWARD READ_ONLY cursor, those options remove quite a chunk of the complexity that the engine might otherwise have to worry about. Your main SELECT is definitely run once and in one go, and your loop is not competing with other activity on the base data while it interacts with the copy in the variable.

Of course you might also find that something within your two loops (the one using the cursor and the one without) has significantly different performance metrics if they are not written exactly the same.

Answer 2

Your solution, although a WHILEloop, is essentially still a cursor in the sense that you loop over a number of records and execute one or more statements once for each record. It's not the WHILE loop in itself, but rather the coding pattern where you loop over rows in a table and execute a statement for each row. Typically, "traditional" programming languages are row-based - this is the main difference between SQL and, for instance, C#, VBA, etc.

Why your query performs better with table variables may depend on a number of potential factors that we can't deduct from your question.

What you want to do when you're eliminating cursor-based solutions is to turn them into set-based solutions. Here's a very primitive pseudo-example:

DECLARE @sum int, @val int;

DECLARE cur CURSOR FOR SELECT val FROM table;
OPEN cur;

FETCH NEXT FROM cur INTO @val;
WHILE (@@FETCH_STATUS=0) BEGIN;
    SET @sum=@sum+@val;
    FETCH NEXT FROM cur INTO @val;
END;

CLOSE cur;
DEALLOCATE cur;

SELECT @sum AS grantTotal;

.. is equivalent to the following set-based query:

SELECT SUM(val) AS grandTotal FROM table;

In the first query, we loop over a table, row-by-row. In the second query, the entire calculation is done with the entire table as a single "set", i.e. set-based. Obviously, most cursor-based patterns will be a lot more complex, particularly if they launch a stored procedure for each row in the cursor. There isn't a single trick for how to convert a cursor-based solution to set-based one.