我经常看到客户测试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脚本如下:
usemyDB
go
createtablet1(idintprimarykeynotnullidentity(1,1),namevarchar(200),memochar(500));
go
truncatetablet1
go
declare@iint
set@i=0
declare@beginTimedatetime,@endTimedatetime
set@beginTime=GETDATE()
while(@i<200000)
begin
insertintot1(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如下面脚本所示。
truncatetablet1
go
declare@iint
set@i=0
declare@beginTimedatetime,@endTimedatetime
set@beginTime=GETDATE()
declare@batchSizeint
set@batchSize=1000
while(@i<200000)
begin
if(@i%@batchSize=0)
begin
if(@@TRANCOUNT>0)COMMITTRAN
BEGINTRAN
end
insertintot1(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
本文原创发布php中文网,转载请注明出处,感谢您的尊重!