tssopa (6/19/2013)
... I had read how efficient and fast recursive CTE's are compared to CURSOR's...
Typically they're not. You could try splitting out a temporary table from the CTE.
IF OBJECT_ID('tempdb..#Base') IS NOT NULL DROP TABLE #Base;
SELECT A.ShapeID AS ShapeID_A, B.ShapeID AS ShapeID_B
INTO #Base
FROM dbo.OverlappingShapes AS A
INNER JOIN dbo.OverlappingShapes AS B
ON A.Shape.STIntersects(B.Shape) = 1
AND A.ShapeID < B.ShapeID;
-- May want to add indexes to #Base here .. CREATE INDEX IX ON #Base(ShapeID_A);
WITH
Recur AS (
SELECT b.ShapeID_A,
b.ShapeID_B
FROM #Base b
WHERE NOT EXISTS(SELECT * FROM #Base b2 WHERE b2.ShapeID_B=b.ShapeID_A)
UNION ALL
SELECT r.ShapeID_A,
b.ShapeID_B
FROM #Base b
INNER JOIN Recur r ON r.ShapeID_B = b.ShapeID_A),
Results AS (
SELECT ShapeID_A,
ShapeID_B
FROM Recur
UNION
SELECT a.ShapeID,
a.ShapeID
FROM dbo.OverlappingShapes a
WHERE NOT EXISTS (SELECT * FROM Recur r WHERE r.ShapeID_B=a.ShapeID))
SELECT ShapeID_B AS ShapeID,
DENSE_RANK() OVER(ORDER BY ShapeID_A) AS ClusterID
FROM Results;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537