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-based.

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

回答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

回答2:

I've replaced some cursors with WHILE loops.

DECLARE @SomeTable TABLE
(
     ID int IDENTITY (1, 1) PRIMARY KEY NOT NULL,
     SomeNumber int,
     SomeText varchar
)

DECLARE @theCount int
DECLARE @theMax int

DECLARE @theNumber int
DECLARE @theText varchar

INSERT INTO @SomeTable (SomeNumber, SomeText)
SELECT Number, Text
FROM PrimaryTable

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

WHILE (@theCount <= @theMax)
BEGIN

     SET @theNumber = 0
     SET @theText = ''

     SELECT @theNumber = IsNull(Number, 0), @theText = IsNull(Text, 'nothing')
     FROM @SomeTable
     WHERE ID = @theCount

     -- Do something.
     PRINT 'This is ' + @theText + ' from record ' + CAST(@theNumber AS varchar) + '.'

     SET @theCount = @theCount + 1

END

PRINT 'Done'

回答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

回答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