替换游标的不同方法是什么？ [英] What are the different ways to replace a cursor?

问题描述

我想知道您在现有代码中替换SQL Server游标的体验，或者如何解决过程性用户使用游标来解决问题，以及是否基于设置。

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?

推荐答案

尝试永不循环，处理数据集。

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

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

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

这篇关于替换游标的不同方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！