Post reply

Performance Issue - CTE

  • July 5, 2011 at 7:35 am

    #238976

    Hi,

    I had a performance issue where in the sproc was taking millions of reads. The only modifcation i made was, in the cte within the sproc, i removed the anchore part (which was joining tables and retrieving data) and dumped it in to a table variable, then used the variable as anchore in the CTE. This brought down reads to thousands.

    So the big question here is, the anchor only executes once, then why is this making such a big difference?

    PFA the exec plans before and after tuning. Scroll down to the CTE part and you ll see the major difference between Before and After is the Concatenation Cost. Why?

    Regards - Yasub

  • July 5, 2011 at 9:11 am

    #1348346

    yasubmj (7/5/2011)

    So the big question here is, the anchor only executes once, then why is this making such a big difference?

    Because recursive CTE's suck. 😀 They're actually worse than cursors.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 5, 2011 at 10:21 pm

    #1348728

    I was expecting a more technical explanation Jeff!!! 😛

    http://blogs.msdn.com/b/craigfr/archive/2007/10/25/recursive-ctes.aspx

    Craig says clearly here that the anchor part is executed only once, then why is my change making such a big difference 🙁

  • July 7, 2011 at 12:02 pm

    #1349911

    yasubmj (7/5/2011)

    I was expecting a more technical explanation Jeff!!! 😛

    http://blogs.msdn.com/b/craigfr/archive/2007/10/25/recursive-ctes.aspx

    Craig says clearly here that the anchor part is executed only once, then why is my change making such a big difference 🙁

    This has nothing to do with recursion and everything to do with estimated/actual rowcount differences. Did you actually look at your query plans? See that big fat nested loop join AND bookmark lookup line of 4 MILLION rows with an estimated rowcount of 392??? THAT is the problem here. :w00t:

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 4 posts - 1 through 3 (of 3 total)

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