CTE Performance

  • Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/phe/2926.asp

  • the scripts are unavailable.

    Nice article.


    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

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

    - 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

  • 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.

    ATBCharles Kincaid

  • 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.

  • 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.

  • 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

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • 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.



  • I agree with Grant. I have to see the queries.

    * Noel

  • 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.


  • The links should be fixed and my apologies to everyone. We've tightened security and I forgot .sql scripts will not download. They are renamed to .txt.

  • Ah ha.  Right you are.  So we can only go on the available evidence.  Your charts (even with broken links ) and the pictures of the execution plans are enough to make me shy away from CTEs in this looping type of scenario.

    Always the compromises: Ease of code vs. production performance.  The folks in Redmond don't like to talk about benchmarks.  It's groups like this where the authors take the time to write up a finding and readers contribute their experience that are of great benefit.


    ATBCharles Kincaid

  • It's kind of funny. I posted similar question about CTE performance almost a year ago:


    but I guess it was too early for anybody to respond.

  • nice article, but besides stating CTE as a neat way to implement recursive queries. this article is missing concept & meaning about CTE.

  • The performance isn't necessarily all bad. I had a query in which a large portion of it was splitting a delimited string into multiple columns. I originally used a function I used on SQL 2000 which was basically a loop using CHARINDEX and SUBSTRING to iterate over each "column" in the string and split it into a table. I rewrote the function to use a recursive CTE rather than the loop, but everything else was essentially the same. The stored procedure I was using it in that was taking over 50 minutes. After that single change, it was running around 16 minutes.

  • The intial issue with the Key Lookup in test case 2 was caused with a poor index. 

    If the following index is added, the CTE is actually outperforming the loops in all cases for me.

    I have used CTEs a lot, and with a little work, you can get them to almost always out perform loops.  Of course there are always the exception.


    INDEX ix_Groups_ParentGroupId_IN_GroupId_GroupName ON dbo.Groups (ParentGroupId) INCLUDE (GroupId, GroupName)

Viewing 15 posts - 1 through 15 (of 32 total)

You must be logged in to reply to this topic. Login to reply