How to make a T-SQL Cursor faster?

问题:

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

回答1:

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.

回答2:

Cursors have to be the worst performing solution to any problem when using 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.

回答3:

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

回答4:

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...

回答5:

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.)

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

回答6:

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 row to 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.

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 may be 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?

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 time and 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.

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.