CTE Performance

  • peterhe

    SSChampion

    Points: 11363

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

  • Johan Bijnens

    SSC Guru

    Points: 134265

    the scripts are unavailable.

    Nice article.

    Johan


    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[/url] :alien: but most of the time this is me :hehe:

  • Charles Kincaid

    SSChampion

    Points: 13593

    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

  • Laxmi Narayan

    SSC Veteran

    Points: 212

    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.

  • Vic Kirkpatrick-173212

    SSCommitted

    Points: 1801

    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.

  • Grant Fritchey

    SSC Guru

    Points: 395661

    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 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • peterhe

    SSChampion

    Points: 11363

    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.

     

     

  • noeld

    SSC Guru

    Points: 96590

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


    * Noel

  • peterhe

    SSChampion

    Points: 11363

    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.

     

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716661

    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.

  • Charles Kincaid

    SSChampion

    Points: 13593

    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

  • VadimK

    SSC-Addicted

    Points: 411

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

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=149&messageid=289849

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

  • sqldba-294117

    Hall of Fame

    Points: 3486

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

  • Tito Jermaine

    SSC Enthusiast

    Points: 142

    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.

  • Jason Adams

    SSC Eights!

    Points: 843

    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.

    CREATE

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

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

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