SQL Server: How to insert million numbers to table fast?
Yesterday I attended at local community evening where one of the most famous Estonian MVPs – Henn Sarv – spoke about SQL Server queries and performance. During this session we saw very cool demos and in this posting I will introduce you my favorite one – how to insert million numbers to table.
The problem is: how to get one million numbers to table with less time? We can solve this problem using different approaches but not all of them are quick. Let’s go now step by step and see how different approaches perform.
NB! The code samples here are not original ones but written by me as I wrote this posting.
Using WHILE
First idea for many guys is using WHILE. It is robust and primitive approach but it works if you don’t think about better solutions. Solution with WHILE is here.
declare @i as int
set @i = 0
while(@i < 1000000)
begin
insert into numbers values(@i)
set @i += 1
end
When we run this code we have to wait. Well… we have to wait couple of minutes before SQL Server gets done. On my heavily loaded development machine it took 6 minutes to run. Well, maybe we can do something.
Using inline table
As a next thing we may think that inline table that is kept in memory will boost up performance. Okay, let’s try out the following code.
declare @t TABLE (number int)
declare @i as int
set @i = 0
while(@i < 1000000)
begin
insert into @t values(@i)
set @i += 1
end
insert into numbers select * from @t
Okay, it is better – it took “only” 01:30 to run. It is better than six minutes but it is not good yet. Maybe we can do something more?
Optimizing WHILE
If we investigate the code in first example we can find one hidden resource eater. All these million inserts are run in separate transaction. Let’s try to run inserts in one transaction.
declare @i as int
set @i = 0
begin transaction
while(@i < 1000000)
begin
insert into numbers values(@i)
set @i += 1
end
commit transaction
Okay, it’s a lot better – 18 seconds only!
Using only set operations
Now let’s write some SQL that doesn’t use any sequential constructs like WHILE or other loops. We will write SQL that uses only set operations and no long running stuff like before.
declare @t table (number int)
insert into @t
select 0
union all
select 1
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
insert into numbers
select
t1.number + t2.number*10 + t3.number*100 +
t4.number*1000 + t5.number*10000 + t6.number*100000
from
@t as t1,
@t as t2,
@t as t3,
@t as t4,
@t as t5,
@t as t6
Bad side of this SQL is that it is not as intuitive for application programmers as previous examples. But when you are working with databases you have to know how some set calculus as well. The result is now seven seconds!
Results
As last thing, let’s see the results as bar chart to illustrate difference between approaches.
I think this example shows very well how usual optimization can give you better results but when you are moving to sets – this is something that SQL Server and other databases understand better – you can get very good results in performance.