更换光标有哪些不同的方法？

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.

我想知道您在现有代码中替换SQL Server游标的经验,或者您是如何解决程序人员使用游标解决的问题,并且是基于集合的。

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

光标用来解决的问题是什么?你是怎么更换光标的?

4 个解决方案

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

在查看循环时,看看它内部做了什么。如果只是插入/删除/更新,请重写以使用单个命令。如果有IF,请查看这些是否可以是插入/删除/更新的CASE语句或WHERE条件。如果是这样,请删除循环并使用set命令。

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:

如果你有一套合理的项目(不是100,000)来循环,你可以这样做:

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

我用WHILE循环替换了一些游标。

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.

好吧,通常用于程序编程的app dev会出于习惯 - 尝试在程序上完成所有操作,即使在SQL中也是如此。

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

大多数情况下,具有正确参数的SELECT可能会 - 或者您正在处理UPDATE语句。

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.

关键在于:您需要开始考虑设置操作并告诉您的RDBMS您想要做什么 - 而不是如何逐步完成。

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.

我写了一些代码来计算与给定年份相关的财务数据的运行总计。在每个季度中,我必须将当前季度的值添加到运行总计中,同时适当地处理NULL,以便当前季度的值为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

#1