sql server - What are the different ways to replace a cursor?

Question

I'd like to know your experience(s) with replacing SQL Server cursors in existing code, or how you took a problem that a procedural guy would use a cursor to solve, and did it set-based.

What was the problem the cursor was used to solve? How did you replace the cursor?

Answer 1

try to never loop, work on sets of data.

you can insert, update, delete multiple rows at one time. here in an example insert of multiple rows:

INSERT INTO YourTable
        (col1, col2, col3, col4)
    SELECT
        cola, colb+Colz, colc, @X
        FROM ....
            LEFT OUTER JOIN ...
        WHERE...

When looking at a loop see what it done inside it. If it is just inserts/deletes/updates, re-write to use single commands. If there are IFs, see if those can be CASE statements or WHERE conditions on inserts/deletes/updates. If so, remove the loop and use set commands.

I've taken loops and replaced them with the set based commands and reduced the execution time from minutes to a few seconds. I have taken procedures with many nested loops and procedure calls and kept the loops (was impossible to only use inserts/deletes/updates), but I removed the cursor, and have seen less locking/blocking and massive performance boosts as well. Here are two looping methods that are better than cursor loops...

if you have to loop, over a set do something like this:

--this looks up each row for every iteration
DECLARE @msg VARCHAR(250)
DECLARE @hostname sysname

--first select of currsor free loop
SELECT @hostname= min(RTRIM(hostname))
    FROM  master.dbo.sysprocesses (NOLOCK)
    WHERE  hostname <> ''

WHILE @hostname is not null
BEGIN
    set @msg='exec master.dbo.xp_cmdshell "net send ' 
        + RTRIM(@hostname) + ' '
        + 'testing  "'
    print @msg
    --EXEC (@msg)

    --next select of cursor free loop
    SELECT @hostname= min(RTRIM(hostname))
        FROM master.dbo.sysprocesses (NOLOCK)
        WHERE  hostname <> ''
        and hostname > @hostname
END

if you have a reasonable set of items (not 100,000) to loop over you can do this:

--this will capture each Key to loop over
DECLARE @msg VARCHAR(250)
DECLARE @From   int
DECLARE @To     int
CREATE TABLE #Rows
(
     RowID     int not null primary key identity(1,1)
    ,hostname  varchar(100)
)

INSERT INTO #Rows
SELECT DISTINCT hostname
    FROM  master.dbo.sysprocesses (NOLOCK)
    WHERE  hostname <> ''
SELECT @From=0,@To=@@ROWCOUNT

WHILE @From<@To
BEGIN
    SET @From=@From+1

    SELECT @msg='exec master.dbo.xp_cmdshell "net send ' 
        + RTRIM(hostname) + ' '
        + 'testing  "'
        FROM #Rows WHERE RowID=@From
    print @msg
    --EXEC (@msg)
END