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