Post reply

GetNumsAB

  • March 24, 2016 at 6:33 pm

    #326118

    Comments posted to this topic are about the item GetNumsAB

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • April 19, 2016 at 4:13 am

    #1872987

    Great thanks, very useful function!

  • April 19, 2016 at 6:44 am

    #1873036

    Great script, thanks.

  • September 13, 2021 at 3:24 am

    #3928970

    Nicely done, Alan.  Your code runs faster than GetNums and fnTally because it only uses 2 CROSS JOINs instead of 31 and 3 respectively.  It also doesn't have the habit of going parallel, which would slow things down a fair bit on larger values like 1 billion and use and insane about of CPU like it currently does in GetNums and fnTally.  Of course, most people aren't going to need a list of numbers greater than your implicit hard stop of 100,544,625.  Heh... I never thought folks would need more than 11,000 values for physical Tally Tables! 😀

    The only thing you might want to add is to do a check for the max number of rows to be returned and if it's larger than your inherent max of 100,544,625 then return nothing in a manner similar to what you've done in your WHERE clause.  Maybe something like the following (uses the TOP formula in the WHERE clause).

      WHERE @high >= @low and @gap > 0 
        AND ABS((@high-@low)/ISNULL(NULLIF(@gap,0),1)+1) <= 100,544,625

    As a bit of a sidebar, I figured out a way to prevent parallelism in iTVFs without having to use an external MAXDOP 1 and I'm going to write that up as a possible fix for all us crazies that do us such functions to sometimes generate a billion or more numbers in a sequence (I use it as a "Row Source" to generate some insane test tables and sometimes the "N" values themselves).  It cuts WAY BACK on the CPU usage.  "Going parallel" on these types of things hurts a whole lot more than it helps.  Glad to see your good code doesn't have that problem.

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

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

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