使用 Batch Size 提高 Transaction 性能

我经常看到客户测试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(@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如下面脚本所示。

 

 

truncatetable t1

go

 

declare @i int

set @i=0

declare @beginTime datetime,@endTime datetime

set @beginTime=GETDATE()

 

declare @batchSize int

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

 Log���面的log flushes/sec，已经减少到700左右。我的测试图如下所示：

 

 

上面的这个小实验充分说明了batch size的重要性。它把很多小的transaction合并成一个大的合适的 transaction来减少磁盘写操作，从而获得极大性能提升。Batch size究竟多大才是最佳的呢？这个取决您的机器，需要你自己测试。

 

很多地方需要设置batch size提高性能。比如如果你使用游标（cursor）操作数据，记得使用batch Size。在bcp里面，也需要指定batchsize来提高性能：

 

bcp/?

usage: bcp {dbtable | query} {in | out | queryout | format} datafile

  [-m maxerrors]            [-f formatfile]          [-e errfile]

  [-F firstrow]             [-L lastrow]             [-b batchsize]

  [-n native type]          [-c character type]      [-w wide character type]

  [-N keep non-text native] [-V file format version] [-q quoted identifier]

  [-C code page specifier]  [-t field terminator]    [-r row terminator]

  [-i inputfile]            [-o outfile]             [-a packetsize]

  [-S server name]          [-U username]            [-P password]

  [-T trusted connection]   [-v version]             [-R regional enable]

  [-k keep null values]     [-E keep identity values]

  [-h "load hints"]         [-x generate xml format file]

  [-d database name]

 

如果使用bulk insert，也记得使用batch size

 

bulkinsert t1 from'\t.bcp'

 with (

 fire_triggers,

 datafiletype='native',

 tablock,

 batchsize=1000

 )

 

当然，性能优化里面batchSize并不是唯一需要考虑的因素。还需要考虑其他方面，比如并发开几个窗口运行bcp，使用合适的磁盘的RAID类型（RAID10比RAID5的写快多了）等。