Slow Nested Cursors - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Slow Nested Cursors

Author

Topic

amer_1109
Starting Member

9 Posts

Posted - 2008-07-28 : 03:53:10

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

Mehr Amer Ali

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-28 : 04:10:29

can you explain your logic for calculation of moving average along with some sample data? Then it might be easier for us to see if we can achieve same by set based approach.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-28 : 04:31:38

The code doesn't do anything, really.
At the end of the cursor you just update another table, with @Up variable which is the same as Up column in original table using ItemID and date as key columns.

The only deviance is that if Qty (running total) at some point is less than zero, you log that.

E 12°55'05.25"
N 56°04'39.16"

amer_1109
Starting Member

9 Posts

Posted - 2008-07-28 : 04:52:32

FETCH FIRST from InvRegister into @Flag,@Id,@Item,@Date,@rQty,@rRate,@rAmt,@iQty,@iRate,@iAmt

Fetches date from union query

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

Calculates New UP

UPDATE sirdetail SET unitRate = round(@UP,4)
WHERE [sirDate] >= @Date AND ItemId = @ItemId
END

Updates New UP

It Works Brother

Outer Cursor just fetches itemid

Mehr Amer Ali

amer_1109
Starting Member

9 Posts

Posted - 2008-07-28 : 04:55:48

G is for Receipt
R is for Return
O is for Opening Balance
I is for Issue

Mehr Amer Ali

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-28 : 05:16:34

No, first cursor fetches all ItemID's ordered in ascending order.

quote:
declare InvRegister0 cursor
SCROLL
for

Select ItemId from QdItems
Order By itemid

Second cursor gets auxiliary thing like Flag, Qty and so on for records dated earlier than some datetime variable @edate and have ItemID equal to ItemID in first cursor.
That's it.

quote:
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

Then you do a running total for the auxiliary records, which is sorted by date.
If the running Qty is less than zero in this "loop", you insert that record into a logging table, but the calculation of running total is continued anyway.
When loop is done, you do not use the running total value. You use Up column from second cursor to update a table.

quote:
UPDATE sirdetail SET unitRate = round(@UP,4)
WHERE [sirDate] >= @Date AND ItemId = @ItemId
END

The Up value used is the "last" record in second cursor since no calculation is made over Up column, nor @Up variable.

E 12°55'05.25"
N 56°04'39.16"

amer_1109
Starting Member

9 Posts

Posted - 2008-07-28 : 05:30:18

the problem is that our factory purchases stores, spares and loose tools from market places it in its store and then these items are issued to engineers for repair n maintenance of the plant

e.g.

we purchased 2 no. of bearings @ 200 each on 28-07-2008
we issue 1 no. of bearing @ 200 from store and charge the repair maintenance cost for 200 on 29-07-2008
now balance with store is 1 no. of bearing @ 200
again we purchase 2 no. of bearing @ 300 from market on 30-07-2008
now the balance in store is
Qty Amount Unit Rate
3 800(200+300+300) 266.66 (i.e. avg rate)

It is called moving average

now the next issue to the engineers will charge the repir maintenance account of the plant and machinery @ 266.66 for each bearing

to summarize we can say that avg. rate of each item is calculated at every receipt and it remains applicable to all the issues from store untill the next receipt.

i use separate tables to record receipt and issue

Receipt Table

Date
Itemid
Qty
UnitRate

Issue Table

Date
ItemId
Qty
UnitRate

Items Table

ItemId
ItemName

1. sotre items with all attributes in items table
2. when an item is purchased i store the data in receipt table with
3. when an item is issued i store data in issue table

Plz Help Thanx

Mehr Amer Ali

amer_1109
Starting Member

9 Posts

Posted - 2008-07-28 : 05:37:22

UP is Calculated conditionally when flags are 'G' or 'R' or 'O'

Plz Find out Bold Lines

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

Mehr Amer Ali

amer_1109
Starting Member

9 Posts

Posted - 2008-07-28 : 06:12:44

quote:
Originally posted by Peso

No, first cursor fetches all ItemID's ordered in ascending order.
quote:
declare InvRegister0 cursor
SCROLL
for

Select ItemId from QdItems
Order By itemid
Second cursor gets auxiliary thing like Flag, Qty and so on for records dated earlier than some datetime variable @edate and have ItemID equal to ItemID in first cursor.
That's it.
quote:
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

Then you do a running total for the auxiliary records, which is sorted by date.
If the running Qty is less than zero in this "loop", you insert that record into a logging table, but the calculation of running total is continued anyway.
When loop is done, you do not use the running total value. You use Up column from second cursor to update a table.
quote:
UPDATE sirdetail SET unitRate = round(@UP,4)
WHERE [sirDate] >= @Date AND ItemId = @ItemId
END
The Up value used is the "last" record in second cursor since no calculation is made over Up column, nor @Up variable.

E 12°55'05.25"
N 56°04'39.16"

can i email u the complete database structure and data zipped format

Mehr Amer Ali

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-28 : 08:06:15

Yes, you are right. I missed that part.

quote:
Originally posted by amer_1109

It is called moving average

I think I have a slight idea what that is
http://weblogs.sqlteam.com/peterl/archive/2007/12/10/Cursor-is-really-faster-than-set-based-solution.aspx

E 12°55'05.25"
N 56°04'39.16"

amer_1109
Starting Member

9 Posts

Posted - 2008-07-29 : 00:01:31

i can send the database by email if someone wants to help me out

Mehr Amer Ali

amer_1109
Starting Member

9 Posts

Posted - 2008-07-29 : 00:07:27

Hello visakh murukes i think u can help me out if i send u the whole db

Mehr Amer Ali

amer_1109
Starting Member

9 Posts

Posted - 2008-07-30 : 04:04:28

place this statement here

IF @Flag = 'G'
begin
set @Qty = (@Qty + @rQty)
set @Amt = (@Amt + @rAmt)
set @UP = (@Amt / @Qty)

UPDATE sirdetail SET unitRate = round(@UP,4)
WHERE [sirDate] >= @Date AND ItemId = @ItemId

end

so that it does not execute for every row in cursor

it reduced the execution time to 1/3rd

Mehr Amer Ali

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources