slow nested cursor - Stored Procedures, Triggers, & Functions - LearnSqlServer.com's SQL Server Forums

Member of the LearnItFirst.com Video Training Network	\| LearnSqlServer.com \| SQL SSIS Training \| SQL Programming Tutorials \|

Welcome Guest

Search | New Posts | Members | Log In | Register

LearnSqlServer.com's SQL Server Forums » Transact-SQL Programming » Stored Procedures, Triggers, & Functions » slow nested cursor

slow nested cursor

Options

Previous Topic · Next Topic

amer_1109

Posted: Monday, July 28, 2008 11:33:44 PM

Rank: Newbie

Joined: 7/28/2008
Posts: 1
Points: 3
Location: Pakistan

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:

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

Scott Whigham

Posted: Friday, August 01, 2008 3:53:22 PM

Rank: Super Mod

Joined: 3/20/2006
Posts: 345
Points: 748
Location: Dallas, TX

Hi there -

I'd love to help, I really would, but there's just no way I can without (a) knowing your schema/tables, and (b) without spending 30 minutes on the problem at a minimum. I'm afraid that I don't have a spare 30 minutes right now (I just had a baby!).

Anyway, if you can distill this to a simpler question, I'll be happy to try to help.

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.