Using a CTE as a Tally Table

  • thisisfutile

    Hall of Fame

    Points: 3496

    Jeff Moden - Thursday, August 9, 2018 8:22 AM

    BluePeter - Thursday, August 9, 2018 5:07 AM

    Eirikur Eiriksson - Thursday, August 9, 2018 2:47 AM

    Be careful here, the recursive CTE is much slower than the inline tally table CTE method I posted, strongly advice against using a recursive CTE for this purpose.
    😎

    Agreed, it doesn't scale upwards, 10,000 is slower with a recursive cte.
    But no difference for 52 (or 53)
    I'll keep yours in mind, never seen it before.

    The reason you think there is no difference is because of what you're measuring and the way your measuring it.  There's another thing to consider, as well...

    If you practice the wrong way just because of low row counts, you'll never get good at the right way.  You also don't have to actually build the cCTE method (Cascading CTEs rather than Recursive CTEs) each and every time you want a sequence of numbers.  You can easily build an iTVF (Inline Table Valued Function) that will do that for you and, unlike scalar functions and mTVFs (Mult-statment Table Valued Functions), provides no additional hit if you refer to it within other iTVFs.

    Jeff,

    Big fan here and well aware of your research and articles on tally tables (Click here, for the uninitiated).  I'm curious, do you ever use an actual tally table anymore or is it strictly CTE's for you?  I have to admit, my Tally table is just too easy to lean against when I'm testing, though I do tend to put the CTE variety in my production code.

  • Jeff Moden

    SSC Guru

    Points: 996645

    thisisfutile - Friday, August 10, 2018 8:09 AM

    Jeff Moden - Thursday, August 9, 2018 8:22 AM

    BluePeter - Thursday, August 9, 2018 5:07 AM

    Eirikur Eiriksson - Thursday, August 9, 2018 2:47 AM

    Be careful here, the recursive CTE is much slower than the inline tally table CTE method I posted, strongly advice against using a recursive CTE for this purpose.
    😎

    Agreed, it doesn't scale upwards, 10,000 is slower with a recursive cte.
    But no difference for 52 (or 53)
    I'll keep yours in mind, never seen it before.

    The reason you think there is no difference is because of what you're measuring and the way your measuring it.  There's another thing to consider, as well...

    If you practice the wrong way just because of low row counts, you'll never get good at the right way.  You also don't have to actually build the cCTE method (Cascading CTEs rather than Recursive CTEs) each and every time you want a sequence of numbers.  You can easily build an iTVF (Inline Table Valued Function) that will do that for you and, unlike scalar functions and mTVFs (Mult-statment Table Valued Functions), provides no additional hit if you refer to it within other iTVFs.

    Jeff,

    Big fan here and well aware of your research and articles on tally tables (Click here, for the uninitiated).  I'm curious, do you ever use an actual tally table anymore or is it strictly CTE's for you?  I have to admit, my Tally table is just too easy to lean against when I'm testing, though I do tend to put the CTE variety in my production code.

    As with all else, "It Depends".  If I need every millisecond of performance I can get, I'll use the physical Tally table with the understanding that, although they won't matter in this case, it'll cause a shedload of logical reads.  I also have a Tally function that I can start at 0 or 1 if I need to avoid the reads for one reason or another.  That function will also allow me to go to ridiculous counts for experimental purposes.

    I usually use the Tally function on forums because too many people seem to either have a personal problem with using a physical helper table or they're simply not allowed to have such tables.  If they not allowed either table or function, then I end up posting the code inline.  And, no... I'll never use an rCTE for such things even if it's just for 10 rows.  It's just not the right way to do it regardless of rowcount and especially since I've proven (many times) that a well written WHILE loop will be it in all cases of incremental counting.

    And thank you for the very kind feedback.

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

Viewing 2 posts - 16 through 17 (of 17 total)

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