ychen 55059 (4/13/2010)

---

I've just started to use CTE's to concatenate individual row values into 1 field and I'm liking the results. However, 1 of my CTE queries is taking a very long time to run (on 1040 records). The same query runs very fast on another server with a differents set of data (145 records). The parameterization is set to SIMPLE for both databases off which the queries are run. What is causing the first CTE query to take so long? Is it the number of records? How can I make the query run faster? Below is the script

WITH CTE ( reportID, roleID, list, user2, length )

AS ( SELECT reportID, roleID, CAST( '' AS VARCHAR(8000) ), CAST( '' AS VARCHAR(8000) ), 0

FROM #tbl

GROUP BY reportID, roleID

UNION ALL

SELECT p.reportID, p.roleID, CAST( list +

CASE WHEN length = 0 THEN '' ELSE ', ' END + RTRIM(p.[user2]) AS VARCHAR(8000) ),

CAST(p.[user2] AS VARCHAR(8000)), length + 1

FROM CTE c

INNER JOIN #tbl p

ON c.reportID = p.reportID

WHERE p.user2 > c.user2 )

SELECT reportID, roleID, list

into #list

FROM ( SELECT reportID, roleID, list,

RANK() OVER ( PARTITION BY reportID, roleID ORDER BY length DESC )

FROM CTE ) D ( reportID, roleID, list , rank )

WHERE rank = 1