如何使 T-SQL 游标更快？

How to make a T-SQL Cursor faster?

提问by Ertugrul Kara

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.jpgWhereas GDEPO:Entry depot, CDEPO:Exit depot,Adet: quantity,E_CIKAN quantity that's used.

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

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.

记录说明：
1：20个单元进入01仓库，2：10个单元离开01。3：5个单元离开01（第一条记录的E_CIKAN现在是15个）4：10个单元进入01站点。5：3个单元离开01第一个记录。请注意，现在第 1 条记录的 E_CIKAN 设置为 18。 6：这就是问题所在：3 个单位需要离开仓库 01。第 1 个记录需要 2 个单位，第 5 个记录需要 1 个单位。如图所示，我的 SP 可以很好地处理这种情况，只是它真的很慢。

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

回答by codeulike

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.

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

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.

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

So ...

所以 ...

Your outer loop only needs to get the totalamount 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)

（然后也将代码末尾的匹配 FETCH 从 SH 更改为匹配，显然）

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.

所以这应该更快。

回答by Justin Niessner

Cursors have to be the worst performing solution to any problem when using T-SQL.

使用 T-SQL 时，游标必须是解决任何问题的最差解决方案。

You have two options depending on the complexity of what you're really trying to accomplish:

根据您真正要完成的任务的复杂程度，您有两种选择：

1. Attempt to rewrite the entire set of code to use set operations. This would be the fastest performing method...but sometimes you just can't do it using set operations.

2. Replace the cursor with a combination of a table variable (with identity column), counter, and while loop. You can then loop through each row of the table variable. Performs better than a cursor...even though it may not seem like it would.

1. 尝试重写整个代码集以使用集合操作。这将是执行速度最快的方法……但有时您无法使用集合操作来做到这一点。

2. 将游标替换为表变量（带有标识列）、计数器和 while 循环的组合。然后您可以遍历表变量的每一行。性能比游标好……即使它看起来不像。

回答by Garrett

First, if you MUST use a cursor, and you're updating stuff, then declare the cursor with the FOR UPDATE clause. (See example below. Note that the example is NOT based on your code at all.)

首先，如果您必须使用游标，并且您正在更新内容，则使用 FOR UPDATE 子句声明游标。（请参见下面的示例。请注意，该示例根本不是基于您的代码。）

Having said that, there are a myriad of ways to use something other than cursors, often leveraging temporary tables. I would investigate that route in lieu of cursors.

话虽如此，除了游标之外，还有很多方法可以使用临时表。我会调查那条路线来代替游标。

DECLARE LoopingCursor CURSOR LOCAL DYNAMIC
FOR
    select sortorder from customfielddefinition
    where context=@targetContext
FOR UPDATE OF sortorder

回答by codeulike

I can see that the problem you are trying to solve is quite complicated:

我可以看到您要解决的问题非常复杂：

• When there is a row with GDEPO specified, it represents stock going into the depo, and you want to use the E_CIKAN of that rowto track how much of the stock gets used later. E_CIKAN will start at 0 and then get added-to as stock goes out, until it reaches ADET.

• So when there is a subsequent row with CDEPO specified, it respresents stock going out, and you want to go back to E_CIKAN of the GDEPO-row and adjust the E_CIKAN, by adding the amount of stock-out to it.

• When there have been two separate rows with stock going in (GDEPO specified), sometimes there is an overflow when the E_CIKAN of one row reaches max (ADET) and then you want to add the remainder to the next one.

• 当有一行指定了 GDEPO 时，它表示库存进入仓库，并且您希望使用该行的 E_CIKAN来跟踪稍后使用的库存量。E_CIKAN 将从 0 开始，然后在库存耗尽时添加，直到达到 ADET。

• 因此，当有指定 CDEPO 的后续行时，它表示库存缺货，并且您希望返回 GDEPO 行的 E_CIKAN 并通过添加缺货量来调整 E_CIKAN。

• 当有两个单独的行有存货（指定 GDEPO）时，有时当一行的 E_CIKAN 达到最大值 (ADET) 时会出现溢出，然后您想将余数添加到下一行。

This is quite a tricky calculation because you have to compare different rows and go back and change values in perhaps one or perhaps two rows to track each stock transaction.

这是一个相当棘手的计算，因为您必须比较不同的行，然后返回并更改可能一行或两行中的值以跟踪每个股票交易。

There maybe a way to do that without a cursor, as others are suggesting. But I think if you could re-arrange your tables and store the data in a different way, you might be able to make the problem easier.

有可能是一个办法做到这一点没有游标的情况，因为别人都暗示。但我认为，如果您可以重新排列表格并以不同的方式存储数据，您也许可以使问题变得更容易。

For example, instead of keeping track of stock in the same table that records the stock transactions, could you have a separate table with 'Product_id, Depo_id, amount' columns that keeps track of the total amount of each product in each depo at one time?

例如，不是在记录库存交易的同一个表中跟踪库存，您是否可以有一个单独的表，其中包含“Product_id、Depo_id、amount”列，用于跟踪每个仓库中每种产品的总量?

A database design change such as that could make things easier.

像这样的数据库设计更改可以使事情变得更容易。

Or ... instead of using E_CIKAN to keep track of what is used, use it to keep track of what remains. And keep an E_CIKAN value in each row. So whenever stock goes in or out of a depo, re-calculate E_CIKAN at that point in timeand store it in that transaction row (instead of trying to go back to the original 'stock in' row and update it there). Then to find out the current stock, you just look at the most recent transcation for that product/depo.

或者...而不是使用 E_CIKAN 来跟踪使用的内容，而是使用它来跟踪剩余的内容。并在每一行中保留一个 E_CIKAN 值。因此，每当库存进出仓库时，在该时间点重新计算 E_CIKAN并将其存储在该交易行中（而不是尝试返回原始“库存进”行并在那里更新）。然后要找出当前库存，您只需查看该产品/仓库的最新交易。

In summary, what I am saying is, your calculation is slow and cumbersome because you are storing the data in a strange way. In the long run it might be worth changing your database design to make the programming easier.

总之，我要说的是，您的计算缓慢而繁琐，因为您以一种奇怪的方式存储数据。从长远来看，改变数据库设计以使编程更容易可能是值得的。

回答by KM.

remove the cursor and rewrite that as an UPDATE FROM joining in the the cursor's query, you can make the IFs a case if you need to. I'm too busy today to write the UPDATE for you today...

删除游标并将其重写为加入游标查询的 UPDATE FROM，如果需要，您可以将 IF 作为一个案例。我今天太忙了，今天没有为你写更新......

回答by Slim

Remove the cursor and do batch updates. I have yet to find a update that cant be done in batch.

移除光标并进行批量更新。我还没有找到不能批量完成的更新。