The Dynamic Tally or Numbers Table

Question

The Dynamic Tally or Numbers Table

Viewing 15 posts - 61 through 75 (of 159 total)

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

Gotta love this stuff!! Nicely done Lynn and follow-on posters too. Very self-aware of you Lynn to acknowledge that you lost sight of the big picture and forgot that you might just be able to tune your own code a bit more.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

mar10br0 Say Hey Kid Points: 668 More actions · Answer 2

Hi Lynn,

I shaved off another 0-50% (depending on the total number of rows requested, better on lower quantities!):

CREATE FUNCTION [ufn_Tally3]

(

@Startbigint = 0,

@Endbigint = 4294967295,

@Incbigint = 1

)

RETURNS TABLE

AS

RETURN(

WITH

L16 (N) AS (

SELECT 1UNION ALL

SELECT 2UNION ALL

SELECT 3UNION ALL

SELECT 4UNION ALL

SELECT 5UNION ALL

SELECT 6UNION ALL

SELECT 7UNION ALL

SELECT 8UNION ALL

SELECT 9UNION ALL

SELECT10UNION ALL

SELECT11UNION ALL

SELECT12UNION ALL

SELECT13UNION ALL

SELECT14UNION ALL

SELECT15UNION ALL

SELECT16),

L65536 (N) AS (

SELECT1

FROML16 L16a

CROSS JOINL16 L16b

CROSS JOINL16 L16c

CROSS JOINL16 L16d),

L (N) AS (

SELECTTOP (1 + ABS(@End - @Start) / ABS(@Inc)) 1

FROML65536 L65536a

CROSS JOINL65536 L65536b

CROSS JOINL65536 L65536c

CROSS JOINL65536 L65536d)

SELECT(((ROW_NUMBER() OVER (ORDER BY N)) - 1) * @Inc) + @Start AS N

FROML

);

Major modifications that increased the performance where:

- Using the constant 1 instead of column N for output by the CTEs (marginal but measurable)

- Folding the ROW_NUMBER function into the final SELECT to reduce a step in the sequence (the big improvement).

I also incorporated Jeff's optimization of the TOP-expression.

BTW: the 4 cross-joins in the final "L" CTE will make it possible for the tally to achieve the full range of a bigint as output (I don't dare try executing though) :Whistling:

Performance comparisons between version from the article and my version executed on my workstation (quad-core Pentium 2.6GHz, SATA-II harddisk):

Assigning to a BIGINT variable (all times in milliseconds):

Number of RowsCPU time ufn_Tally2Elapsed time ufn_Tally2CPU time ufn_Tally3%Elapsed time ufn_Tally3%

1000-0-

10000-0-

100000-0-

1000000-0-

10000040-325%

1000003133310%316%

100000032932729710%2989%

100000003265326429699%29619%

Inserting into temporary table (all times in milliseconds):

Number of RowsCPU time ufn_Tally2Elapsed time ufn_Tally2CPU time ufn_Tally3%Elapsed time ufn_Tally3%

1010-0-

10000-0-

1001610-0-

1000050-340%

100003140310%2733%

1000002822792666%2655%

1000000256326112594-1%2672-2%

100000002579726507257970%262691%

Assigning to a BIGINT variable (all times in milliseconds):

Number of RowsCPU time ufn_Tally2Elapsed time ufn_Tally2CPU time ufn_Tally3%Elapsed time ufn_Tally3%

6000-0-

60000-0-

600000-0-

6000020-20%

6000032201553%1810%

600000187199188-1%17811%

600000019691959176610%17929%

6000000019609196381754611%1757710%

Inserting into temporary table (all times in milliseconds):

Number of RowsCPU time ufn_Tally2Elapsed time ufn_Tally2CPU time ufn_Tally3%Elapsed time ufn_Tally3%

60000-0-

600020-20%

60001616160%17-6%

600001561631560%1583%

600000153115781562-2%1584-0%

60000001559415841154691%157611%

60000000155985159653159094-2%161357-1%

Based on GSquared's post I played with the following nonsense-parameter protection, but after seeing a significant performance-penalty I decided against them. Anyway it is my firm belief that such protections should be validated once where user-input comes into the system rather than taking the performance-penalty deep inside the routines (potentially executed millions of times).

TOP (1 + ABS(ISNULL(@End, 0) - ISNULL(@Start, 0)) / ISNULL(NULLIF(ABS(@Inc), 0), 1))

(((ROW_NUMBER() OVER (ORDER BY N)) - 1) * SIGN(ISNULL(@End, 0) - ISNULL(@Start, 0)) * ISNULL(NULLIF(ABS(@Inc), 0), 1)) + ISNULL(@Start, 0) AS N

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

Pretty impressive Marco!! I thought though that Jeff had said 10 was the sweet spot for the number of values. Looks like you are using 16? Course it could be just that my brain is mush after a very intense day of coding. 🙂

I agree about the cost of input protection/validation. I plan to remove that from any uses I do of the code presented here.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 4

Heh... it was during my testing. I'll check this on the same machine tomorrow to keep the apples'n'oranges thing from happening.

--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: 1004290 More actions · Answer 5

Lots'o good code coming out of this discussion... Haven't tested that last one for speed on the same machine that I've been testing on, but it'll gen more numbers than INT and a hell of a lot more numbers than I'll ever wait for. 😛

--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: 1004290 More actions · Answer 6

marco-870908 (9/22/2009)
Anyway it is my firm belief that such protections should be validated once where user-input comes into the system

Roger that! One of my favorite sayings is that "If you make something idiot proof, only idiots will use 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)

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 7

Lynn, great article..

You make reference to Jeff's article, I'm curious how this method compares to Jeff's method..

CEWII

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Elliott W (9/22/2009)
Lynn, great article..
You make reference to Jeff's article, I'm curious how this method compares to Jeff's method..
CEWII

I'm not sure what you are talking about. I just reread my article, and the only article I reference is the one written by Frank Solomon. I do mention that I was introduced to the concept of the Tally by Jeff Moden, but I didn't reference how. Now he does have an article that discusses the uses of tally tables, and in it he shows how to create a static tally table. His article is actually referenced in my signature block below.

The dynamic tally table can be used just like a static tally table, not a lot of difference. I'd pay attention to some of what Jeff mentioned earlier though if you are using a large numbe of values.

MrAkki SSCrazy Points: 2574 More actions · Answer 9

Hi,

I just wanted to say, that this is a great discussion.... 🙂

I learned a lot... thanks....:Wow:

Johan Bijnens SSC Guru Points: 135361 More actions · Answer 10

Lynn Pettis (9/22/2009)
... I was so busy looking at the routine itself and comparing it to that other one, I didn't even consider tuning mine more.

Just hopping in 😉

The ever lasting question is : "How much is enough ?"

BTW: Nice article Lynn :smooooth:

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Matt Whitfield SSCrazy Eights Points: 8107 More actions · Answer 11

Jeff Moden (9/22/2009)
Heh... I just get lucky.

Jeff, no way.

Lucky is when you manage to do it once or twice, someone like me.

You are on a different level of SQL-ness to the rest of us!

😀

Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone

Matt Whitfield SSCrazy Eights Points: 8107 More actions · Answer 12

I've just caught up with this discussion, because the site isn't emailing me about it for some reason - which is a shame - because it's a *really* good one. I have enjoyed immensely - thank you all.

Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone

Grant Fritchey SSC Guru Points: 398872 More actions · Answer 13

Sorry I'm late to the party Lynn. This is a fantastic article. It's practically a how-to on writing a good performance test article. Well done.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 14

Darn good article Lynn... And the discussion was pretty good. Lots of new ideas to optimize and already optimized solution came out... Great Job. Two thumbs up to you.. 🙂

-Roy

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 15

Fine article, Lynn, and another rocking discussion, everyone.

I'm cussing now because work is taking away from my reading time in here.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills