Generate N sequential numbers (fast)

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

  • 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

  • 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

  • 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.

  • 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

     

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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.

     

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

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