Hierarchical data is very difficult in relational structures. If you can refactor the table you might get a win from using the built-in hierarchy datatype, which is CLR based under the covers. I would recommend checking out stuff written by Itzik Ben-Gan too. He has done a number of articles (and book topics I think) on the topic of hierarchies. Check SQLMag.com. There is a neat trick you can do with binary casting that can sometimes get things done without bajillions of reads/iterations.