Generate N sequential numbers (fast)

Question

Post reply

Generate N sequential numbers (fast)

Brymen

SSC-Addicted

Points: 495
More actions
May 31, 2013 at 7:05 am

#273348

Comments posted to this topic are about the item Generate N sequential numbers (fast)

Viewing 8 posts - 1 through 7 (of 7 total)

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

jw.lankhaar SSC Rookie Points: 37 More actions · Answer 1

For a limited number of rows (< 100) using a recursive common table expression is an alternative:

DECLARE @lowerbound int

DECLARE @increment int

DECLARE @upperbound int

SET @lowerbound = -5

SET @upperbound = 90

SET @increment = 3

;

WITH seq AS (

SELECT @lowerbound AS n

UNION ALL

SELECT seq.n + @increment AS n

FROM seq INNER JOIN (SELECT NULL AS a) a ON

n < @upperbound

)

SELECT n FROM seq

Note that due to recursion, the number of returned rows may not exceed 99.

Jan-Willem Lankhaar

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

jw.lankhaar (6/13/2013)
For a limited number of rows (< 100) using a recursive common table expression is an alternative:
DECLARE @lowerbound int
DECLARE @increment int
DECLARE @upperbound int
SET @lowerbound = -5
SET @upperbound = 90
SET @increment = 3
;
WITH seq AS (
SELECT @lowerbound AS n
UNION ALL
SELECT seq.n + @increment AS n
FROM seq INNER JOIN (SELECT NULL AS a) a ON
n < @upperbound
)
SELECT n FROM seq
Note that due to recursion, the number of returned rows may not exceed 99.
Jan-Willem Lankhaar

DECLARE

@lowerbound INT,

@increment INT,

@upperbound INT

SELECT

@lowerbound = -5,

@upperbound = 2800000,

@increment = 3;

SELECT x.Start + y.Inc

FROM [dbo].[InlineTally] (1000000) t

CROSS APPLY (SELECT Start = (t.n+@lowerbound-1)) x

CROSS APPLY (SELECT Inc = (t.n-1)*(2)) y

WHERE x.Start + y.Inc <= @upperbound;

-- (933,336 row(s) affected) / 00:00:05

WITH seq AS (

SELECT n = @lowerbound

UNION ALL

SELECT seq.n + @increment

FROM seq

WHERE n < @upperbound

)

SELECT n FROM seq OPTION(MAXRECURSION 0);

-- (933,336 row(s) affected) / 00:00:12

^{“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

Steven Willis SSCrazy Eights Points: 9893 More actions · Answer 3

Did you run any performance tests with this? It LOOKS simpler to the eye and at first I thought it looked really good--and it still may be a better alternative than the Itzik Ben-Gan algorithm. I ran some quickie tests with both by plugging your new code into a tally table itvf I use frequently which uses the Itzik method. Then I ran a very simple function that stepped through random characters in strings of varying length. Just something to make a comparison.

I plan on doing some more testing but it will probably be next week if I'm to do it right with some good functions. If you have any ideas for some functions/scripts that might give your model a good test, please pass them along. I've been working on creating a testing methodology that will (I hope) be easy enough to use so that any promising ideas like yours can be put to the test.

Now things might change with different applications, but the bad news at the moment is that old Itzik still seems to have a slight advantage. I've attached a screenshot of the intial test runs.

Steven Willis SSCrazy Eights Points: 9893 More actions · Answer 4

Brian

It looks like you have a winner! I ran a test of DelimitedSplit8K using your method against 3 other methods including the Itzik Ben-Gan method. Your version consistently outperformed the Itzik method--not by much, but clearly a slight edge based on the numbers on my machine. The test results might be different on different servers of course.

The test results are attached.

Good work!

Steven Willis

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

jw.lankhaar (6/13/2013)
For a limited number of rows (< 100) using a recursive common table expression is an alternative:

Please read the following article for why you should probably never use an rCTE for such a thing.

http://www.sqlservercentral.com/articles/T-SQL/74118/

Note that due to recursion, the number of returned rows may not exceed 99.

Careful now. It is true that the default for rCTEs is a max of 100 but that's easy to override using OPTION(MAXRECURSION x) where "x" can be any positive integer from 0 to 32,767 and "0" means "unlimited".

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

Steven Willis (6/22/2013)
Brian
It looks like you have a winner! I ran a test of DelimitedSplit8K using your method against 3 other methods including the Itzik Ben-Gan method. Your version consistently outperformed the Itzik method--not by much, but clearly a slight edge based on the numbers on my machine. The test results might be different on different servers of course.
The test results are attached.
Good work!
Steven Willis

Which version of the DelimitedSplit8K?

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

Steven Willis SSCrazy Eights Points: 9893 More actions · Answer 7

Jeff Moden (8/15/2013)
Steven Willis (6/22/2013)
Brian
It looks like you have a winner! I ran a test of DelimitedSplit8K using your method against 3 other methods including the Itzik Ben-Gan method. Your version consistently outperformed the Itzik method--not by much, but clearly a slight edge based on the numbers on my machine. The test results might be different on different servers of course.
The test results are attached.
Good work!
Steven Willis

Which version of the DelimitedSplit8K?

Good guestion...there seems to have been some revisions and I may have used an older one? I'll have to do some research and let you know.