更新游标性能较慢

Question

我正在运行这段代码来根据前一天(昨天)更新我的仓库数量。它是有效的，但速度非常慢。每天，系统都会生成大约50k+行，每行对应仓库上的一个地址(位置)。因此，光标获取每个地址，然后搜索前一天的数量，然后更新。

示例：

2019年8月4日地址01B134010数量为500

2019年9月4日的地址01B134010数量为450

因此，当代码运行时，它用-50更新"DIF_DIA“列

今天，我运行了代码，仅一个分支(47k+行)就花了1个多小时。

有没有更好/更快的方法呢？

DECLARE @ARMAZEM    VARCHAR(2),
        @FILIAL     VARCHAR(3),
        @CLIENTE    VARCHAR(6),
        @GRPESTOQUE VARCHAR(6),
        @DATA       AS VARCHAR(8),
        @ENDERECO as VARCHAR(30),
        @DIFERENCA AS FLOAT

-- reinicia variaveis
SET @armazem = ''
SET @FILIAL = ''
SET @CLIENTE = ''
SET @GRPESTOQUE = ''
SET @DATA = ''
SET @ENDERECO = ''
SET @DIFERENCA = ''

DECLARE CUR_UPD_DIF_DIA CURSOR FOR
  SELECT CODIGO_ARMAZEM,
         CODIGO_FILIAL,
         CODIGO_CLIENTE,
         GRP_EST_COD,
         DATA_SALDO,
         CODIGO_ENDERECO
  FROM   WMS_OCUPACAO_ARMAZEM_DETALHE_V2_ESTATICO
  WHERE  DATA_SALDO >= '20190401'
  and CODIGO_FILIAL = '106'
         AND DIF_DIA IS NULL
  FOR UPDATE OF DIF_DIA

OPEN CUR_UPD_DIF_DIA

FETCH NEXT FROM CUR_UPD_DIF_DIA INTO @ARMAZEM,
                               @FILIAL,
                               @CLIENTE,
                               @GRPESTOQUE,
                               @DATA, 
                               @ENDERECO

WHILE @@FETCH_STATUS = 0
  BEGIN

      -- pega a quantidade deste endereço ontem
      SELECT @DIFERENCA = IsNull(QUANTIDADE_PALLET,0)
      FROM   WMS_OCUPACAO_ARMAZEM_DETALHE_V2_ESTATICO T1
      WHERE  T1.DATA_SALDO = dateadd(day, -1 , @DATA)
             AND T1.GRP_EST_COD     = @GRPESTOQUE
             AND T1.CODIGO_CLIENTE  = @CLIENTE
             AND T1.CODIGO_ARMAZEM  = @ARMAZEM
             AND T1.CODIGO_ENDERECO = @ENDERECO
             AND T1.CODIGO_FILIAL   = @FILIAL

      -- e atualiza o endereço na data de hoje, tendo o valor da DIF_DIA a quantidade atual - a quantidade de ontem, assim, tem a diferença.
      -- se positivo, incrementou o endereço. Se negativo, diminuiu do endereço. Válido, na prática, somente para BLOCADO pois PP sempre terá apenas "1" de quantidade
      UPDATE WMS_OCUPACAO_ARMAZEM_DETALHE_V2_ESTATICO
      SET    DIF_DIA = (QUANTIDADE_PALLET - @DIFERENCA)
      WHERE  CURRENT OF CUR_UPD_DIF_DIA

      SET @DIFERENCA = 0

      FETCH NEXT FROM CUR_UPD_DIF_DIA INTO @ARMAZEM,
                                     @FILIAL,
                                     @CLIENTE,
                                     @GRPESTOQUE,
                                     @DATA, @ENDERECO
  END

CLOSE CUR_UPD_DIF_DIA

DEALLOCATE CUR_UPD_DIF_DIA

Answer 1

基于集合的方法会比像这样的游标性能好几个数量级。

根据您的查询，基于集合的方法可能如下所示。免责声明:该查询未经测试。

UPDATE WMS_OCUPACAO_ARMAZEM_DETALHE_V2_ESTATICO
SET    DIF_DIA = (QUANTIDADE_PALLET - (
    SELECT IsNull(QUANTIDADE_PALLET,0)
    FROM   WMS_OCUPACAO_ARMAZEM_DETALHE_V2_ESTATICO T1
    WHERE  T1.DATA_SALDO = dateadd(day, -1 , WMS_OCUPACAO_ARMAZEM_DETALHE_V2_ESTATICO.DATA_SALDO)
    AND    T1.GRP_EST_COD     = WMS_OCUPACAO_ARMAZEM_DETALHE_V2_ESTATICO.GRP_EST_COD
    AND    T1.CODIGO_CLIENTE  = WMS_OCUPACAO_ARMAZEM_DETALHE_V2_ESTATICO.CODIGO_CLIENTE
    AND    T1.CODIGO_ARMAZEM  = WMS_OCUPACAO_ARMAZEM_DETALHE_V2_ESTATICO.CODIGO_ARMAZEM
    AND    T1.CODIGO_ENDERECO = WMS_OCUPACAO_ARMAZEM_DETALHE_V2_ESTATICO.CODIGO_ENDERECO
    AND    T1.CODIGO_FILIAL   = WMS_OCUPACAO_ARMAZEM_DETALHE_V2_ESTATICO.CODIGO_FILIAL
))
WHERE  DATA_SALDO >= '20190401'
and CODIGO_FILIAL = '106'
AND DIF_DIA IS NULL

Answer 2

这是另一种优化更新的方法，它只需执行一次操作，而不是逐行运行。希望我的逻辑是正确的。

UPDATE atual
SET    DIF_DIA = atual.QUANTIDADE_PALLET - IsNull(ontem.QUANTIDADE_PALLET,0)
FROM WMS_OCUPACAO_ARMAZEM_DETALHE_V2_ESTATICO atual
LEFT
JOIN WMS_OCUPACAO_ARMAZEM_DETALHE_V2_ESTATICO ontem ON atual.DATA_SALDO      = dateadd(day, 1 , ontem.DATA_SALDO)
                                                    AND atual.GRP_EST_COD     = ontem.GRP_EST_COD    
                                                    AND atual.CODIGO_CLIENTE  = ontem.CODIGO_CLIENTE 
                                                    AND atual.CODIGO_ARMAZEM  = ontem.CODIGO_ARMAZEM 
                                                    AND atual.CODIGO_ENDERECO = ontem.CODIGO_ENDERECO
                                                    AND atual.CODIGO_FILIAL   = ontem.CODIGO_FILIAL  
WHERE atual.DATA_SALDO >= '20190401'
AND   atual.CODIGO_FILIAL = '106'
AND   atual.DIF_DIA IS NULL;