Paul White (2/27/2010)

---

Chris,

Turns out all that is needed to get rid of the index spool is to define the clustered index as UNIQUE. Given that information, a better plan is produced (without the extra index spool), and the recursive CTE now starts to return results after four seconds on my laptop. Well done!

Paul

Paul White (2/27/2010)

---

This is a method called 'set-based iteration' by MVP Hugo Kornelis, which was first brought to my attention here in a discussion of one of Jeff's fine articles.

I have shamelessly re-used Jeff's fine test script, up to the point marked 'Solution to the problem starts here'. The following code runs from that point, and again draws heavily on Jeff's work. I hope he won't mind.

Paul White (2/27/2010)

---

Chris,

Turns out all that is needed to get rid of the index spool is to define the clustered index as UNIQUE. Given that information, a better plan is produced (without the extra index spool), and the recursive CTE now starts to return results after four seconds on my laptop. Well done!

Paul

nathan 7372 (2/27/2010)

---

I'm overwhelmed with all the help. When I first posted this problem I figured it might be quite some time if ever that someone came up with a solution. Thank you all so much. I will be running these queries and puzzling out how they work on monday. I will let you all know how it goes. Once again thank you so much.

Jeff Moden (2/27/2010)

---

Was that for the 100k rows, Paul?

Jeff Moden (2/27/2010)

---

You only have yourself to thank. Thanks for taking the time to read the "etiquette" article I led you to... I just knew a bunch of great people would jump in on this one if they had a little data to work with. Well done on the data you posted, Nathan.