SQL Server插入或者批量导入大量数据的性能表现

我经常看到客户测试sql server 的插入(insert)或者批量导入大量数据的性能表现。其中有测试大量insert的tsql脚本如下： use myDB go create table t1 ( id int primary key not null identity ( 1 , 1 ), name varchar ( 200 ), memo char ( 500 )); go trun

我经常看到客户测试sql server 的插入(insert)或者批量导入大量数据的性能表现。其中有测试大量insert的tsql脚本如下：

use myDB

go

网趣网上购物系统旗舰版

网趣网上购物系统支持PC电脑版+手机版+APP，数据一站式更新，支持微信支付与支付宝支付接口，是专业的网上商城系统，网趣商城系统支持淘宝数据包导入，实现与淘宝同步更新!支持上传图片水印设置、图片批量上传功能，同时支持订单二次编辑以及多级分类隐藏等实用功能，新版增加商品大图浏览与列表显示功能，使分类浏览更方便，支持最新的支付宝即时到帐接口。

下载

createtable t1(id intprimarykeynotnullidentity (1,1),name varchar(200),memo char(500));

go

truncatetable t1

go

declare @i int

set @i=0

declare @beginTime datetime,@endTime datetime

set @beginTime=GETDATE()

while(@i200000)

begin

 insertinto t1(name,memo)values('test'+str(RAND()*100000000),'memo'+str(RAND()*100000000))

 set @i=@i+1

end

set @endTime=GETDATE()

select @endTime-@beginTime

上面这个脚本测试插入200000行数据需要的时间。这个脚本有问题么?

语法没有问题，但是写法却不够优化。如果你使用performance  monitor

来观察，就会发现在数据插入期间log flushes/sec的次数非常高。在我的机器上达到5000。Log flushes发生一次，意味着SQL server 需要写入事务日志（transaction log）一次。每秒5000次的日志写动作导致磁盘大量的写操作。正是这个磁盘写操作影响了上面的batch的性能。我上面脚本在我的机器上使用了大概40秒左右的时间。

如何改进这个脚本的性能呢？如何减少log flushes/sec从而减少磁盘的写操作？ 答案是使用Batch Size如下面脚本所示。

truncatetable t1

go

declare @i int

set @i=0

declare @beginTime datetime,@endTime datetime

set @beginTime=GETDATE()

declare @batchSize int

set @batchSize=1000

while(@i00000)

begin

 if (@i%@batchSize=0)

    begin

      if (@@TRANCOUNT>0)COMMITTRAN

      BEGINTRAN

    end

 insertinto t1(name,memo)values('test'+str(RAND()*100000000),'memo'+str(RAND()*100000000))

 set @i=@i+1

end

 if (@@TRANCOUNT>0)COMMITTRAN

set @endTime=GETDATE()

select @endTime-@beginTime

黄色高亮是我改进的地方。我在同样的机器上跑了一下，奇迹发生了，它只使用了7秒多一点。快了5倍多！如果在看下performance