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 1234»»»

CTE Performance Expand / Collapse
Author
Message
Posted Wednesday, March 14, 2007 10:21 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Saturday, May 3, 2014 6:30 PM
Points: 531, Visits: 430
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/phe/2926.asp


Post #351522
Posted Monday, May 7, 2007 11:50 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:40 AM
Points: 7,001, Visits: 8,439

the scripts are unavailable.

Nice article.



Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #363895
Posted Tuesday, May 8, 2007 12:34 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962
You said, "The plans do not give any clue to their performance difference."  But there ARE indications.  The thickness of the arrows are a real indicator.  The thicker the arrow, the more rows in that part of the plan.  I can see that there are thicker arrows with the CTE.  More rows will, generally, take more time.  Hover with the mouse over the arrows to see the number of rows.

ATB

Charles Kincaid

Post #363901
Posted Tuesday, May 8, 2007 4:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:08 PM
Points: 108, Visits: 170
Hey It was a nice article...
But it would have been more exciting if the scripts were available...

Anyway I would look forward to see the script as well to have a better understanding in it...

Thanks and Really gr8 effort to put such things on board.
Post #363922
Posted Tuesday, May 8, 2007 5:44 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, November 13, 2008 9:13 AM
Points: 499, Visits: 76
Wow, this is really disappointing. I hadn't yet tested much with CTE's on the performance, as the db I am working with now is relatively small, but I just kind of figured that since CTE's were working in a set-based fashion it would perform better than the looping. Hmmmm.
Post #363940
Posted Tuesday, May 8, 2007 6:17 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:32 PM
Points: 15,517, Visits: 27,895

I'd sure like to see the queries before I can completely agree with the conclusions.



----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #363951
Posted Tuesday, May 8, 2007 6:46 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Saturday, May 3, 2014 6:30 PM
Points: 531, Visits: 430

Thanks everyone.

The scripts were all submitted to SQLCentral.com with the article. I am not sure why the link was broken.

To Charles Kincaid:

What I meant in "The plans do not give any clue to their performance difference."  is that the CTE execution plan doesn't give the details in the recursive execution. The row number (the thickness of the arrow) is actually the final output. SQL server generates only one execution plan for a CTE.

The execution plan of the loop method showed in the article is for the one execution of the looping statement. You know, SQL server generates one execution plan for every execution of the same statement in the loop.  So the arrow (row number) is thinner in each plan.

 

 




Post #363966
Posted Tuesday, May 8, 2007 7:03 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 5:51 AM
Points: 6,266, Visits: 2,028
I agree with Grant. I have to see the queries.


* Noel
Post #363976
Posted Tuesday, May 8, 2007 7:11 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Saturday, May 3, 2014 6:30 PM
Points: 531, Visits: 430

I just sent an email to Steve about the broken of the link to the scripts.

If it's not possible to fix the link, I can send the scripts to your guys later.

 




Post #363981
Posted Tuesday, May 8, 2007 7:34 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, July 20, 2014 11:55 AM
Points: 1,945, Visits: 2,860
You might also want to try a Nested Sets model against a CTE and a loop. For that depth, it should beat both of them as well as use only one simple query.



Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #363990
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse