如何使T-SQL游标更快？ [英] How to make a T-SQL Cursor faster?

问题描述

嘿，我在SQL Server 2000（现在不可能更新）的存储过程中有一个游标更新所有的表，但通常需要几分钟才能完成。我需要使它更快。下面是通过任意产品ID过滤的示例表：
示例表http://img231.imageshack.us/img231/9464/75187992.jpg
而GDEPO：Entry depot，CDEPO：Exit depot，Adet：quantity，E_CIKAN quantity used's used。

记录说明：

1：20单位进入仓库01，
2：10单位叶01.
3：5单位叶01（第一个记录的E_CIKAN现在为15）
4： 01.
5：3单位离开第1个记录的01。现在注意第一条记录的E_CIKAN设置为18.
6：这是问题出在哪里：3单位需要离开车厂01.从第一个记录需要2个单位，第五个记录需要1个单位。

这里的存储过程翻译成英语;

  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  - ，我们将记录减少记录
 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  - 当前记录不够用， b $ b 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 
下一步从SA INTO @SUBID，@ SUBAD，@ SUBCK 
 END 
关闭SA 
 DEALLOCATE SA 
 FETCH NEXT FROM SHIDTO @ID，@ SK，@ DEMAND，@ DP 
 END 
 CLOSE SH 
 DEALLOCATE SH 
  

解决方案

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

您有两个嵌套的游标：

• 第一个选择有一个exitdepot指定。它需要产品，depo和金额，然后：


• 内部游标循环遍历指定了entrydepot的产品/库的行。

因此，内部游标循环至少运行一次，直到它分配了所有的产品。每个exitdepot行。但是，您的系统并不真正关心哪些项目与哪个交易相关 - 您只是尝试计算最终的E_CIKAN值。

因此...

您的外圈仅需要获取每个产品/库组合的出货量总。因此，您可以将外部游标定义更改为：

  DECLARE SH CURSOR FAST_FORWARD FOR 
 SELECT PRODUCTID，EXITDEPOT，Sum数量）为TOTALQTY 
 FROM PRODUCTDETAILS 
 WHERE（EXITDEPOT不为NULL）
 GROUP BY PRODUCTID，EXITDEPOT 
 OPEN SH 
 FETCH NEXT FROM SH INTO @ SK，@ DP ，@ DEMAND 
  

（然后还要更改匹配的FETCH从SH结尾的代码匹配，显然）

这意味着你的外部游标将有更少的行循环，而你的内部游标将粗略地相同数量的行循环。 p>

所以这应该更快。

Hey, I Have a cursor in stored procedure under SQL Server 2000 (not possible to update right now) that updates all of table but it usually takes few minutes to complete. I need to make it faster. Here's example table filtered by an arbitrary product id; Example table http://img231.imageshack.us/img231/9464/75187992.jpg Whereas GDEPO:Entry depot, CDEPO:Exit depot,Adet: quantity,E_CIKAN quantity that's used.

Record explainations:
1: 20 unit enters depot 01, 2: 10 unit leaves 01. 3: 5 Unit leaves 01 (E_CIKAN for 1st record will be 15 now) 4: 10 more unit enters depot 01. 5: 3 unit leaves 01 from 1st record. Notice now 1st record has E_CIKAN set to 18. 6: This is where the problem comes in: 3 unit needs to leave depot 01. It takes 2 unit from 1st record and 1 unit from 5th record. My SP can handle this fine as seen in picture, except it's REALLY slow.

Here's the stored procedure translated into English;

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

解决方案

Based on our conversation in my other answer to this question, I think I have found a way to speed up your routine.

You have two nested cursors:

• The first one is selecting each row that has an exitdepot specified. It takes the product, depo and amount, and then:
• The inner cursor loop runs through the rows for that product/depot that have entrydepot specified. It adds onto the E_CIKAN for each one, until it has allocated all the product.

So the inner cursor loop runs at least once for every exitdepot row you have. However, your system doesn't really care which items went out with which transaction - you are only trying to calculate the final E_CIKAN values.

So ...

Your outer loop only needs to get the total amount of items shipped out for each product/depot combo. Hence you could change the outer cursor definition to:

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

(and then also change the matching FETCH from SH at the end of the code to match, obviously)

This means your outer cursor will have many fewer rows to loop through, and your inner cursor will have roughtly the same amount of rows to loop through.

So this should be faster.

这篇关于如何使T-SQL游标更快？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！