SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


CTE


Author
Message
ychen 55059
ychen 55059
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 36
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
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39886 Visits: 38563
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
ChrisM@home
ChrisM@home
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2070 Visits: 10375
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.


Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5326 Visits: 4076
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


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;-)
ychen 55059
ychen 55059
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 36
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.
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39886 Visits: 38563
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32755 Visits: 18559
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.aspx

I 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

ychen 55059
ychen 55059
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 36
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


reportID RoleID User2
Report1 Browser AChen
Report1 Browser BChen
Report1 Browser YChen
Report1 Writer XChen
Report1 Writer YChen
Report2 Browser BChen
Report2 Browser XChen
Report2 Browser YChen


~~~
Desired output:

reportID roleID list
Report1 Browser AChen, BChen, YChen
Report1 Writer XChen, YChen
Report2 Browser BChen, 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.
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39886 Visits: 38563
Missing DDL for the table you are.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
ychen 55059
ychen 55059
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 36
Would the 'into #tbl ' above suffice? I wanted to be consistent with the #tbl referenced in the CTE query.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search