SQL Server Performance

Slow Nested Cursors

Discussion in 'General Developer Questions' started by amer_1109, Jul 28, 2008.

  1. amer_1109 New Member

    i am using nested cursors to write inventory register i.e. calculating moving average of items in my store five tables ItemsOpening BalanceReceiptReturnIssuemade a union queryQdInvregUnProcWhere Qd is for querycalculate the average rate on every receipt n apply it to all the issues beyond that datehere is the stored procedure which took 30 minutes to complete for about 50,000 records in the union queryPlz help me that how i can speed this process upCREATE PROCEDURE Inv_Register01 @EdDate as DatetimeASSET NOCOUNT ONdeclare InvRegister0 cursorSCROLLforSelect ItemId from QdItemsOrder By itemidOpen InvRegister0Declare @ItemId moneySET @ItemId = 0delete from temperrorFETCH FIRST from InvRegister0 into @ItemIdWHILE (@@FETCH_STATUS =0)BEGINdeclare InvRegister cursorSCROLLforSelect Flag, [Id], ItemId, [Date], rQty, rRate, rAmt, iQty, iRate, iAmtfrom QdInvRegUnProcWhere ([Date] < @EdDate) AND ItemId = @ItemIdOrder By [Date], flgOpen InvRegisterDeclare @Flag Varchar(1),@Id Varchar(20),@Item Int,@Date Datetime,@rQty Money,@rRate Money,@rAmt Money,@iQty Money,@iRate Money,@iAmt Money,@Qty Money,@Amt Money,@UP MoneySET @Qty = 0SET @Amt = 0SET @UP = 0delete from temperrardelete from abcFETCH FIRST from InvRegister into @Flag,@Id,@Item,@Date,@rQty,@rRate,@rAmt,@iQty,@iRate,@iAmtWHILE (@@FETCH_STATUS =0)BEGINBEGINIF @Flag = 'G'begin set @Qty = (@Qty + @rQty)set @Amt = (@Amt + @rAmt)set @UP = (@Amt / @Qty)endelse if @Flag = 'R'begin set @Qty = (@Qty + @rQty)set @Amt = (@Amt + (@UP * @rqty))set @UP = (@Amt / @Qty)endelse if @Flag = 'O'beginset @Qty = (@Qty + @rQty)set @Amt = (@Amt + @rAmt)set @UP = (@Amt / @Qty)endelse if @Flag = 'I'beginset @Qty = (@Qty - @iQty)set @Amt = (@Amt - (@iQty * @UP))if @Qty < 0begininsert into temperror (itemid) values (@itemid)endendENDBEGINUPDATE sirdetail SET unitRate = round(@UP,4)WHERE [sirDate] >= @Date AND ItemId = @ItemId-- UPDATE QDitemledgerrt SET rRate = round(@UP,4)-- WHERE [Date] >= @Date AND ItemId = @ItemIdENDFETCH NEXT from InvRegister into @Flag,@Id,@Item,@Date,@rQty,@rRate,@rAmt,@iQty,@iRate,@iAmtENDClose InvRegisterDEALLOCATE InvRegisterFETCH next from InvRegister0 into @ItemIdENDClose InvRegister0DEALLOCATE InvRegister0GOPlease Help me Out
  2. moh_hassan20 New Member

    can you post the complete code
    i found no actions applied within cursor in your previous post.

  3. Adriaan New Member

    Instead of passing column values from a row in one table into a list of variables, then using those variables to lookup values from a row in another table ... just write a query that joins the two tables. No need to use variables.

Share This Page