苏小弟学SQL Server导入百万数据仅需7秒

时间：2010-10-22 来源：Steve Ember

declare @i as int
set @i = 0

while(@i < 1000000)
begin
    insert into numbers values(@i)
    set @i += 1
end

该方法消耗大约6分钟，貌似我吃顿饭也只要这么久

方法二：内联表方法

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

据说保存在内存里能提高性能。果然不错运行仅需1分30秒。

方法三：优化wihle方法

declare @i as int
set @i = 0

begin transaction
while(@i < 1000000)
begin
    insert into numbers values(@i)
    set @i += 1
end
commit transaction

由于插入是在分离的事物里，我们现在让它在一个事物下插入，仅需18秒

方法四：Set操作

代码 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

此方法操作仅需7秒。