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