使用游标的存储过程非常缓慢

I wrote a stored procedure that sometimes i need to call many times in a row. The sp uses one cursor. It is the first time I realize why Robert Vieira wrote "Cursors are slow" in his SS2000 Pro book i read years ago.

我编写了一个存储过程，有时需要连续调用多次。sp使用一个游标。这是我第一次意识到为什么罗伯特·维埃拉(Robert Vieira)在他的SS2000 Pro笔记本上写的“游标速度慢”。

Could you suggest a better implementation? I am sorry to use copy and paste and not to use a simplified version, anyway a general suggestion is fine too I don't expect you to rewrite it.

你能提出更好的实施方案吗?我很抱歉用的是复制和粘贴，而不是简化版，总之，一般的建议也可以，我不希望你重写它。

here the code:

下面的代码:

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

3 个解决方案

#1

Use FAST_FORWARD cursors (it's a shorthand for static, forward_only, read-only), they're much faster than defaults.

使用FAST_FORWARD游标(它是静态、只转发、只读的简写)，它们比默认游标快得多。

However, I believe that you don't need a cursor here at all, it could be rewritten as a simple INSERT.. SELECT FROM.

但是，我认为您根本不需要光标，它可以重写为一个简单的插入。选择从。

#2

Seems you can avoid the cursor here

看起来你可以避开光标

Please replace your cursor with statement like below.. I haven't put all the columns here but sure you get the idea!!

请用如下所示的语句替换光标。我还没把所有的专栏都写在这里，但你一定明白我的意思!

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

阅读有关从一个表到另一个表插入数据的更多信息

If you need incremental field, then you can use ROW_NUMBER() to achieve this.

如果您需要增量字段，那么您可以使用ROW_NUMBER()来实现这一点。

i.e.

即。

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

#3

It appears that ID_LISTA is not an IDENTITY column, meaning you will need to populate this manually.

ID_LISTA似乎不是标识列，这意味着您需要手动填充它。

You are declaring your cursor as:

您将光标声明为:

   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
         )

However, when you loop through your cursor, the only processing you appear to be doing is to increment the value of @ID_LISTA. One solution therefore may be to start off creating a temporary table:

但是，当您循环遍历游标时，您似乎正在做的惟一处理是增加@ID_LISTA的值。因此，一种解决办法可能是开始创建临时表:

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

Then,

然后,

  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
             )

to populate #Temp with the data you are inserting into DOC_LISTE_DISTRIBUZIONE. You should then be able to do:

将插入到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'

#1