TheSQLGuru (6/19/2013)
tssopa (6/19/2013)
Okay, I am a little dissapointed. I had read how efficient and fast recursive CTE's are compared to CURSOR's, however when I run the CTE against my data set of 1000+ records it takes far too long. When I run the CURSOR it only takes 4 seconds. The shape table has the ShapeID as the primary key and the shape field does have a spatial index. Any thoughts on why the recursive CTE is taking so long?Recursive CTEs SUCK @SS and should be avoided at almost all costs, IMNSHO. I only use them when there is NO other recourse or testing shows they are optimal for a specific data processing need.
Very strong anti-rCTE statement there!
I'll protest. They are but a tool. Often times they are misused but for some things they are the best option available.
Jeff Moden once suggested to me that any rCTE can be rewritten as a set-based loop. The loop basically looks something like this (using a Temp table):
INSERT INTO #Temp
-- rCTE anchor leg
WHILE -- terminating criteria
INSERT INTO #Temp
-- rCTE recursive leg
END
This can be faster than the rCTE (I did this in one of my articles if you're interested in the proof).
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St