sql server - How to speed up SQL script which creates a large table of AlphaNumeric codes -


i have following script, generates 175 million(!) unique alphanumeric codes in format 'abc-1234', ranging aaa-0000 zzz-9999.

running script as-is, on dedicated ms sql 2016 box takes 20 hours. what's best way speed up? can tell script, sql skills lacking !

there's identity (int) column, id, , column code (nvarchar(20)) itself. 2 columns make 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 nvarchar(4)),4) + char(45) + @code3                                        set @num = @num +1                                insert 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 , thoughts gratefully appreciated!

i do:

with alphas (       select v.ch       (values ('a'), ('b'), . . .            ) v(ch)      ),      digits (       select v.ch       values ('0'), ('1'), . . .      ) select (a1.ch + a2.ch + a3.ch + d1.ch + d2.ch + d3.ch + d4.ch) code alphas a1 cross join      alphas a2 cross join      alphas a3 cross join      digits d1 cross join      digits d2 cross join      digits d3 cross join      digits d4;

the . . . valid characters want. can generate values using method other values if prefer.


Comments

Post a Comment

Popular posts from this blog

PHP and MySQL WP -

i trying accomplish 2 things here code. writing php directly wp page using insert php plugin. 1) query mysql database , return results 1 column 4 columns. 2) make each result own hyperlink directs user new wp page runs new query on page. i able query show results , turn results dynamic hyperlink, cannot formatting down. right returns result 1 column. here have: [insert_php] $servername = "localhost"; $username = "login"; //edited privacy $password = "password"; //edited privacy $database = "database"; //edited privacy // create connection $conn = new mysqli($servername, $username, $password, $database); // check connection if ($conn->connect_error) { die("connection failed: " . $conn->connect_error); } echo "connected successfully"; //get results database $result = mysqli_query($conn,"select mine mines order mine"); $all_property = array(); //declare array saving property //showing proper...
Read more

android - InAppBilling registering BroadcastReceiver in AndroidManifest -

i'm implementing inapp billing in android app. works well, however, i'm trying decouple broadcast receiver activity manifest. suggestion in android's trivialdrive sample: // important: dynamically register broadcast messages updated purchases. // register receiver here instead of <receiver> in manifest // because call getpurchases() @ startup, therefore can ignore // broadcasts sent while app isn't running. // note: registering listener in activity bad idea, done here // because sample. regardless, receiver must registered after // iabhelper setup, before first call getpurchases(). currently there's class extends broadcastreceiver : public class iabbroadcastreceiver extends broadcastreceiver { /** * intent action receiver should filter for. */ public static final string action = "com.android.vending.billing.purchases_updated"; private final iabbroadcastlistener mlistener; public iabbroadcastreceiver(iabbroadcastlistener listener) { m...
Read more

go - golang pprof for c library code -

i have been trying profile golang application pprof, seems details of c code(linked static library) missed in output result. flame graph from frame graph can see c code time consuming reported within "runtime._externalcode", no details @ all. , sure static library compiled debug enable. so there way make pprof details(like stack trace) of 3rd party c code? or there other golang profiling tool this?
Read more