How to generate larger sets of lottery numbers efficiently

Question

I am a beginner with SQL and I was looking for more experiences with SQL hence I decided to design a procedure to generate X amount of random lotto picks. The lottery here in my area allows you to pick 5 numbers from 1-47 and 1 "mega" number from 1-27. The trick is the "mega" number could repeat with the 5 numbers previously, i.e. 1, 2, 3, 4, 5, mega 1.

I created the following procedure to generate 10 million lottery picks, and it took 12 hours and 57 minutes for the process to finish. While my friends tested the same thing with java and it took seconds. I was wondering if there's any improvements I can make to the code or if there's any mistakes that I've made? I'm new at this hence I am trying to learn better approaches etc, all comments welcome.

USE lotto
DECLARE 
@counter INT,
@counter1 INT,
@pm SMALLINT,
@i1 SMALLINT,
@i2 SMALLINT,
@i3 SMALLINT,
@i4 SMALLINT,
@i5 SMALLINT,
@sort int

SET @counter1=0

TRUNCATE TABLE picks 

WHILE @counter1<10000000
BEGIN
    TRUNCATE TABLE sort
    SET @counter = 1
        WHILE @counter < 6
        BEGIN
            INSERT INTO sort (pick)
            SELECT CAST(((47+ 1) - 0)   * RAND() + 1 AS TINYINT)
            IF (SELECT count(distinct pick) FROM sort)<@counter 
                BEGIN
                TRUNCATE TABLE sort
                SET @counter=1
                END
            ELSE IF (SELECT COUNT(DISTINCT pick) FROM sort)=@counter 
                BEGIN
                SET @counter = @counter + 1
            END
        END



    SET @sort = 0
        WHILE @sort<5
        BEGIN
            UPDATE sort
            SET sort=@sort
            WHERE pick = (SELECT min(pick) FROM sort WHERE sort is null)
            SET @sort=@sort + 1
        END

    SET @i1 = (SELECT pick FROM sort WHERE sort = 0)
    SET @i2 = (SELECT pick FROM sort WHERE sort = 1)
    SET @i3 = (SELECT pick FROM sort WHERE sort = 2)
    SET @i4 = (SELECT pick FROM sort WHERE sort = 3)
    SET @i5 = (SELECT pick FROM sort WHERE sort = 4)
    SET @pm = (CAST(((27+ 1) - 0)   * RAND() + 1 AS TINYINT))

    INSERT INTO picks(
        First,
        Second,
        Third,
        Fourth,
        Fifth,
        Mega,
        Sequence
        )
    Values(
        @i1,
        @i2,
        @i3,
        @i4,
        @i5,
        @pm,
        @counter1
        )
    SET @counter1 = @counter1+1
END

Answer 1

I generated 10000 rows in 0 sec. I did it i another way. Hope this will help you

;WITH Nbrs ( n ) AS (
        SELECT 1 UNION ALL
        SELECT 1 + n FROM Nbrs WHERE n < 10000 )
SELECT 
    (ABS(CHECKSUM(NewId())) % 47 + 1) AS First,
    (ABS(CHECKSUM(NewId())) % 47 + 1) AS Second,
    (ABS(CHECKSUM(NewId())) % 47 + 1) AS Third,
    (ABS(CHECKSUM(NewId())) % 47 + 1) AS Fourth,
    (ABS(CHECKSUM(NewId())) % 47 + 1) AS Fifth,
    (ABS(CHECKSUM(NewId())) % 27 + 1) AS Mega,
    Nbrs.n AS Sequence 
FROM 
    Nbrs
OPTION ( MAXRECURSION 0 )

10000 rows 0 sec
100000 rows 1 sec
1000000 rows 13 sec
10000000 rows 02 min 21 sec

Or with cross joins

WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
        E02(N) AS (SELECT 1 FROM E00 a, E00 b),
        E04(N) AS (SELECT 1 FROM E02 a, E02 b),
        E08(N) AS (SELECT 1 FROM E04 a, E04 b),
        E16(N) AS (SELECT 1 FROM E08 a, E08 b),
        E32(N) AS (SELECT 1 FROM E16 a, E16 b),
   Nbrs(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)
 SELECT 
    (ABS(CHECKSUM(NewId())) % 47 + 1) AS First,
    (ABS(CHECKSUM(NewId())) % 47 + 1) AS Second,
    (ABS(CHECKSUM(NewId())) % 47 + 1) AS Third,
    (ABS(CHECKSUM(NewId())) % 47 + 1) AS Fourth,
    (ABS(CHECKSUM(NewId())) % 47 + 1) AS Fifth,
    (ABS(CHECKSUM(NewId())) % 27 + 1) AS Mega,
    Nbrs.n AS Sequence 
   FROM Nbrs
  WHERE N <= 10000000;

10000 rows 0 sec
100000 rows 1 sec
1000000 rows 14 sec
10000000 rows 03 min 29 sec

I should also mention that the reason I am using

(ABS(CHECKSUM(NewId())) % 47 + 1)

is that it returns a random number per row. The solution with

CAST(((47+ 1) - 0)   * RAND() + 1 AS TINYINT)

return the same random number for each row if you select them in one go. To test this run this example:

    ;WITH Nbrs ( n ) AS (
        SELECT 1 UNION ALL
        SELECT 1 + n FROM Nbrs WHERE n < 5 )
SELECT
    CAST(((47+ 1) - 0)   * RAND() + 1 AS TINYINT) AS Random,
    (ABS(CHECKSUM(NewId())) % 47 + 1) AS RadomCheckSum,
    Nbrs.n AS Sequence
FROM Nbrs

Ok. So I did see your comment and I have a solution for that as well. If you really want to order the numbers. The complexity of the algorithm elevates and that also means that the time of the algorithm increases. But i still think it is doable. But not in the same neat way.

--Yeah declaring a temp table for just the random order number
DECLARE @tbl TABLE(value int)

--The same function but with the number of the random numbers
;WITH Nbrs ( n ) AS (
        SELECT 1 UNION ALL
        SELECT 1 + n FROM Nbrs WHERE n < 5 )
INSERT INTO @tbl
(
    value
)
SELECT
    Nbrs.n AS Sequence
FROM Nbrs 

;WITH Nbrs ( n ) AS (
        SELECT CAST(1 as BIGINT) UNION ALL
        SELECT 1 + n FROM Nbrs WHERE n < 100000 )
SELECT
    tblOrderRandomNumbers.[1] AS First,
    tblOrderRandomNumbers.[2] AS Second,
    tblOrderRandomNumbers.[3] AS Third,
    tblOrderRandomNumbers.[4] AS Fourth,
    tblOrderRandomNumbers.[5] AS Fifth,
    (ABS(CHECKSUM(NewId())) % 27 + 1) AS Mega,
    Nbrs.n AS Sequence
FROM
    Nbrs
    --This cross join. Joins with the declared table
    CROSS JOIN
        (
            SELECT
                [1], [2], [3], [4], [5]
            FROM
            (
            SELECT
                Random,
                ROW_NUMBER() OVER(ORDER BY tblRandom.Random ASC) AS RowNumber
            FROM
                (
                SELECT
                    (ABS(CHECKSUM(NewId())) % 47 + 1) AS Random
                FROM
                    @tbl AS tblNumbers
                ) AS tblRandom

            )AS tblSortedRadom
            --A pivot makes the rows to columns. Using the row index over order of the random number
            PIVOT
            (
                AVG(Random)
            FOR RowNumber IN ([1], [2], [3], [4],[5])
            ) as pivottable
        ) AS tblOrderRandomNumbers
OPTION ( MAXRECURSION 0 )

But still i manage to do it in a little time
10000 Rows : 0 sec
100000 Rows : 4 sec
1000000 Rows : 43 sec
10000000 Rows : 7 min 9 sec

I Hope this help

Answer 2

I wrote this script just out of curiousity. It should do better than your script, but I cant tell for sure.

Beware that I use a declared table, and if you use a real table performance should be better when generating larger amounts of rows.

I generated 10000 rows on about 13 seconds, that counts to about 3.5 hours to generate 10 000 000 rows. Still far worse than the Java-case you described.

set nocount on
go

declare @i int = 1

declare  @t table(nr1 int, nr2 int, nr3 int, nr4 int, nr5 int, mega int, seq int)

while @i <= 10000
begin

;with numbers(nr)
as
(
select 1
union all
select nr+1
from numbers
where nr < 47
)
,mega(nr)
as
(
select 1
union all
select nr+1
from mega
where nr < 27
)
,selectednumbers(nr)
as
(
select top 5 nr
from numbers
order by newid()
)
,selectedmega(mega)
as
(
select top 1 nr
from mega
order by newid()
)
,tmp
as
(
select  *
        ,row_number() over(order by nr) as rownr
from selectednumbers
)
insert into @t
select  max(nr1) as nr1
        ,max(nr2) as nr2
        ,max(nr3) as nr3
        ,max(nr4) as nr4
        ,max(nr5) as nr5
        ,(select mega from selectedmega) as mega
        ,@i as seq
from (
        select  case when rownr = 1 then nr else 0 end as nr1
                ,case when rownr = 2 then nr else 0 end as nr2
                ,case when rownr = 3 then nr else 0 end as nr3
                ,case when rownr = 4 then nr else 0 end as nr4
                ,case when rownr = 5 then nr else 0 end as nr5
        from    tmp
    ) x

set @i = @i + 1
end

select * from @t