Tally generator

Question

Post reply

Tally generator

Sergiy

SSC Guru

Points: 110208
More actions
February 16, 2017 at 8:54 pm

#340813

Comments posted to this topic are about the item Tally generator
_____________
Code for TallyGenerator

Viewing 14 posts - 1 through 13 (of 13 total)

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

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 1

Can't believe whomever the editor was didn't catch the missing "y" in the title. They can fix it for you, Sergiy.

Good to see you've finally picked up the pen on SSC.

--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)

Sergiy SSC Guru Points: 110208 More actions · Answer 2

I had status "Commited" for 2 years in a busy Production system.

Not a soul complained.

Till I spotted and fixed it myself.

🙂

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 3

Sergiy - Thursday, February 16, 2017 10:36 PM
I had status "Commited" for 2 years in a busy Production system.Not a soul complained.Till I spotted and fixed it myself.:-)

I'm more concerned that people won't find your script if they do a site search for the word "Tally".

--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)

Sergiy SSC Guru Points: 110208 More actions · Answer 4

I posted it only because it's used in the article scripts, so if it gets published I can refer to this post.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 5

Oh yeah... I got that from the beginning. Still, it's useful by itself and it would be nice if people could find it. But, it's your script. I'm just making a suggestion.

--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)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Nice, Sergiy, and very useful.
I'm guessing that you've checked out all of the alternatives to get rid of the row count spool. I've only had a little time to play today and came up with this:

DECLARE

@StartValue bigint = null, -- -32768,

@EndValue bigint = null, -- 32767,

@Rows INT = 980, -- number of rows to bereturned. Used only when either @StartValue or @EndValue is not supplied

@Increment smallint=3;

WITH BaseNum (N) as (

select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all

select 1 union all select 1 union all select 1 union all select 1 union all select 1

),

L1 (N) as (SELECT 1 FROM BaseNum bn1 CROSS JOIN BaseNum bn2),

L2 (N) as (SELECT 1 FROM L1 a1 CROSS JOIN L1 a2),

_Tally (N) as (

SELECT TOP(ISNULL(@EndValue - @StartValue + @Increment,@Rows)/@Increment)

rn= ISNULL(@StartValue,0) + ((ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1)*@Increment)

FROM L2 a1 CROSS JOIN L2 a2 CROSS JOIN L2 a3

)

SELECT * FROM _Tally

[/code]

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

George W (no relation) Ten Centuries Points: 1339 More actions · Answer 7

I actually came to the article because I wanted to find out what a "Tall Generator" was :).

Sergiy SSC Guru Points: 110208 More actions · Answer 8

ChrisM@Work - Friday, February 17, 2017 9:38 AM
Nice, Sergiy, and very useful.
I'm guessing that you've checked out all of the alternatives to get rid of the row count spool. I've only had a little time to play today and came up with this:
DECLARE
       @StartValue bigint = null, -- -32768,
     @EndValue bigint = null, -- 32767,
       @Rows INT = 980, -- number of rows to bereturned. Used only when either @StartValue or @EndValue is not supplied
     @Increment smallint=3;

WITH BaseNum (N) as (
       select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all
       select 1 union all select 1 union all select 1 union all select 1 union all select 1
       ),
       L1 (N) as (SELECT 1 FROM BaseNum bn1 CROSS JOIN BaseNum bn2),
       L2 (N) as (SELECT 1 FROM L1 a1 CROSS JOIN L1 a2),
       _Tally (N) as (
              SELECT TOP(ISNULL(@EndValue - @StartValue + @Increment,@Rows)/@Increment)
                     rn= ISNULL(@StartValue,0) + ((ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1)*@Increment)
              FROM L2 a1 CROSS JOIN L2 a2 CROSS JOIN L2 a3
              )
SELECT * FROM _Tally
[/code]

I have an unfinished "v.2." script which takes better care of spooling, and is also meant to work with odd combinations of input parameters: negatives, opposite signs, nulls here and there, etc.
I'm still don't feel like I collected all the test cases.
🙂

_____________
Code for TallyGenerator

o103452@rtrtr.com Ten Centuries Points: 1025 More actions · Answer 9

Hi Sergiy, thanks for the script. Please keep us posted on your updated "V2" script. 🙂

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 10

Wow... it's amazing how fast machines have gotten. I just tested Sergiy's, Chris', and my fnTally function for simply generating 10 million rows to a bitbucket variable. (No JOIN, though, so I don't know if Chris' code has the "optimizer moved the TOP" problem or not, which cause all of the rows to be generated before TOP is applied even though it should be impossible).

In earlier days, all that stuff to do increments and offsets took a lot of extra time, which is why I've always gone with just the simple generation from 0 or 1 to N. That's no longer true. I'm still beating (duration) both of the queries for such simple generation but only by (worst case) a couple of hundred milliseconds (remember... that's on 10 MILLION rows), although it goes parallel and costs a couple hundred milliseconds more CPU time. I may have to reconsider my position on all the functionality that you good folks have added.

--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)

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 11

Lynn Pettis wrote one back in 2009, as well.

https://www.sqlservercentral.com/articles/the-dynamic-tally-or-numbers-table

If I get the chance, I'll add his to the mix, as well.

Anyway, thanks again for the article and the code, Sergiy.

--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)

Sergiy SSC Guru Points: 110208 More actions · Answer 12

Yes, that’s what was the point of the function - provide convenience with minimal impact on performance.

actually, overall performance may benefit from using carefully crafted code within the function, comparing to ad-hoc manipulations, when you need not just a simple sequence from 1 to million.

as for “top N” problem - I also noticed it when designing the function. That’s why I used this clause twice. So when you need a small set the TOP limitation comes to play earlier, saving the machine from doing extra unnecessary work.

This reply was modified 3 years, 11 months ago by Sergiy.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 13

Totally agreed on that, Sergiy. It turned out that I had problems with my fnTally function going nuts but it wasn't the fault of the function. 2017 RTM sucked eggs for execution plan generation. Installing the latest CU fixed it for me.

I should know better by now. Never run with Rev 0. In fact, wait for about the 10th CU to be safe.

On that same note and as a bit of a sidebar, all else notwithstanding, the cool about about things like 2008 is that they've stopped breaking it. 😀

--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)