April 30, 2008 at 2:09 pm
This cursor has given me headaches since day 1. It was tolerable in sql2000 because it was a batch process and ran overnight. However in 2005 the performance has gone so far south of tolerable that you can't see it (~3 minutes to ~8 hours!!!). And this isn't the largest recordset (233000 rows) it processes. It joins 2 tables then runs cumulative updates on 1 table based on results from both. If the item number changes, it resets the cum total. Reading on here, it looks like maybe a candidate for using a tally table, but I can't quite get it.
Here's the cursor:
set nocount on
Declare @Record_No as int
Declare @CumPlnQty as float
Declare @mRqmnts as float
Declare @mCID as float
Declare @CumCumInDemand as float
Declare @IOHandNet as float
Declare @IOHoldNet as float
Declare @Count as integer
Declare @TType as varchar(30)
Declare @ChkItem as varchar(47)
Declare @CumItem as varchar(47)
Declare @Update1_Ctr as integer
Declare @Update2_Ctr as integer
Declare CumInDemand_cursor CURSOR FORWARD_ONLY FOR
SELECT b.Record_No
, a.InvOnHoldNet
, a.InvOnHandNet
, b.Item
, b.PlnQty
, b.CumInDemand
From ETPTables903.dbo.tInvMove b
, ETPTables903.dbo.tItemMaster a
Where b.item = a.item
and (b.TType <> "+ (Planned Receipt)" or b.ttype is null)
Order by b.item
, b.TDt
, b.TType
, b.fOrder
FOR UPDATE OF CumInDemand
Set @ChkItem = "0000000000000000"
Set @Count = 0
Set @Update1_Ctr = 0
Set @Update2_Ctr = 0
OPEN CumInDemand_cursor
FETCH NEXT FROM CumInDemand_cursor INTO @Record_No, @IOHoldNet, @IOHandNet, @CumItem, @CumPlnQty, @CumCumInDemand
SELECT @@fetch_status
WHILE @@fetch_status = 0
BEGIN
IF @ChkItem <> @CumItem
BEGIN
Set @ChkItem = @CumItem
Set @mCid = @IOHandNet - @IOHoldNet
END
Set @mCID = @mCID - @CumPlnQty
If @mCID >= 0
BEGIN
Update ETPTables903.dbo.tInvMove
Set CumInDemand = 0 Where current of CumInDemand_cursor
Set @Update1_Ctr = @Update1_Ctr + 1
END
Else
BEGIN
Update ETPTables903.dbo.tInvMove
Set CumInDemand = @mCID * (-1) Where current of CumInDemand_cursor
Set @Update2_Ctr = @Update2_Ctr + 1
END
FETCH NEXT FROM CumInDemand_cursor INTO @Record_No, @IOHoldNet, @IOHandNet, @CumItem, @CumPlnQty, @CumCumInDemand
SELECT @@fetch_status
SET @Count = @Count + 1
CONTINUE
END
CLOSE CumInDemand_cursor
DEALLOCATE CumInDemand_cursor
Here's some data (actually the results from running it):
Record_No InvOnHoldNet InvOnHandNet Item PlnQty CumInDemand
20 0 4 0154JV 1 0
21 0 4 0154JV 1 0
22 0 4 0154JV 1 0
23 0 4 0154JV 1 0
24 0 4 0154JV 1 1
25 0 4 0154JV 1 2
26 0 4 0154JV 1 3
27 0 4 0154JV 1 4
48 0 204 11604-1400-901 50 0
49 0 204 11604-1400-901 50 0
50 0 204 11604-1400-901 50 0
51 0 204 11604-1400-901 50 0
52 0 204 11604-1400-901 50 46
53 0 204 11604-1400-901 50 96
54 0 204 11604-1400-901 50 146
55 0 204 11604-1400-901 50 196
56 0 204 11604-1400-901 50 246
57 0 204 11604-1400-901 50 296
58 0 204 11604-1400-901 50 346
69 0 204 11604-1400-901 4 350
Anybody got a clue?
April 30, 2008 at 4:35 pm
Please put your data into a form that we can use for tests (like INSERT statements). Here is a link that explains this:http://qa.sqlservercentral.com/articles/Best+Practices/61537/
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 30, 2008 at 4:47 pm
I'll second that. In fact, it would be good if you'd include sample data from before and after your cursor runs. This seems like a pretty straight forward re-write, but it'll help if you get the prep work ready.
April 30, 2008 at 4:58 pm
Also, this bit here:
...
SELECT @@fetch_status
WHILE @@fetch_status = 0
BEGIN
IF @ChkItem <> @CumItem
BEGIN
Set @ChkItem = @CumItem
Set @mCid = @IOHandNet - @IOHoldNet
END
...
Is this just to avoid redundantly recalculating the same @ChkItem and @mCid items for every @CumItem group or do you really need to use only the first values in the group?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 30, 2008 at 6:08 pm
OK, no answers yet, but this is what I have come up with:
--====== Load up the working table
SELECT b.Record_No
, b.Item
, b.PlnQty
, Rank() Over(Order by b.Item) as ChkItem
, Row_Number() Over(Partition by b.Item Order by b.TDt, b.TType, b.fOrder) as CumItemNo
, (a.InvOnHandNet - a.InvOnHoldNet) as Diff1
, Cast(0 as Float) as mCID
INTO #tmpInvItem
From ETPTables903.dbo.tInvMove b
, ETPTables903.dbo.tItemMaster a
Where b.item = a.item
and (b.TType <> "+ (Planned Receipt)" or b.ttype is null)
Order by b.item, b.TDt, b.TType, b.fOrder
--======Add a Primary Key/Clustered Index for speed & order
Alter Table #tmpInvItem
ADD CONSTRAINT PK_tmpInvItem PRIMARY KEY CLUSTERED (ChkItem, CumItemNo)
--======Calculate the mCID (CumInDemand) values
Update t1
Set mCID = Diff1 - (Select SUM(t2.PlnQty) from #tmpInvItem t2
Where t2.ChkItem = t1.ChkItem
And t2.CumItemNo >= 1
And t2.CumItemNo < t1.CumItemNo)
From #tmpInvItem t1
--======Push it back into the source table
Update ETPTables903.dbo.tInvMove
Set CumInDemand = (Case When mCID >=0 Then 0 Else -1 * mCID End)
, @Update1_Ctr = @Update1_Ctr + (Case When mCID >=0 Then 1 Else 0 End)
, @Update2_Ctr = @Update2_Ctr + (Case When mCID >=0 Then 0 Else 1 End)
, @Count = @Count + 1
From ETPTables903.dbo.tInvMove IM
Join #tmpInvItem TI on
TI.Record_No = IM.Record_No
And TI.item = IM.item
Where IM.TType <> "+ (Planned Receipt)" or IM.ttype is null)
It definitely could be consolidated some, but it should be much faster than what you currently have.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 30, 2008 at 7:50 pm
If you really want some speed out of this, consider the following article (Grouped Running Total and Running Total sections), please... should be able to do 223,000 rows in about 2 seconds...
http://qa.sqlservercentral.com/articles/Advanced+Querying/61716/
--Jeff Moden
April 30, 2008 at 10:20 pm
Actually, Jeff, it's not that different from what I am already doing, except that it is dependent on an Index that may not exist.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 30, 2008 at 10:24 pm
Oops, wait a minute, I am getting my threads mixed up... Never mind.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 1, 2008 at 9:53 am
Ok, my bad. I was in a hurry last night, and didn't get everything in the shape it should have been. Here's a full script end to end that will build the tables, load the data, process it, and display the final results, including a before vs. after view of the CumInDemand field. Thanks for taking a look and making suggestions.
set nocount on
--Set up tables
IF OBJECT_ID('tInvMove','U') IS NOT NULL
DROP TABLE [tInvMove]
IF OBJECT_ID('tItemMaster','U') IS NOT NULL
DROP TABLE [tItemMaster]
IF OBJECT_ID('BEFORE','U') IS NOT NULL
DROP TABLE [BEFORE]
IF OBJECT_ID('AFTER','U') IS NOT NULL
DROP TABLE [AFTER]
/****** Object: Table [dbo].[tInvMove] Script Date: 05/01/2008 09:38:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tInvMove](
[Record_No] [int] IDENTITY(1,1) NOT NULL,
[Item] [varchar](47) NULL,
[TDt] [datetime] NULL,
[TType] [varchar](30) NULL,
[fOrder] [varchar](9) NULL,
[PlnQty] [float] NULL CONSTRAINT [DF_tInvMove_PlnQty] DEFAULT (0),
[CumInDemand] [float] NULL CONSTRAINT [DF_tInvMove_CumInDemand] DEFAULT (0),
CONSTRAINT [PK_tInvMove] PRIMARY KEY NONCLUSTERED
(
[Record_No] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
/****** Object: Table [dbo].[tItemMaster] Script Date: 05/01/2008 09:43:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tItemMaster](
[Item] [varchar](47) NOT NULL,
[InvOnHoldNet] [float] NULL,
[InvOnHandNet] [float] NULL,
CONSTRAINT [PK_tItemMaster] PRIMARY KEY NONCLUSTERED
(
[Item] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
--Load Sample Data
TRUNCATE TABLE [tItemMaster]
INSERT INTO [tItemMaster]
([Item]
,[InvOnHoldNet]
,[InvOnHandNet])
SELECT '0154JV',0,4 UNION ALL
SELECT '11604-1400-901',0,221
TRUNCATE TABLE [tInvMove]
SET IDENTITY_INSERT [tInvMove] ON
INSERT INTO [tInvMove]
([Record_No]
,[Item]
,[TDt]
,[TType]
,[fOrder]
,[PlnQty]
,[CumInDemand])
SELECT 20,'0154JV','Oct 24 2008 5:00AM','- (Mat.Requirement)','B00087533',1,0 UNION ALL
SELECT 21,'0154JV','Oct 28 2008 5:00AM','- (Mat.Requirement)','B00087541',1,0 UNION ALL
SELECT 22,'0154JV','Dec 18 2008 6:00AM','- (Mat.Requirement)','B00091515',1,0 UNION ALL
SELECT 23,'0154JV','Dec 22 2008 6:00AM','- (Mat.Requirement)','B00091523',1,0 UNION ALL
SELECT 24,'0154JV','Jan 23 2009 8:00AM','Assembly Part Demand','0',1,1 UNION ALL
SELECT 25,'0154JV','Jan 30 2009 8:00AM','Assembly Part Demand','0',1,2 UNION ALL
SELECT 26,'0154JV','Feb 13 2009 8:00AM','Assembly Part Demand','0',1,3 UNION ALL
SELECT 27,'0154JV','Feb 20 2009 8:00AM','Assembly Part Demand','0',1,4 UNION ALL
SELECT 48,'11604-1400-901','Apr 14 2008 12:00AM','- (Mat.Requirement)','100359378',50,0 UNION ALL
SELECT 49,'11604-1400-901','Apr 19 2008 12:00AM','- (Mat.Requirement)','100360031',50,0 UNION ALL
SELECT 50,'11604-1400-901','Apr 19 2008 12:00AM','- (Mat.Requirement)','100360032',50,0 UNION ALL
SELECT 51,'11604-1400-901','Apr 19 2008 12:00AM','- (Mat.Requirement)','100360033',50,0 UNION ALL
SELECT 52,'11604-1400-901','Apr 19 2008 12:00AM','- (Mat.Requirement)','100360034',50,46 UNION ALL
SELECT 53,'11604-1400-901','Apr 19 2008 12:00AM','- (Mat.Requirement)','100360035',50,96 UNION ALL
SELECT 54,'11604-1400-901','Apr 26 2008 12:00AM','- (Mat.Requirement)','100361123',50,146 UNION ALL
SELECT 55,'11604-1400-901','Apr 26 2008 12:00AM','- (Mat.Requirement)','100361124',50,196 UNION ALL
SELECT 56,'11604-1400-901','Apr 26 2008 12:00AM','- (Mat.Requirement)','100361125',50,246 UNION ALL
SELECT 57,'11604-1400-901','Apr 26 2008 12:00AM','- (Mat.Requirement)','100361126',50,296 UNION ALL
SELECT 58,'11604-1400-901','Apr 26 2008 12:00AM','- (Mat.Requirement)','100361127',50,346 UNION ALL
SELECT 59,'11604-1400-901','Jun 4 2008 12:00AM','- (Mat.Requirement)','100362743',50,1143 UNION ALL
SELECT 60,'11604-1400-901','Jun 5 2008 12:00AM','- (Mat.Requirement)','100362744',50,1257 UNION ALL
SELECT 61,'11604-1400-901','Jun 6 2008 12:00AM','- (Mat.Requirement)','100362745',50,1363 UNION ALL
SELECT 62,'11604-1400-901','Jun 9 2008 12:00AM','- (Mat.Requirement)','100362746',50,1465 UNION ALL
SELECT 63,'11604-1400-901','Jun 10 2008 12:00AM','- (Mat.Requirement)','100362747',50,1576 UNION ALL
SELECT 64,'11604-1400-901','Jun 11 2008 12:00AM','- (Mat.Requirement)','100363798',50,1703 UNION ALL
SELECT 65,'11604-1400-901','Jun 12 2008 12:00AM','- (Mat.Requirement)','100363799',50,1805 UNION ALL
SELECT 66,'11604-1400-901','Jun 13 2008 12:00AM','- (Mat.Requirement)','100363800',50,1911 UNION ALL
SELECT 67,'11604-1400-901','Jun 16 2008 12:00AM','- (Mat.Requirement)','100363801',50,2013 UNION ALL
SELECT 68,'11604-1400-901','Jun 17 2008 12:00AM','- (Mat.Requirement)','100363802',50,2119 UNION ALL
SELECT 69,'11604-1400-901','May 13 2008 6:15PM','- (Mat.Requirement)','100360063',4,350
SET IDENTITY_INSERT [tInvMove] OFF
--Get "BEFORE" Snapshot
SELECT b.Record_No
, a.InvOnHoldNet
, a.InvOnHandNet
, b.Item
, b.PlnQty
, b.CumInDemand
INTO BEFORE
From tInvMove b
, tItemMaster a
Where b.item = a.item
and (b.TType <> '+ (Planned Receipt)' or b.ttype is null)
Order by b.item
, b.TDt
, b.TType
, b.fOrder
/* Begin problem code */
--Set up Variables
Declare @Record_No as int
Declare @CumPlnQty as float
Declare @mRqmnts as float
Declare @mCID as float
Declare @CumCumInDemand as float
Declare @IOHandNet as float
Declare @IOHoldNet as float
Declare @Count as integer
Declare @TType as varchar(30)
Declare @ChkItem as varchar(47)
Declare @CumItem as varchar(47)
Declare @Update1_Ctr as integer
Declare @Update2_Ctr as integer
--Create Cursor
Declare CumInDemand_cursor CURSOR FORWARD_ONLY FOR
SELECT b.Record_No
, a.InvOnHoldNet
, a.InvOnHandNet
, b.Item
, b.PlnQty
, b.CumInDemand
From tInvMove b
, tItemMaster a
Where b.item = a.item
and (b.TType <> '+ (Planned Receipt)' or b.ttype is null)
Order by b.item
, b.TDt
, b.TType
, b.fOrder
FOR UPDATE OF CumInDemand
--Initialize Variables
Set @ChkItem = '0000000000000000'
Set @Count = 0
Set @Update1_Ctr = 0
Set @Update2_Ctr = 0
OPEN CumInDemand_cursor
FETCH NEXT FROM CumInDemand_cursor INTO @Record_No, @IOHoldNet, @IOHandNet, @CumItem, @CumPlnQty, @CumCumInDemand
WHILE @@fetch_status = 0
BEGIN
--Check Current Item against previous item, if different, CumInDemand resets for this item
IF @ChkItem <> @CumItem
BEGIN
Set @ChkItem = @CumItem
Set @mCid = @IOHandNet - @IOHoldNet
END
Set @mCID = @mCID - @CumPlnQty
If @mCID >= 0
BEGIN
Update tInvMove
Set CumInDemand = 0 Where current of CumInDemand_cursor
Set @Update1_Ctr = @Update1_Ctr + 1
END
Else
BEGIN
Update tInvMove
Set CumInDemand = @mCID * (-1) Where current of CumInDemand_cursor
Set @Update2_Ctr = @Update2_Ctr + 1
END
--Get next record
FETCH NEXT FROM CumInDemand_cursor INTO @Record_No, @IOHoldNet, @IOHandNet, @CumItem, @CumPlnQty, @CumCumInDemand
SET @Count = @Count + 1
CONTINUE
END
CLOSE CumInDemand_cursor
DEALLOCATE CumInDemand_cursor
/* End problem code */
--Get "AFTER" Snapshot
SELECT b.Record_No
, a.InvOnHoldNet
, a.InvOnHandNet
, b.Item
, b.PlnQty
, b.CumInDemand
INTO AFTER
From tInvMove b
, tItemMaster a
Where b.item = a.item
and (b.TType <> '+ (Planned Receipt)' or b.ttype is null)
Order by b.item
, b.TDt
, b.TType
, b.fOrder
select b.record_no, b.item, b.cumindemand, a.cumindemand
from before b
inner join [after] a
on b.record_no = a.record_no
May 1, 2008 at 9:57 am
rbarryyoung (4/30/2008)
Also, this bit here:
...
SELECT @@fetch_status
WHILE @@fetch_status = 0
BEGIN
IF @ChkItem <> @CumItem
BEGIN
Set @ChkItem = @CumItem
Set @mCid = @IOHandNet - @IOHoldNet
END
...
Is this just to avoid redundantly recalculating the same @ChkItem and @mCid items for every @CumItem group or do you really need to use only the first values in the group?
That piece of code checks the current record against the previous record. If the item number is the same, it keeps track of it and cumulatively updates the CumInDemand field. If the item has changed, it resets so the CumInDemand only pertains to this item.
And (duh! me), I figured out that the SELECT @@fetch_status did not need to be there. Don't ask me why I didn't catch that before.
May 1, 2008 at 8:02 pm
So, you're happy with the speed of a While loop? Might as well use a "fire hose" cursor if you're gonna do that.
--Jeff Moden
May 1, 2008 at 8:38 pm
OK, looking at your data now, it is clear that my routines do not respect the relationship between your "Order By" fields and the sequencing of CumInDemand. I do not have time to fix it now, but hopefully, this can get you started.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 2, 2008 at 1:20 pm
HOLY CRAP! :w00t::w00t::w00t::w00t: I rewrote the query based on Jeff's Running Total post, and it went from 8 hours to 6 seconds! Jeff, I bow down before you! THANKS!!!!!!!
--===== Build Temp table
SELECT b.Record_No
, a.InvOnHoldNet
, a.InvOnHandNet
, b.Item
, b.PlnQty
, b.CumInDemand
, cast(0 as float) CumDemand
INTO #tInvMove
From tInvMove b
, tItemMaster a
Where b.item = a.item
and (b.TType <> '+ (Planned Receipt)' or b.ttype is null)
Order by b.item
, b.TDt
, b.TType
, b.fOrder
--===== Add Primary Key
ALTER TABLE #tInvMove ADD PRIMARY KEY CLUSTERED (Record_No)
--===== Declare the variables
DECLARE @CumDemand float
SET @CumDemand = 0
DECLARE @PrevItem varchar(47)
SET @PrevItem = ''
--===== Changed the formula to "reset" when the item changes
UPDATE #tInvMove
SET @CumDemand = CumDemand = CASE
WHEN Item = @PrevItem
THEN @CumDemand + PlnQty
ELSE PlnQty -- Restarts total at "0 + current amount"
END,
@PrevItem = Item
FROM #tInvMove WITH (TABLOCKX)
--===== Push the changes back to the main table
UPDATE tInvMove
SET CumInDemand = CASE
WHEN ((t.InvOnHandNet - t.InvOnHoldNet) - t.CumDemand) > 0
THEN 0
ELSE ((t.InvOnHandNet - t.InvOnHoldNet) - t.CumDemand) * (-1)
END
FROM tInvMove m
INNER JOIN #tInvMove t
ON m.Record_No = t.Record_No
DROP TABLE #tInvMove
May 2, 2008 at 1:27 pm
Less code too.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 2, 2008 at 2:52 pm
mdevore (5/2/2008)
HOLY CRAP! :w00t::w00t::w00t::w00t: I rewrote the query based on Jeff's Running Total post, and it went from 8 hours to 6 seconds! Jeff, I bow down before you! THANKS!!!!!!!
Heh... ran so fast I'll bet you didn't think it ran at all, huh? Lemme know before you try to use a While loop for anything again... 😀
And, thanks for the awesome compliment! 🙂
--Jeff Moden
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply