SQL Server : large batch of random codes inserted into table

Question

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

*"The whole routine takes about 30 seconds to complete."* I would honestly be surprised at this. SQL is a set based language, and therefore it excels at set based solutions and therefore performs awfully at iterative tasks (such as this). Though I freely admit I'm running my Sandbox instance in a Container, which doesn't have access to the full resources of the host, it still has access to enough, and 2 minutes it's only created ~45,000 rows. Less than 20% of the target. — Thom A, Jan 24 '22 at 14:08
How often do you need to do this? If you're worried about efficiency then pre-filling the `tblCodes` with all permissible values (32^8) and then _selecting_ from that would be better i.e. pay the generation cost only the once. — gvee, Jan 24 '22 at 14:58
Hi gvee - the codes are generated adhoc, sometimes a few hundred, sometimes many thousands - see them as voucher codes. So what you're suggesting is to generate ALL conceivable codes and then select - copy/move those codes to be 'live' ?? — BigIWT, Jan 24 '22 at 15:01
Joining to a tally table is likely to be far faster than your current code — Charlieface, Jan 24 '22 at 15:40

score 0 · Answer 1 · answered Jan 25 '22 at 00:20

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

score -1 · Answer 2 · answered Jan 24 '22 at 14:14

-1

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.

answered Jan 24 '22 at 14:14

Sandritsch91

9
3

Thanks, I was coming to a similar conclusion. Batches of no greater than 150k work every time - hence pointing to a timeout issue. Also agree that we shouldn't manipulate the server defaults to overcome a single 'issue' - but looking how to make the procedure more efficient. – BigIWT Jan 24 '22 at 14:27
1

It is very inefficient to perform a select every time you generate a new value to check for double entries. Maybe create a temporary table at the beginning of the function, insert all existing values and then add the new ones. This way you can check the temporary table, if the value already exists. Temporary tables are way faster, because they are stored in-memory – Sandritsch91 Jan 24 '22 at 14:41
SQL Server does *not* have a default timeout, although most client drivers do. A timeout on the client will result in an `Attention` TDS signal being sent, which cancels the query – Charlieface Jan 24 '22 at 15:39
I've restructured the procedure to use #temp table, generate and insert the codes - check for uniqueness (delete and re-run as necessary) - then insert into my 'real' table. In doing so, this has reduced processing time from 30 seconds down to 7 seconds ! Thanks @Sandritsch91 for your suggestion – BigIWT Jan 24 '22 at 16:08

SQL Server : large batch of random codes inserted into table

2 Answers2