How to make a T-SQL Cursor faster?

Answer 1

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

Answer 2

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

Answer 3

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.

Answer 4

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

Answer 5

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.

Answer 6

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.