sql - 如何使 T-SQL 游标更快？

Question

我在 SQL Server 2000 下的存储过程中有一个游标(现在无法更新)，可以更新所有表，但通常需要几分钟才能完成。我需要让它更快。

GDEPO:入口仓库，CDEPO:导出仓库，Adet:数量，已使用的 E_CIKAN 数量。

记录说明:

1. 20 辆车辆进入仓库 01，
2. 10 单位留下 01。
3. 5 个单位离开 01(第一个记录的 E_CIKAN 现在为 15)
4. 还有 10 辆车辆进入仓库 01。
5. 3 个单元从第 1 条记录中留下 01。请注意，现在第 1 条记录的 E_CIKAN 设置为 18。
6. 这就是问题所在:3 个单位需要离开仓库 01。需要从第 1 个记录中取出 2 个单位，从第 5 个记录中取出 1 个单位。如图所示，我的 SP 可以很好地处理这个问题，只是速度非常慢。

这是翻译成英文的存储过程；

CREATE PROC [dbo].[UpdateProductDetails]
as
UPDATE PRODUCTDETAILS SET E_CIKAN=0;
DECLARE @ID int
DECLARE @SK varchar(50),@DP varchar(50)  --SK = STOKKODU = PRODUCTID, DP = DEPOT
DECLARE @DEMAND float     --Demand=Quantity, We'll decrease it record by record
DECLARE @SUBID int
DECLARE @SUBQTY float,@SUBCK float,@REMAINS float
DECLARE SH CURSOR FAST_FORWARD FOR
SELECT [ID],PRODUCTID,QTY,EXITDEPOT FROM PRODUCTDETAILS  WHERE (EXITDEPOT IS NOT NULL) ORDER BY [DATE] ASC
OPEN SH
FETCH NEXT FROM SH INTO @ID, @SK,@DEMAND,@DP

WHILE (@@FETCH_STATUS = 0)
BEGIN
   DECLARE SA CURSOR FAST_FORWARD FOR
   SELECT [ID],QTY,E_CIKAN FROM PRODUCTDETAILS  WHERE (QTY>E_CIKAN) AND (PRODUCTID=@SK) AND (ENTRYDEPOT=@DP) ORDER BY [DATE] ASC
   OPEN SA
   FETCH NEXT FROM SA INTO @SUBID, @SUBQTY,@SUBCK
   WHILE (@@FETCH_STATUS = 0) AND (@DEMAND>0)
   BEGIN
      SET @REMAINS=@SUBQTY-@SUBCK
      IF @DEMAND>@REMAINS  --current record isnt sufficient, use it and move on
      BEGIN
         UPDATE PRODUCTDETAILS SET E_CIKAN=QTY WHERE ID=@SUBID;
         SET @DEMAND=@DEMAND-@REMAINS
      END
      ELSE
      BEGIN
         UPDATE PRODUCTDETAILS SET E_CIKAN=E_CIKAN+@DEMAND WHERE ID=@SUBID;
         SET @DEMAND=0
      END
      FETCH NEXT FROM SA INTO @SUBID, @SUBAD,@SUBCK
   END
   CLOSE SA
   DEALLOCATE SA
   FETCH NEXT FROM SH INTO @ID, @SK,@DEMAND,@DP
END
CLOSE SH
DEALLOCATE SH

Answer 1

根据我们在这个问题的其他答案中的对话，我想我已经找到了一种加快你的日常工作的方法。

您有两个嵌套游标:

• 第一个是选择指定了 exitdepot 的每一行。它需要产品、depo 和金额，然后:
• 内部游标循环遍历指定了entrydepot 的产品/depot 的行。它会为每个产品添加 E_CIKAN，直到分配完所有产品。

因此，内部游标循环对于您拥有的每个 exitdepot 行至少运行一次。但是，您的系统并不真正关心哪些项目是通过哪笔交易发出的 - 您只是尝试计算最终的 E_CIKAN 值。

所以...

您的外循环只需获取每个产品/仓库组合发出的总商品数量。因此，您可以将外部游标定义更改为:

DECLARE SH CURSOR FAST_FORWARD FOR
    SELECT PRODUCTID,EXITDEPOT, Sum(Qty) as TOTALQTY
    FROM PRODUCTDETAILS  
    WHERE (EXITDEPOT IS NOT NULL) 
    GROUP BY PRODUCTID, EXITDEPOT
OPEN SH
FETCH NEXT FROM SH INTO @SK,@DP,@DEMAND

(然后也将代码末尾的 SH 匹配的 FETCH 更改为匹配，显然)

这意味着外部游标要循环的行数会少得多，而内部游标要循环的行数大致相同。

所以这应该更快。