SQL Server : large batch of random codes inserted into table-CodePudding

Edited: Suspect procedure timeout to be the culprit, so seeking more efficient way to proceed. thanks

I'm struggling with a routine that as far as I can see should work but isn't.

The stored procedure should generate x-number of random strings (8 chars) and then insert them into a table. A check for uniqueness is carried out also. Batch size for code generation is 250k codes.

My issue is that not all requested codes are generated all of the time, sometimes the generated batch size is smaller and I can't figure out why? Sometime it runs the full 250k codes are generated, sometimes it only generate 240k ?? The whole routine takes about 30 seconds to complete.

Thanks muchly in advance

DECLARE @i int = 1, @chars char(32), @result char(8), @cntR int, @rfQty int = 0

SET @chars = N'23456789ABCDEFGHJKLMNPQRSTUVWXYZ'

WHILE (@i <= 250000)
BEGIN       
    /* generate code */
    SET @cntR = 0;      
    SET @result = SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT)   1, 1)
                    SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT)   1, 1)
                    SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT)   1, 1)
                    SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT)   1, 1)
                    SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT)   1, 1)
                    SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT)   1, 1)
                    SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT)   1, 1)
                    SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT)   1, 1);

    /* is unique ? */
    SELECT @cntR = COUNT(rfcode)
    FROM tblCodes
    WHERE rfCode = @result;
    
    /* insert result if unique */
    IF @cntR = 0 
    BEGIN
        INSERT INTO tblCodes (rfCode)       
        VALUES (@result);

        SET @i = @i   1;
    END
END

CodePudding user response：

Building on the comments, you can use a TALLY table (I've generated mine using some CTEs) and use ABS(CHECKSUM(NewId())) % 32 to generate a random number between 0 & 32 as shown here: How do i generate a random number for each row

DECLARE @chars char(32)

SET @chars = N'23456789ABCDEFGHJKLMNPQRSTUVWXYZ'

;WITH CTE AS
(
    SELECT *
    FROM (VALUES (1),(2), (3), (4), (5), 
                (6), (7), (8), (9), (10),
                (11), (12), (13), (14), (15),
                (16), (17), (18), (19), (20),
                (21),(22),(23)) AS  Nums(N)
),
TALLY AS
(
    SELECT TOP 250000 C1.N
    FROM CTE C1
    CROSS JOIN CTE C2
    CROSS JOIN CTE C3
    CROSS JOIN CTE C4
)
SELECT SUBSTRING(@chars, ABS(CHECKSUM(NewId())) % 32  1, 1)
       SUBSTRING(@chars, ABS(CHECKSUM(NewId())) % 32   1, 1)
       SUBSTRING(@chars, ABS(CHECKSUM(NewId())) % 32   1, 1)
       SUBSTRING(@chars, ABS(CHECKSUM(NewId())) % 32   1, 1)
       SUBSTRING(@chars, ABS(CHECKSUM(NewId())) % 32   1, 1)
       SUBSTRING(@chars, ABS(CHECKSUM(NewId())) % 32   1, 1)
       SUBSTRING(@chars, ABS(CHECKSUM(NewId())) % 32   1, 1)
       SUBSTRING(@chars, ABS(CHECKSUM(NewId())) % 32   1, 1)
       SUBSTRING(@chars, ABS(CHECKSUM(NewId())) % 32   1, 1)
FROM Tally

This takes about 3 seconds to run on my laptop

CodePudding user response：

Sql-server has a command timeout which defaults to 30 seconds. So your stored procedure will abort after 30 seconds:

You can change the timeout, but this could be dangerous. It would be smarter to modify your function to iterate fewer times and call it multiple times.