sql 服务器 - 使用游标的存储过程速度非常慢

Question

我编写了一个存储过程，有时我需要连续多次调用。sp 使用一个游标。这是我第一次意识到为什么罗伯特·维埃拉（Robert Vieira）在我多年前读过的SS2000 Pro书中写了"光标很慢"。

你能建议一个更好的实施吗？很抱歉使用复制和粘贴而不是使用简化版本，无论如何，一般建议也很好，我不希望您重写它。

这里的代码：

CREATE  PROC sp_DuplicaDocDistrib(@ID_DIP_SRC INT ,@ID_DIP_DEST int) AS
BEGIN
    DECLARE @ID_LISTA INT
    DECLARE @ID_DISTRIB INT
    DECLARE @TIPO_DISTRIB NVARCHAR(1)
    DECLARE @NRO_COPIE NVARCHAR(50)
    DECLARE @NOTE NVARCHAR(50)
    SET @ID_LISTA  = (SELECT LAST_ID FROM SW9_SEQUENCES WHERE SEQ_NAME ='DOCN_ID_LISTA_DISTRIBUZIONE') 
       SET @NOTE  = 'Automatically distributed on  '+  convert(varchar(25), getdate(), 103) 
    DECLARE CURSOR_DOCDISTRIB CURSOR FOR    
       SELECT ID_DISTRIBUZIONE,TIPO_DISTRIBUZIONE,NRO_COPIE from DOC_LISTE_DISTRIBUZIONE BASE
       WHERE BASE.ID_DIPENDENTE = @ID_DIP_SRC
        AND NOT EXISTS ( SELECT ID_LISTA FROM DOC_LISTE_DISTRIBUZIONE
         WHERE ID_DISTRIBUZIONE = BASE.ID_DISTRIBUZIONE AND ID_DIPENDENTE = @ID_DIP_DEST)
      OPEN CURSOR_DOCDISTRIB
      FETCH NEXT FROM CURSOR_DOCDISTRIB INTO @ID_DISTRIB,@TIPO_DISTRIB,@NRO_COPIE
      WHILE @@FETCH_STATUS = 0
      BEGIN
        SET @ID_LISTA = @ID_LISTA +1
        PRINT @ID_LISTA
        PRINT @ID_DISTRIB
        INSERT INTO DOC_LISTE_DISTRIBUZIONE (ID_LISTA,ID_DISTRIBUZIONE,ID_DIPENDENTE,NRO_COPIE,TIPO_DISTRIBUZIONE,NOTE)
           VALUES (@ID_LISTA,@ID_DISTRIB,@ID_DIP_DEST,@NRO_COPIE,@TIPO_DISTRIB,@NOTE)
        FETCH NEXT FROM CURSOR_DOCDISTRIB INTO @ID_DISTRIB,@TIPO_DISTRIB,@NRO_COPIE        
      END
      CLOSE CURSOR_DOCDISTRIB
      DEALLOCATE CURSOR_DOCDISTRIB
      UPDATE SW9_SEQUENCES
            SET LAST_ID = @ID_LISTA
            WHERE SEQ_NAME = 'DOCN_ID_LISTA_DISTRIBUZIONE'    
END

Answer 1

似乎你可以在这里避免光标

请将光标替换为如下所示的语句。我没有把所有的列都放在这里，但肯定你明白了！！

INSERT INTO DOC_LISTE_DISTRIBUZIONE (ID_LISTA,ID_DISTRIBUZIONE,ID_DIPENDENTE,NRO_COPIE,TIPO_DISTRIBUZIONE,NOTE)
SELECT @ID_LISTA,ID_DISTRIBUZIONE, @ID_DIP_DEST, TIPO_DISTRIBUZIONE,NRO_COPIE 
From DOC_LISTE_DISTRIBUZIONE BASE
WHERE BASE.ID_DIPENDENTE = @ID_DIP_SRC
         AND NOT EXISTS ( SELECT ID_LISTA FROM DOC_LISTE_DISTRIBUZIONE
         WHERE ID_DISTRIBUZIONE = BASE.ID_DISTRIBUZIONE AND ID_DIPENDENTE = @ID_DIP_DEST

阅读有关将数据从一个表插入到另一个表的详细信息

如果需要增量字段，则可以使用ROW_NUMBER()来实现此目的。

即

Select ROW_NUMBER() Over (order By FieldName1) IncrementField ,FieldName2
From TableName

Answer 2

使用FAST_FORWARD游标（它是静态、forward_only、只读的简写），它们比默认值快得多。

但是，我相信您在这里根本不需要光标，它可以重写为简单的 INSERT。从中选择。

Answer 3

ID_LISTA似乎

不是 IDENTITY 列，这意味着您需要手动填充它。

您将光标声明为：

   SELECT ID_DISTRIBUZIONE,TIPO_DISTRIBUZIONE,NRO_COPIE from DOC_LISTE_DISTRIBUZIONE BASE
       WHERE BASE.ID_DIPENDENTE = @ID_DIP_SRC
         AND NOT EXISTS (
            SELECT ID_LISTA FROM DOC_LISTE_DISTRIBUZIONE
             WHERE ID_DISTRIBUZIONE = BASE.ID_DISTRIBUZIONE AND ID_DIPENDENTE = @ID_DIP_DEST
         )

但是，当您遍历光标时，您似乎正在执行的唯一处理是递增 @ID_LISTA 的值。因此，一种解决方案可能是开始创建一个临时表：

  CREATE TABLE #Temp (
     ID_LISTA_INC int IDENTITY(1,1),
     ID_DISTRIBUZIONE ...,
     ID_DIPENDENTE ...
     (etc)
  )

然后

  INSERT INTO #Temp
       (ID_DISTRIBUZIONE, ID_DIPENDENTE, ...)
       SELECT ID_DISTRIBUZIONE,TIPO_DISTRIBUZIONE,NRO_COPIE from DOC_LISTE_DISTRIBUZIONE BASE
           WHERE BASE.ID_DIPENDENTE = @ID_DIP_SRC
             AND NOT EXISTS (
                 SELECT ID_LISTA FROM DOC_LISTE_DISTRIBUZIONE
                  WHERE ID_DISTRIBUZIONE = BASE.ID_DISTRIBUZIONE AND ID_DIPENDENTE = @ID_DIP_DEST
             )

以使用要插入到DOC_LISTE_DISTRIBUZIONE中的数据填充#Temp。然后，您应该能够执行以下操作：

INSERT INTO DOC_LISTE_DISTRIBUZIONE
    (ID_LISTA,ID_DISTRIBUZIONE,ID_DIPENDENTE,NRO_COPIE,TIPO_DISTRIBUZIONE,NOTE)
    SELECT ID_LISTA_INC + @ID_LISTA, ID_DISTRIBUZIONE,ID_DIPENDENTE, ... FROM #Temp
SELECT @ID_LISTA = @ID_LISTA + max(ID_LISTA_INC) FROM #Temp
UPDATE SW9_SEQUENCES
    SET LAST_ID = @ID_LISTA
    WHERE SEQ_NAME = 'DOCN_ID_LISTA_DISTRIBUZIONE'