I have the following script, that generates 175 Million(!) unique Alphanumeric codes in the format 'ABC-1234', ranging from AAA-0000 to ZZZ-9999.
Running this script as-is, on my dedicated MS SQL 2016 box takes 20 hours. What's the best way to speed this up? As you can tell from the script, my SQL skills are somewhat lacking !
There's an Identity (int) Column, ID, and the column for the CODE (nvarchar(20)) itself. The two columns make up the primary key:
CREATE TABLE [dbo].[ORDERED_CODES](
[ID] [int] NOT NULL,
[Code] [nvarchar](20) NOT NULL,
CONSTRAINT [PK_ORDERED_CODES] PRIMARY KEY CLUSTERED
(
[ID] ASC,
[Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
The script:
DECLARE @Alpha1 INT;
DECLARE @Alpha2 INT;
DECLARE @Alpha3 INT;
DECLARE @Num INT;
-- alpha elements 'ABC'
SET @Alpha1 = 65;
SET @Alpha2 = 65;
SET @Alpha3 = 65;
-- number element '9999'
SET @Num = 0;
-- temporary holders
DECLARE @FINALCODE Nvarchar(50);
DECLARE @CODE1 Nvarchar(50);
DECLARE @CODE2 Nvarchar(50);
DECLARE @CODE3 Nvarchar(50);
WHILE @Alpha1 < 91
BEGIN
SET @CODE1 = CHAR(@Alpha1)
WHILE @Alpha2 < 91
BEGIN
SET @CODE2 = @CODE1 + CHAR(@Alpha2)
SET @Alpha2 = @Alpha2 +1
WHILE @Alpha3 < 91
BEGIN
SET @CODE3 = @CODE2 + CHAR(@Alpha3)
SET @Alpha3 = @Alpha3 +1
WHILE @Num < 10000
BEGIN
SET @FINALCODE = RIGHT('0000'+ CAST(@Num as nvarchar(4)),4) + CHAR(45) + @CODE3
SET @Num = @Num +1
INSERT INTO ORDERED_CODES (CODE) VALUES (@FINALCODE)
END
SET @FINALCODE = null
SET @Num = 0
END
SET @Alpha3 = 65
END
SET @Alpha2 = 65
SET @Alpha1 = @Alpha1 +1
END;
Any and all thoughts are gratefully appreciated!