July 22, 2011 at 5:44 am
I'm about to build a function that returns a stock code that was superceded by another. However Chains can form.
This is a very simplified representation of my data.
Create table #Stock (StockID int Identity(1,1), StockCode varchar(20))
Create Table #Supercede (SupercedeID int Identity(1,1), FromStockID int, ToStockID int)
Insert Into #Stock (StockCode)
Select 'A' as 'StockCode'
UNION Select 'B'
UNION Select 'C'
UNION Select 'D'
UNION Select 'E'
UNION Select 'F'
UNION Select 'G'
UNION Select 'H'
UNION Select 'I'
UNION Select 'J'
Insert Into #Supercede (FromStockID, ToStockID)
Select '1' as 'FromStockID', '2' as 'ToStockIT'
UNION Select '2', '3'
UNION Select '3', '4'
UNION Select '4', '2' --Did this on purpose
Select * from #Stock
Select * from #Supercede
Drop Table #Stock
Drop Table #Supercede
Like I said, this is very simplified, but should make the concept clear.
Let's assume the function is Called f_LastSupercededItem(StockID)
If I Select dbo.f_LastSupercededItem(1), It should return 4
Now There is a data error with a stock item that will cause an infinite loop. So I will have to build in some logic for that.
With a set base solution, I can left join the To value to the from value. Test for null or get the next superceded item. But this will just give me 1 level further into the chain. There can be 10 links in the chain, or 20 for that matter. Do I then have to do 10 left joins?
Is this truly a case where a loop / cursor is justifiable?
July 22, 2011 at 7:00 am
July 22, 2011 at 7:05 am
Decide on the logic then worry about the code. For instance, you could stop traversing if a previous number is reached and use the prior number. You could set a limit on the number of rows to include before halting, perhaps with a "not found" option.
Whatever logic you settle on should determine the code, not the other way round.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 22, 2011 at 12:02 pm
Gianluca Sartori (7/22/2011)
I don't think so, unless you're really using SQL Server 2000 (is this the right forum?).Take a look at these articles and see if they help:
Displaying Sorted Hierarchies[/url]
Hope this helps
Gianluca
It is SQL 2000 🙁
(ps this is the right forum, look at the breadcrumbs, its states we are in sql 7, 2000) 😀
Also looked at cte, but there is no way it seems to make this work on 2000
I've managed to sort multiple supersessions from a single item to many with a sub query that joins to the original, and counts the PK's < the current. This will not work though when you have different parts in the from field.
Will think a little more over the design. I might fix the data that will cause infinite loops, and prevent data like that with a trigger or something.
But for now it seems like cursor is the way to go 🙁
This job will run at night, so speed is not a major issue. What is a concern is this function will run against 200k lines, of which about 10K contains a superceded part. so 10 seconds is out of the question, but 1 or 2 seconds I can live with.
July 22, 2011 at 12:17 pm
1 second per row is too long. 200k records at 1 second per row is a bit over 55 hours. Just so you have the right scope on the performance.
There are much easier ways to do this in SQL 2005 or later, but in SQL 2000, your best bet is probably a cursor or a recursive UDF. Test both. Either one can use a breadcrumb style hierarchy to make sure a value hasn't been hit before, thus preventing infinite loops.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 22, 2011 at 12:43 pm
GSquared (7/22/2011)
1 second per row is too long. 200k records at 1 second per row is a bit over 55 hours. Just so you have the right scope on the performance.There are much easier ways to do this in SQL 2005 or later, but in SQL 2000, your best bet is probably a cursor or a recursive UDF. Test both. Either one can use a breadcrumb style hierarchy to make sure a value hasn't been hit before, thus preventing infinite loops.
1st check I do in the UDF is if superceding is required. If not go next. Thus for 190K lines is is sub seconds, and for 10K lines it will execute the whole UDF
Thanks for the input though. Will run some test queries.
July 26, 2011 at 10:54 pm
You may wish to convert hierarchy into relationship.
Create Table #StockSuperceded
(StockID_Superceeded int,
StockID_SupercededWith int,
StepNo smallint -- assuming there won't be more than 32k steps in the process :-)
)
Then you create a trigger on #Supercede to populate the "history" table:
INSERT INTO #StockSuperceded
(StockID_Superceeded, StockID_SupercededWith, StepNo)
SELECT T.StockID_Superceeded, i.ToStockID, T.StepNo + 1
FROM inserted i
INNER JOIN #StockSuperceded T ON T.StockID_SupercededWith = i.FromStockID
INSERT INTO #StockSuperceded
(StockID_Superceeded, StockID_SupercededWith, StepNo)
SELECT i.FromStockID, i.ToStockID, 1
FROM inserted i
Then when you need to find out what was the chain of superceeings for any particular item you just run a simple SELECT:
SELECT @StockID, StockID_SupercededWith, StepNo
FROM #StockSuperceded
WHERE StockID_Superceeded = @StockID
ORDER BY StepNo
Please don't take it as a ready solution, it's a draft pointing to a direction.
_____________
Code for TallyGenerator
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply