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.

Comparison between different 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.

Gunnar Peipman

Gunnar Peipman is ASP.NET, Azure and SharePoint fan, Estonian Microsoft user group leader, blogger, conference speaker, teacher, and tech maniac. Since 2008 he is Microsoft MVP specialized on ASP.NET.

    7 thoughts on “How to insert million numbers to table fast?

    • October 21, 2010 at 2:46 am
      Permalink

      I understand that using Set Operations & Multiple Table Select, you were able to insert 1MIL records.

      However, the insertions were just multiplicative factor of a table (which would not be a realistic scenario in most cases)… if you really have 1MIL records to insert, we would have to rely on your Optimised While Loop

      That’s just my understanding. Great post though!!

    • October 21, 2010 at 5:17 am
      Permalink

      Set based approach is always give you better performance than looping or using cursor.

    • October 21, 2010 at 7:39 am
      Permalink

      Great post.

      I don’t think that many real world business scenarios has the need to insert a static sequentially generated set of numbers that often, though.
      In comparison, it would be like a big sports car company would create a advertisment for their latest greatest sports car, only showing it jigged in a testbench without any real road or any driver.

      I would really like to see a sample on how to utilize this technique for inserting 1 million data objects from, let’s say a c# based client over the wire.

    • October 21, 2010 at 12:05 pm
      Permalink

      So let me get this right…you have come to the conclusion that set based methods are faster than looping and/or cursors?

      Wow, innovative stuff, I can now see why you are an MVP…

    • October 21, 2010 at 12:48 pm
      Permalink

      No, Dave, I knew it long-long years before I got MVP title and my databases are living very well. :)

      But… there are still guys who are not familiar with databases and some of these guys are beginners. For other guys it may be interesting to know how big are differences. Let’s say you have legacy database with a lot of bad T-SQL that causes performance problems. Maybe you are the guy who likes to shoot to dark hoping to hit something but I like to be the guy who knows how bad one or another problem is. In the end of the day I think I get better results. :)

    • October 21, 2010 at 2:31 pm
      Permalink

      Nice comparision.
      I agree with @S.P’s comment, which is very true.

    • February 7, 2011 at 12:52 pm
      Permalink

      Hi
      Your solution is nice.
      But I prefer bellow query with 1 second:

      with c as
      (
      select 0 as a1
      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
      )
      select c1.a1 * 1
      + c2.a1 * 10
      + c3.a1 * 100
      + c4.a1 * 1000
      + c5.a1 * 10000
      + c6.a1 * 100000
      + 1 id
      into aa1
      from c as c1,
      c as c2,
      c as c3,
      c as c4,
      c as c5,
      c as c6
      order by id

    Leave a Reply

    Your email address will not be published. Required fields are marked *