>I can try to rewrite the SP into a set-based operation but that doesn't
>explain why, all of a sudden with no apparent changes, the SP is taking so
It's not that I want to make snide remarks, but the question verges
of the kind "why is the sky blue"? A cost-based optimizer makes
it estimates from statistics, and sometimes it goes wrong. And this
can happen from one day or another because you pass some threshold
in the volumes.
Of course, someone might also have dropped an index.
Anyway, since there is a hierarchy involved, one single INSERT
statement is not easilly achieved. (But Joe Celko might discuss
possible strategies in his books.) Redeclaring a cursor again
and again is definitely not going help you to break the speed
If you change the cursor declaration to
DECLARE BOMcursor CURSOR LOCAL STATIC
you don't have to decallocate before the recursive call, and you
don't have to save FindNo when you comes back. Already this is likely
to do wonders.
Here is a outline which iterates as long as there are levels
in the hierarchy.
CREATE PROCEDURE ExplodeBOM2
CREATE TABLE #temp (ident int IDENTITY,
-- columns as target table)
CREATE TABLE #bills (itemno varchar(20) NOT NULL,
itemrev char(3) NOT NULL)
INSERT #bills(itemno, itemrev)
WHILE EXISTS (SELECT * FROM #bills)
-- indent the level designator by preceding it with n hyphens,
INSERT INTO #tmp -- You should add columns list here!
a.FindNo, b.ItemDesc, b.StdUM, b.StdCost, b.LastCost,
FROM stageBillDetail a
JOIN stageItemMaster b ON a.ItemNo = b.ItemNo
JOIN #bills c ON a.BillNo = c.itemno AND
a.BillRev = c.itemrev
ORDER BY a.FindNo
OPTION (MAXDOP = 1)
INSERT INTO stageIndendedBOM -- add column list!
t.ItemRev, t.ItemDesc, t.StdUM, t.Qty, t.StdCost, t.LastCost
INSERT #bills (itemno, itemrev)
SELECT ItemNo, MAX(ItemRev)
FROM #tmp t
JOIN stageBillMaster s ON s.ItemNo = s.BillNo
WHERE t.ItemRev LIKE '*%'
Books Online for SQL Server SP3 at