GetNumsAB
-
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
-
Great thanks, very useful function!
-
Great script, thanks.
-
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,625As 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