Thursday night, at 6:00 (or so) I will be speaking in Richmond (http://richmondsql.org/cs2007/ ), talking about How to Implement a Hierarchy using SQL Server. The abstract is:
One of the most common structures you will come across in the real world is a hierarchy (either a single parent "tree" or a multi-parent "graph"). Many systems will implement the obvious examples, such as a corporate managerial structure or a bill of materials. It turns out that almost any many-to-many relationship can be used as a hierarchy to find the relationship of parent rows to child rows (for example, the relationship of actors to movies they’ve been in). In this session, we’ll discuss several different implementations of hierarchies that you can use to optimize your hierarchy implementations and put into practice immediately, with ready-made T-SQL examples.
Hierarchies are one of my favorite "fun" topics, as they are interesting for a few reasons. First, they have very common usages that most people come across, and second, they make for fairly interesting example code and performance testing. In my slides (and downloads), I will have examples where I generate several trees, including 3400, 41000, 52000, and even a 1.2 million node tree (which, using my slightly slow generator, took like 16 hours on my i7 laptop) along with 5 rows of sales data for every root node of the tree. It is kind of interesting to me to see how well the different tree implementations behave using each sized tree. I may even get a chance this week to toss the tree structures into in-memory tables and check their performance (but if not, it certainly will be included in what I am going to announce in the next paragraph.)
The only downside is that (not unlike most of my presentations) I have way too much material for 1 hour (or even 2). So I will be working in the upcoming future (hopefully by Devlink) to put out my directors cut video of this and several other presentations I have that are just too unwieldy for a non-precon sized session. I will officially announce this effort soon (along with a realistic schedule!), but it has been met with many life issues. I had a few weeks set aside for this task, but the weekend I sat down to record videos, I got sick and have had to push things back.
However, all of the code will be available for download, and my email address is no secret (drsql@Hotmail.com) and I am always happy (if sometimes slow) to answer questions, take criticisms, or paypal payments at that address, so feel free to do either with the code when it is posted at http://www.drsql.org/Pages/Presentations.aspx