Slow Neted Cursor in Stored Procedure

Last Post 29 Jul 2008 12:32 AM by SQLUSA. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
amer1109
New Member
New Member

--
28 Jul 2008 09:32 PM
i am using nested cursors to write inventory register i.e. calculating moving average of items in my store

five tables

Items

Opening Balance

Receipt
Return
Issue

made a union query
QdInvregUnProc

Where Qd is for query

calculate the average rate on every receipt
n apply it to all the issues beyond that date


here is the stored procedure which took 30 minutes to complete for about 50,000 records in the union query



Plz help me that how i can speed this process up




CREATE PROCEDURE Inv_Register01 @EdDate as Datetime
AS
SET NOCOUNT ON
declare InvRegister0 cursor
SCROLL
for

Select ItemId from QdItems
Order By itemid

Open InvRegister0
Declare @ItemId money
SET @ItemId = 0
delete from temperror
FETCH FIRST from InvRegister0 into @ItemId
WHILE (@@FETCH_STATUS =0)
BEGIN
declare InvRegister cursor
SCROLL
for
Select Flag, [Id], ItemId, [Date], rQty, rRate, rAmt, iQty, iRate, iAmt
from QdInvRegUnProc
Where ([Date] < @EdDate) AND ItemId = @ItemId
Order By [Date], flg
Open InvRegister
Declare @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 Money
SET @Qty = 0
SET @Amt = 0
SET @UP = 0
delete from temperrar
delete from abc
FETCH FIRST from InvRegister into @Flag,@Id,@Item,@Date,@rQty,@rRate,@rAmt,@iQty,@iRate,@iAmt
WHILE (@@FETCH_STATUS =0)
quote:

Moving Average
BEGIN
BEGIN
IF @Flag = 'G'
begin
set @Qty = (@Qty + @rQty)
set @Amt = (@Amt + @rAmt)
set @UP = (@Amt / @Qty)
end
else if @Flag = 'R'
begin
set @Qty = (@Qty + @rQty)
set @Amt = (@Amt + (@UP * @rqty))
set @UP = (@Amt / @Qty)
end
else if @Flag = 'O'
begin
set @Qty = (@Qty + @rQty)
set @Amt = (@Amt + @rAmt)
set @UP = (@Amt / @Qty)
end
else if @Flag = 'I'
begin
set @Qty = (@Qty - @iQty)
set @Amt = (@Amt - (@iQty * @UP))
if @Qty < 0
begin
insert into temperror (itemid) values (@itemid)
end
end
END
BEGIN
UPDATE sirdetail SET unitRate = round(@UP,4)
WHERE [sirDate] >= @Date AND ItemId = @ItemId
-- UPDATE QDitemledgerrt SET rRate = round(@UP,4)
-- WHERE [Date] >= @Date AND ItemId = @ItemId
END
FETCH NEXT from InvRegister into @Flag,@Id,@Item,@Date,@rQty,@rRate,@rAmt,@iQty,@iRate,@iAmt
END


Close InvRegister
DEALLOCATE InvRegister
FETCH next from InvRegister0 into @ItemId

END

Close InvRegister0

DEALLOCATE InvRegister0
GO
SQLUSA
New Member
New Member

--
29 Jul 2008 12:32 AM
Best way to speed up a cursor is to use set-based operations.

Check out this link:
http://www.bigresource.com/MS_SQL-O...kjqHi.html


Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/


Acceptable Use Policy
---