我經常看到客戶測試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
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
