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. [font="Arial Black"]What do you think?[/font]
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
Change is inevitable... Change for the better is not.