title: How to make a T-SQL Cursor faster?
body: <p>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.</p>
  <p>Whereas GDEPO:Entry depot, CDEPO:Exit depot,Adet: quantity,E_CIKAN quantity that's used.</p>
  <p>Record explanations:</p>
  <ol>
  <li>20 unit enters depot 01,</li>
  <li>10 unit leaves 01.</li>
  <li>5 Unit leaves 01 (E_CIKAN for 1st record will be 15 now)</li>
  <li>10 more unit enters depot 01.</li>
  <li>3 unit leaves 01 from 1st record. Notice now 1st record has E_CIKAN set to 18.</li>
  <li>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.</li>
  </ol>
  <p>Here's the stored procedure translated into English;</p>
  <pre><code>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&gt;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&gt;0)
     BEGIN
        SET @REMAINS=@SUBQTY-@SUBCK
        IF @DEMAND&gt;@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
  </code></pre>