游标和存储过程优化

Question

以下存储过程是前一段时间编写的，现在需要修改。

联系不上原开发者，我看了一下。对我来说这个过程似乎过于复杂。难道不能通过简单的更新来完成吗？谁能证明这里使用 CURSOR 是合理的吗？

ALTER PROCEDURE [settle_Stage1]
    @settleBatch int
AS

DECLARE @refDate datetime;
DECLARE @dd int;
DECLARE @uid int;

DECLARE trans_cursor CURSOR FOR 
SELECT uid, refDate FROM tblTransactions WHERE (settle IS NULL ) AND (state IN (  21, 31, 98, 99 ))
OPEN trans_cursor
FETCH FROM trans_cursor INTO @uid, @refDate
WHILE @@FETCH_STATUS = 0
   BEGIN
    SET @dd = DATEDIFF( day, @refDate, getDate())
    IF ( @dd >= '1' )
      BEGIN
        UPDATE tblTransactions
        SET settle = @settleBatch WHERE uid = @uid
      END
    FETCH FROM trans_cursor INTO @uid, @refDate
   END  
CLOSE trans_cursor
DEALLOCATE trans_cursor

Answer 1

你是对的 - 这看起来像“过程 SQL”，来自可能不了解 SQL 和设置操作的人。

将其转换为基于集合的查询应该有助于提高性能。

游标不是必需的，而且确实使存储过程过于复杂。

Answer 2

如果涉及的触发器会在多个更新的行上爆炸，那么您将需要迭代。但这仍然不能证明使用实际的游标是合理的。

执行单个更新会导致行锁，而不是基于集的更新可能导致的页锁或表锁。由于您正在缩小事务，因此程序员可能会尝试消除由大更新引起的死锁。

注意：我并不提倡这种方法，我只是提出原因。

Answer 3

简单地看一下，我根本看不出有任何理由不通过一次更新来完成此操作。也许（这是一个maaaaaybe）如果有太多记录需要更新，那么这可能是一个原因。无论如何，我会简单地更改它：

UPDATE tblTransactions
SET settle = @settleBatch
WHERE settle IS NULL 
AND [state] IN (21, 31, 98, 99)
AND DATEDIFF( day, refDate, getDate()) >= 1

编辑以下@Martin Smith评论

Answer 4

如果一次运行一条记录太慢，并且单个更新会导致阻塞和事务日志增长过多，则第三种替代方法是批处理。使用基于集合的查询，但一次运行 1000 条记录的循环（您可能需要进行实验才能找到批次的最佳大小）。