SQL插入100万个自然数

SQL插入100万个自然数到表(id为主键)

方案一

with Result as(

select aaa.col*100000+ aa.col*10000+ a.col*1000+b.col*100+c.col*10+d.col+1 as col
from 
(select 0 as col 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)aaa
cross join 
(select 0 as col 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)aa
cross join 
(select 0 as col 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)a
cross join 
(select 0 as col 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)b
cross join 
(select 0 as col 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)c
cross join 
(select 0 as col 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)d

)
insert into normaltable([id]) select * from Result order by col

准备工作

设置数据库大小为50MB，日志文件初始大小为600MB，增长为500MB，使得插入过程中硬盘文件空间全部预备好。每次测试均收缩日志文件到600MB。

	有索引	无索引	内存表有索引 （经测在非性能的某些方面是个坑， 暂时不要使用内存表）
100万		2秒	3秒（delete 1秒）
10万

（100万个）MSSQL2014 普通表83秒（12048/s），普通表无索引首次2秒13秒（76923/s），内存表4秒（25万个/s）
（10万个）MSSQL2014 普通表秒（/s），普通表无索引秒（/s ~/s），内存表1秒（万个/s）
作为参考一般应用程序单线程发送请求“select 1”能达到每秒8000~12000的水平
（上面产生数据的临时结果集代码是从csdn里翻出来的不是原创）

---

方案二（仅插入10万个）

begin tran

DECLARE @id int
SET @id = 1
while @id <= 100000 begin
insert into dbo.memorytable ([id]) values(@id)
SET @id = @id + 1
end
commit tran;

（10万个）普通表（首次运行在空表上43秒）24秒（2326/s）普通表不含主键、索引8秒（12500/s） 内存表4秒（25000/s）
（20万个）普通表秒（/s）普通表不含主键、索引秒（/s） 内存表11秒（/s）
（40万个）普通表秒（/s）普通表不含主键、索引秒（/s） 内存表21秒（/s）

结论

空表插入时，无索引性能高很多
批量从数据源插入比循环插入快
（SQL脚本中循环比应用程序中循环快）