What Are The Different Ways To Replace A Cursor?

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-

Solution 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:

INSERTINTO YourTable
        (col1, col2, col3, col4)
    SELECT
        cola, colb+Colz, colc, @XFROM ....
            LEFTOUTERJOIN ...
        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 iterationDECLARE@msgVARCHAR(250)
DECLARE@hostname sysname

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

WHILE @hostnameisnotnullBEGINset@msg='exec master.dbo.xp_cmdshell "net send '+ RTRIM(@hostname) +' '+'testing  "'
    print @msg--EXEC (@msg)--next select of cursor free loopSELECT@hostname=min(RTRIM(hostname))
        FROM master.dbo.sysprocesses (NOLOCK)
        WHERE  hostname <>''and hostname >@hostnameEND

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 overDECLARE@msgVARCHAR(250)
DECLARE@FromintDECLARE@TointCREATETABLE #Rows
(
     RowID     intnotnullprimary key identity(1,1)
    ,hostname  varchar(100)
)

INSERTINTO #RowsSELECTDISTINCT hostname
    FROM  master.dbo.sysprocesses (NOLOCK)
    WHERE  hostname <>''SELECT@From=0,@To=@@ROWCOUNT

WHILE @From<@ToBEGINSET@From=@From+1SELECT@msg='exec master.dbo.xp_cmdshell "net send '+ RTRIM(hostname) +' '+'testing  "'FROM #RowsWHERE RowID=@From
    print @msg--EXEC (@msg)END

Solution 2:

I've replaced some cursors with WHILE loops.

DECLARE@SomeTableTABLE
(
     ID intIDENTITY (1, 1) PRIMARY KEY NOTNULL,
     SomeNumber int,
     SomeText varchar
)

DECLARE@theCountintDECLARE@theMaxintDECLARE@theNumberintDECLARE@theTextvarcharINSERTINTO@SomeTable (SomeNumber, SomeText)
SELECT Number, Text
FROM PrimaryTable

SET@theCount=1SELECT@theMax=COUNT(ID) FROM@SomeTable

WHILE (@theCount<=@theMax)
BEGINSET@theNumber=0SET@theText=''SELECT@theNumber= IsNull(Number, 0), @theText= IsNull(Text, 'nothing')
     FROM@SomeTableWHERE ID =@theCount-- Do something.
     PRINT 'This is '+@theText+' from record '+CAST(@theNumberASvarchar) +'.'SET@theCount=@theCount+1END

PRINT 'Done'

Solution 3:

Well, often an app dev used to procedural programming will - out of habit - try to do everything procedurally, even in SQL.

Most often, a SELECT with the right paramters might do - or maybe you're dealing with an UPDATE statement.

The point really is: you need to begin to think in set operations and tell your RDBMS what you want done - not how to do it step by step.

It's hard to give a single, "right" answer to this..... you'd almost have to show it with a concrete example.

Marc

Solution 4:

I wrote some code that calculated running totals for financial data related to a given year. In each quarter, I had to add the value for the current quarter to the running total while handling NULLs appropriately so that the running total for the previous quarter carried over when the value for the current quarter was NULL.

Originally, I did this using a cursor and from a functional standpoint this met the business requirement. From a technical standpoint, it turned out to be a show-stopper because as the amount of data increased the code took exponentially longer. The solution was to replace the cursor with a correlated sub-query which met the functional requirements and eliminated any performance issues.

Hope this helps,

Bill