SQL Server

Using a CTE for Heirarchical Query




A
project I did a few years back was for a large company with thousands of
employees.  The project was essentially building a system to allow managers
to evaluate employees and give them quarterly bonuses.  We had to take raw
data out of a PeopleSoft repository and build the company heirarchy so the
employees showed up on the appropriate managers screen, as well as so the
bonuses for employees at the lower levels rolled up to the higher managers level
so they could manage their department budgets appropriately.  The biggest
pain in this process was building queries to create this hierarchical
structure…And man, was it slow!  We eventually did some performance
tuning on it and got it to be acceptable, but it was still quite a query. 
Many lines of code!


I wish I had that same project on my new project
list for later this year!  Yukon is introducing the concept of “Common
Table Expression”, or CTE.  It essentially lets you perform a recursive
query…perfect for a hierarchy!


TheServerSide.NET has an article
on this exact subject
.


One of these days, I’d be interested in (finally)
installing a copy of Yukon and running a CTE and compare the performance with
one of the “old” queries we used in that project.  My guess is that it
would be a big improvement.

Leave a Reply

Your email address will not be published. Required fields are marked *