SQL Server：游标性能

Question

我在存储过程中使用游标，除了游标之外没有其他方法。

我已将我的脚本粘贴在这里。我想提高这个游标的性能。 一点背景。我传递“名称”和“操作”，光标正在调用不同的存储过程作为返回。我在所有这些存储过程中传递了另一个参数 (ID)。请提高性能。

ALTER PROCEDURE dbo.Driver_sp
AS

TRUNCATE TABLE Driver_tbl

INSERT INTO Driver_tbl (ID,Name,Action,vipbatchid,processseq)
SELECT ID,Name,Action,batchid,ProcessSeq
FROM pcis_tbl


DECLARE @name varchar(20),
        @action varchar(20),
        @ID bigint

DECLARE cur CURSOR LOCAL FOR
    SELECT id,name,action FROM Driver_tbl
    ORDER BY processseq

OPEN cur  

FETCH NEXT FROM cur INTO @id, @name,@action

WHILE @@FETCH_STATUS = 0

BEGIN

   if (@name = 'A' and @action = 'Update')  
   BEGIN exec A_sp @ID END 
     else 
     if (@name = 'B' and @action = 'Update') 
     BEGIN exec B_sp @ID END 
       else
       if (@name = 'C' and @action = 'Update')
       BEGIN exec C_sp @ID END 
         else 
         if (@name = 'D' and @action = 'Update') 
         BEGIN exec D_sp @ID END 


fetch next FROM cur INTO @id, @name,@action

END
CLOSE cur
DEALLOCATE cur

谢谢 ！！

Answer 1

假设您使用的是 TSQL，请将 CURSOR 声明更改为

DECLARE cur CURSOR FAST_FORWARD FOR