April 13, 2010 at 11:56 am
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
April 13, 2010 at 12:19 pm
If you provide the DDL (CREATE TABLE statement(s)) for the table(s) involved, sample data (as a series of INSERT INTO statements) for the table(s), expected results based on sample the sample data you may be surprised to find a better way of accomplishing what you are attempting to do with what appears to be a recursive CTE which won't scale well at all.
April 13, 2010 at 12:53 pm
Ensure that reportID, roleID are indexed on table #tbl.
Follow Lynn's advice, because there's almost certainly a faster way of doing this - providing sufficient detail will enable folks to figure it out for you.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 14, 2010 at 5:14 am
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 scriptWITH 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
Execution plan of both the servers can give you better picture.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
April 14, 2010 at 10:47 am
For an example of what I'm trying to do and the method I used, please see http://www.projectdmx.com/tsql/rowconcatenate.aspx
I followed the example under the Recursive CTE methods.
April 14, 2010 at 10:51 am
If you can't be bothered with posting what is asked, I guess I can't be bothered with helping you. Search SSC, you'll find numerous examples on this site that will work better than the recursive method you are attempting to use.
April 14, 2010 at 11:00 am
ychen 55059 (4/14/2010)
For an example of what I'm trying to do and the method I used, please see http://www.projectdmx.com/tsql/rowconcatenate.aspxI followed the example under the Recursive CTE methods.
Please provide the remaining pertinent information so relevant answers can be given and lessen any frustration that could develop as a part of trying to solve this problem.
Table definitions are essential. Sample data and output is also essential.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 14, 2010 at 1:03 pm
Sample data set:
select 'Report1' as reportID,'Browser' as RoleID, 'YChen' as User2
into #tbl
UNION
select 'Report1' as reportID,'Browser' as RoleID,'AChen' as User2
UNION
select 'Report1' as reportID,'Browser' as RoleID,'BChen' as User2
UNION
select 'Report2' as reportID,'Browser' as RoleID,'YChen' as User2
UNION
select 'Report2' as reportID,'Browser' as RoleID,'XChen' as User2
UNION
select 'Report2' as reportID,'Browser' as RoleID,'BChen' as User2
UNION
select 'Report1' as reportID,'Writer' as RoleID,'YChen' as User2
UNION
select 'Report1' as reportID,'Writer' as RoleID,'XChen' as User2
reportIDRoleIDUser2
Report1BrowserAChen
Report1BrowserBChen
Report1BrowserYChen
Report1WriterXChen
Report1WriterYChen
Report2BrowserBChen
Report2BrowserXChen
Report2BrowserYChen
~~~
Desired output:
reportIDroleIDlist
Report1BrowserAChen, BChen, YChen
Report1WriterXChen, YChen
Report2BrowserBChen, XChen, YChen
~~~
CTE method used (takes a long time with many records):
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
and c.roleID = p.roleID
WHERE p.user2 > c.user2 )
SELECT reportID, roleID, 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
~~~
Thank you for your help.
April 14, 2010 at 1:10 pm
Missing DDL for the table you are.
April 14, 2010 at 1:14 pm
Would the 'into #tbl ' above suffice? I wanted to be consistent with the #tbl referenced in the CTE query.
April 14, 2010 at 1:15 pm
Also, your code won't work as is when run on a system using a case sensitive collation.
April 14, 2010 at 1:20 pm
Here is my code:
select distinct
reportID,
RoleID,
stuff((select ', ' + t2.User2
from #tbl t2
where t2.reportID = t1.reportID and t2.RoleID = t1.RoleID
for xml path('')),1,2,'') as List
from #tbl t1;
April 14, 2010 at 1:33 pm
ychen 55059 (4/14/2010)
Would the 'into #tbl ' above suffice? I wanted to be consistent with the #tbl referenced in the CTE query.
It works, just not the way you should do it. It would be better to separate your DDL from the load of your data.
April 14, 2010 at 3:24 pm
Thank you so much. That works beautifully.
I'm not familiar with XML. Could you point me to some additional resources so that I can understand what your code actually does?
Thanks again.
April 14, 2010 at 3:28 pm
ychen 55059 (4/14/2010)
Thank you so much. That works beautifully.I'm not familiar with XML. Could you point me to some additional resources so that I can understand what your code actually does?
Thanks again.
Not that familiar myself, just something I picked up here on SSC. Hopefully someone else reading this thread can provide you with a link. I'd have to do some searching and I am getting ready to leave to watch (yes watch, not ref) a soccer game.
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply