Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Recursive User-Defined Function Expand / Collapse
Author
Message
Posted Monday, June 20, 2011 5:42 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:25 AM
Points: 35,959, Visits: 30,253
GSquared (6/20/2011)
Jeff Moden (6/20/2011)
GSquared (6/20/2011)
He's very technically proficient


Considering that he recently recommended FK's on First and LastName columns and he still uses a While Loop/Push Stack to convert from an Adjacency List to a Nested Set, I'll have to disagree with that notion.


Okay. I'll clarify. He displays significant but inconsistent technical proficiency, oddly combined with periods of acute technical WTFness of magnitude.

Better?


Better? BWAA-HAA!!! I couldn't have said it any better!


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1128658
Posted Monday, June 20, 2011 6:32 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:25 AM
Points: 35,959, Visits: 30,253
MikeAngelastro-571287 (6/20/2011)
Hi GSquared and Jeff,

Thanks for your kind comments. Jeff, I read both of your links and added a bookmark for each one. They actually contain advice on how to logically think through a problem to come up with a good solution for it at the outset. Posters following your advice may actually find that they are able to solve the problem on their own. In addition, it follows a process often used by authors of books on SQL in order to provide readers some simplified data to help with the learning. That is what I should have done before my original post.

I am no longer working for the same company but I am interested in T-SQL solutions for graphs, trees, and hierarchies. I’m reading though a book by Itzik Ben-Gan et al, the one I used three year ago for the CTE that I used for my final solution. When I finish the Ben-Gan book, which has only one, though quite thorough chapter about these issues, I’d like to study more. Since it looks like the Celko book may not be ideal (I read some Amazon reviews), do you know of a good source of tutorials I could use for this?

As a VB .NET developer, I was faced with the need to become very knowledgeable on both .NET and SQL. One requirement was to create production orders based on the hierarchical nature of the BOM table. But I came across a condition where a BOM with only 6 line items, 4 of which were phantoms, required 4 minutes for a production order containing 385 line items to be created from it. The code I had inherited was using a recursive VB function. Every time it encountered a phantom it had to recall itself to query the database to get the phantom's members. I added code to determine how many times the database was being hit and discovered that it was over 100 times for this BOM. I believed and still do that, for data-centric applications, it is best to do as much of the processing right in the database as possible. This principle was not being followed by the VB code. So I looked for a way to hit the database only once and used the recursive stored procedure to do it. The procedure took less than a second to run and the result was that the original 4 minutes was reduced to 1 minute. I couldn’t get lower than that because I had to use their SDK to create the production order based on the record set from the stored procedure. They didn’t want any objects created unless policed by their SDK.

This experience and others led me to the notion that because many organizations look for developers who could do everything, desktop .NET, ASP .NET, SQL, etc., they are being very foolish. The knowledge required to create major applications is too great for this to work. They end up getting jacks of all trades and masters of none. The resulting applications then are not all that great. This policy is contrary to SOA or any of the other well-regarded architectural designs. I think there needs to be more division of the effort and a major part is the database piece. I think that all the data requesting should be handled by people who know all the abstruse features of databases and the let desktop and ASP developers do their thing without distraction. What do you think?
I hope I’m not being too verbose here for SQL Server Central, but I think this issue is quite important. If I am, I apologize. The next time I post with a problem issue, I will follow the advice from the links you provided.

Thanks,

Mike


What I think is that you're a very wise man, Mike. There are some people on this very forum who are actually outstanding in both worlds. I find that they're they extraordinary exception rather then even coming close to the rule.

Shifting gears, I've not tried to generate BOM's using "phantoms" as you say. From the sounds of it, you did quite well. Other than the Ben-Gan chapters, I don’t know of any really good tutorials that explain the “big 3” hierarchical structures along with how they can be made to interplay with each other. There are quite a few that explain each kind more-or-less separately. They can usually be found by Googling for “Adjacency List Hierarchy”, “Hierarchical Path”, and “Nested Sets Hierarchy”. Of course, be real careful with the code that accompanies those articles… I found that a lot of the principles given are spot on but the code is sometimes more than a wee-bit performance challenged, silently misses some nodes here and there, or just flat-out doesn’t work until a couple of repairs have been made (or course, making those repairs tends to deepen the understanding quite a bit). With exception of Ben-Gan’s chapters, I’ve found that the more famous the author is, the more likely that one of these problems will be present, so test and validate everything.

Is there something special you’re looking for in a tutorial on hierarchies?

Thank you for the kind words on the links in my signature. Gail’s link (the second one) is especially helpful in trying to science-out performance problems. And, as you suggested, both links have inspired some folks to write in and say “I was going to ask a question but those two links helped me figure it out on my own.”

It's been a real pleasure and I really enjoyed your "miniature article" on this thread. I really do wish more people thought like you do.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1128670
Posted Monday, June 20, 2011 6:57 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 16, 2012 10:19 AM
Points: 46, Visits: 177
Jeff,

Thanks for your kind words. You put a big smile on my face. I don't what else to say but, "Thank you!"

The tutorials would use both English, that is, be without the fancy math symbols, and provide reasonable examples, that is, they should be simple enough to avoid obfuscation and complex enough to represent reality.

Thanks again.

Mike
Post #1128675
Posted Monday, June 20, 2011 7:14 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:25 AM
Points: 35,959, Visits: 30,253
I'm definitely for that. Although I can certainly understand why some people might be interested in all the fancy relational and hierarchical calculus, some folks just want a simple explanation so they can build something and maintain it.

I did something a while ago and I'm going to convert it to an article (it's finally boiling up to the top of my list of things to do). I don't want to post what I have here because it's not ready as an article, so I'll make you a simple trade... I'll send you what I have if you provide an honest (even if it's bad) critique on the methods and general direction before I turn it into an article, please.

And don't be such a stranger on these forums. It's a great place to learn new things and maybe even vent a little. I've learned more by solving other people's problems than I could have if I did 1,000 tutorials or had a 1,000 job history.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1128681
Posted Monday, June 20, 2011 7:28 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 16, 2012 10:19 AM
Points: 46, Visits: 177
Jeff,

I would be happy to review your preliminary article. I'm sure I will learn a great deal in the process. I might even be helpful.

I will try not to be a stranger to SQL Server Central. Two of my joys in life are working on puzzles and discussing software development issues.

Send it on. If you provide a private email address, I'll send it back to you there.

Regards,

Mike
Post #1128684
Posted Tuesday, June 21, 2011 6:23 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
Mike, on your point about specialization vs Swiss-army-knife devs, yes, more companies would benefit from specialization than are aware of it.

If you turned that post into an editorial, and sent it to Steve (the editor for this site), I think he'd love it. Would probably generate some interesting discussions too.


- 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
Post #1128943
Posted Tuesday, June 21, 2011 3:22 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, April 11, 2014 8:50 AM
Points: 77, Visits: 398
Not sure if it's relevant now, but in case... here's my code for an example where a list of Top Bills of Materials is used and a product structure table, to create a BOM explosion.

(Thanks also go to Jeff M during its birth):

------------------------------------------------------------------------
-- BOM explosion example, using a seed list
-- Thanks to jhood for initial example, modified to explode a list of top bills of material for dm:

-- Drop temp tables if they exist
IF OBJECT_ID('TempDB..#dmBOM','U') IS NOT NULL
DROP TABLE #dmBOM
IF OBJECT_ID('TempDB..#dmBOM_expanded','U') IS NOT NULL
DROP TABLE #dmBOM_expanded
IF OBJECT_ID('TempDB..#dmBOM_final_output','U') IS NOT NULL
DROP TABLE #dmBOM_final_output

-- Input table is the product structure table
-- Desired output is a table containing all unique parent and child parts

-- Create the product structure table,
-- each top bill of material with all of their child components
CREATE TABLE #dmBOM (parent_item varchar(20), child_item varchar(20))

INSERT INTO #dmBOM VALUES ('1', '2')
INSERT INTO #dmBOM VALUES ('1', '3')
INSERT INTO #dmBOM VALUES ('1', '4')
INSERT INTO #dmBOM VALUES ('1', '5')
INSERT INTO #dmBOM VALUES ('1', '6')
INSERT INTO #dmBOM VALUES ('1', '7')
INSERT INTO #dmBOM VALUES ('2', 'a')
INSERT INTO #dmBOM VALUES ('2', 'b')
INSERT INTO #dmBOM VALUES ('2', 'c')
INSERT INTO #dmBOM VALUES ('3', '12')
INSERT INTO #dmBOM VALUES ('3', '13')
INSERT INTO #dmBOM VALUES ('3', '14')
INSERT INTO #dmBOM VALUES ('4', 'x')
INSERT INTO #dmBOM VALUES ('4', 'y')
INSERT INTO #dmBOM VALUES ('4', 'z')
INSERT INTO #dmBOM VALUES ('9', 'd')
INSERT INTO #dmBOM VALUES ('9', 'e')
INSERT INTO #dmBOM VALUES ('9', 'f')
INSERT INTO #dmBOM VALUES ('a', 'g')
INSERT INTO #dmBOM VALUES ('b', 'h')
INSERT INTO #dmBOM VALUES ('c', 'i')

-- Create the intermediate output table and initialise it with all the
-- top bills of material that need exploding
CREATE TABLE #dmBOM_expanded
(item varchar(20), i_level INT)

INSERT INTO #dmBOM_expanded (item, i_level)
SELECT distinct parent_item, 0 AS i_level
FROM #dmBOM

-- Create and set a level counter, then explode the hierarchy using the product
-- structure table, then remove duplicates to get the final output:
DECLARE @CurrentLevel INT
SET @CurrentLevel = 0
WHILE @@ROWCOUNT > 0
BEGIN
SET @CurrentLevel = @CurrentLevel + 1
INSERT INTO #dmBOM_expanded (item, i_level)
SELECT p.child_item as item, @CurrentLevel AS i_level
FROM #dmBOM as p
INNER JOIN #dmBOM_expanded as h
ON p.parent_item = h.item
AND h.i_level = @CurrentLevel - 1
END
select distinct item
into #dmBOM_final_output
from #dmBOM_expanded
order by item

select * from #dmBOM order by parent_item, child_item
select * from #dmBOM_expanded order by item, i_level
select * from #dmBOM_final_output order by item

-----------------------------------------------------------------------------------
-- Cleanup temporary tables when verified
IF OBJECT_ID('TempDB..#dmBOM','U') IS NOT NULL
DROP TABLE #dmBOM
IF OBJECT_ID('TempDB..#dmBOM_expanded','U') IS NOT NULL
DROP TABLE #dmBOM_expanded
IF OBJECT_ID('TempDB..#dmBOM_final_output','U') IS NOT NULL
DROP TABLE #dmBOM_final_output
-----------------------------------------------------------------------------------

Regards, Dave

Post #1129362
Posted Tuesday, June 21, 2011 7:17 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 16, 2012 10:19 AM
Points: 46, Visits: 177
Hi GSquared,

Are there any guidelines for an editorial as opposed to a post?

Mike
Post #1129410
Posted Wednesday, June 22, 2011 8:13 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
MikeAngelastro-571287 (6/21/2011)
Hi GSquared,

Are there any guidelines for an editorial as opposed to a post?

Mike


Check the "Write for us" link on the left side of the site.


- 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
Post #1129688
Posted Thursday, July 14, 2011 7:51 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 16, 2012 10:19 AM
Points: 46, Visits: 177
Hi GSquared,

I sent a proposed article to the article email address for SQL Server Central on June 27 but haven't received any notification that it was received. How many days does it typically take to receive a status on article submissions?

Thanks,

Mike
Post #1142147
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse