Click here to Skip to main content
15,867,756 members
Articles / Programming Languages / SQL

SQL. Fast GUID List Generator

Rate me:
Please Sign up or sign in to vote.
2.20/5 (4 votes)
30 Aug 2017CPOL 5.5K   2   1
If you want your database to be fast with a huge amount of data, you should test it with large test database.

If you want your database to be fast with huge amount of data, you should test it with large test database. To create test database, you may need many guids for row ids. If you want to create a list of guids and put them into a SQL table, you may write the following:

SQL
create table ids(id uniqueidentifier);

declare @guidCount int = 10000;
declare @n int = 0;
while @n < @guidCount
begin
   insert ids values(NEWID());
   set @n = @n + 1;
end

This code creates 10 000 guids. If you need more guids, it can be very slow. You then modify the code as follows:

SQL
create table ids(id uniqueidentifier);

declare @ids table(id uniqueidentifier default NEWID());
declare @guidCount int = 100000;
declare @n int = 0;
while @n < @guidCount
begin
   insert @ids default values;
   set @n = @n + 1;
end

insert ids with(tablockx)
select * from @ids;

This code creates 100 000 guids with the same period of time. If you need even more guids, the second approach also can be slow and memory consumptive. But what if we won’t generate guids in memory? Instead, we just generate counter and then use it to generate guids:

SQL
create table ids(id uniqueidentifier);

declare @ids table(id int identity)
declare @guidCount int = 1000000;
declare @n int = 0;
while @n < SQRT(@guidCount)
begin
   insert @ids default values;
   set @n = @n + 1;
end

insert ids with(tablockx)
select NEWID() from @ids i1, @ids i2;

This code creates 1 000 000 guids also with the same period of time as the first and second ones! I use this pattern to generate huge test databases. If you need volume testing – this pattern is for you.

This article was originally posted at https://anton-burtsev.livejournal.com/2834.html

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Web Developer
Russian Federation Russian Federation
I have started as a C++ developer in far 2000. I'd been developing banking and treasury software for 3 years using C++. In 2003 I switched to .NET at DELL. After that I worked as a project manager on different projects (internal audit, treasury automation, publishing house automation, etc.). Since 2009 I own a small software company specialized in SaaS services and develop a DotNetNuke modules.

Comments and Discussions

 
AnswerNEWSEQUENTIALID Pin
sx20087-Sep-17 9:03
sx20087-Sep-17 9:03 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.