SQL批量数据导入,性能测试
2012-06-15 17:49 xiashengwang 阅读(415) 评论(0) 编辑 收藏 举报1,第一种方法,循环插入
在循环里,用insert语句,注意要加上begin tran 和commit tran 否则慢的吓人。
原因可能是每次发行事务需要开销,不显示指定事务,每次执行insert语句都会发行一次事务。
if OBJECT_ID('t_sample') is null begin create table t_sample ( id varchar(10) not null primary key, txt varchar(10)); end go delete from t_sample; declare @count int; set @count =1; begin tran; while @count<1000000 begin insert into t_sample values (Convert(varchar(10),@count),Convert(varchar(10),Rand()*200000)); set @count = @count +1; end commit tran; select * from t_sample;
耗时:1分半
2,第二种方法,借助临时表,循环次数明显减少,效率提升了
--主表 if OBJECT_ID('t_sample') is null begin create table t_sample ( id varchar(10) not null primary key, txt varchar(10)); end go delete from t_sample; --用临时表来暂存数据,关键点是 --临时表不是一条一条的插入的,每次插入的是 --前一次的记录数 if object_id('t_temp') is not null drop table t_temp; create table t_temp ( id int not null primary key, rnd float ); declare @rc int,@max int; set @rc =1; set @max = 2000000; insert into t_temp values(1,rand()); while @rc * 2 <= @max begin insert into t_temp select (id + @rc) as id ,rand() as rnd from t_temp; set @rc = @rc * 2; end insert into t_sample select id, cast(ceiling(rnd * 10000000000) as numeric(10,0)) from t_temp --(1048576 row(s) affected) 36s
耗时:36秒
备注:两种方式都是插入100万条左右的数据