Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

CTE Expand / Collapse
Author
Message
Posted Tuesday, April 13, 2010 11:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 24, 2011 12:39 PM
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


Post #902657
Posted Tuesday, April 13, 2010 12:19 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:33 PM
Points: 22,475, Visits: 30,157
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.




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)
Post #902670
Posted Tuesday, April 13, 2010 12:53 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:05 AM
Points: 1,059, Visits: 5,743
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
Post #902695
Posted Wednesday, April 14, 2010 5:14 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, March 14, 2014 2:19 AM
Points: 2,820, Visits: 3,916
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
Post #903007
Posted Wednesday, April 14, 2010 10:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 24, 2011 12:39 PM
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.


Post #903355
Posted Wednesday, April 14, 2010 10:51 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:33 PM
Points: 22,475, Visits: 30,157
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.


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)
Post #903358
Posted Wednesday, April 14, 2010 11:00 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:11 PM
Points: 20,455, Visits: 14,075
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #903371
Posted Wednesday, April 14, 2010 1:03 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 24, 2011 12:39 PM
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.
Post #903483
Posted Wednesday, April 14, 2010 1:10 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:33 PM
Points: 22,475, Visits: 30,157
Missing DDL for the table you are.



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)
Post #903492
Posted Wednesday, April 14, 2010 1:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 24, 2011 12:39 PM
Points: 9, Visits: 36
Would the 'into #tbl ' above suffice? I wanted to be consistent with the #tbl referenced in the CTE query.
Post #903498
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse