Using a CTE as a Tally Table

Question

Using a CTE as a Tally Table

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

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

thisisfutile Hall of Fame Points: 3668 More actions · Answer 1

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: 1003863 More actions · Answer 2

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.

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