Tally generator

  • Comments posted to this topic are about the item Tally generator

    _____________
    Code for TallyGenerator

  • 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)
    Intro to Tally Tables and Functions

  • 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

  • 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)
    Intro to Tally Tables and Functions

  • 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

  • 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)
    Intro to Tally Tables and Functions

  • 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

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

  • 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

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

  • 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)
    Intro to Tally Tables and Functions

  • 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)
    Intro to Tally Tables and Functions

  • 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 9 months, 4 weeks ago by  Sergiy.

    _____________
    Code for TallyGenerator

  • 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)
    Intro to Tally Tables and Functions

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

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