Blog - Title

使用 Batch Size 提高 Transaction 性能

使用 Batch Size 提高 Transaction 性能

  • Comments 3

 

我经常看到客户测试SQL Server 的插入(INSERT)或者批量导入大量数据的性能表现。其中有测试大量INSERTTSQL脚本如下:

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

  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的次数非常高。在我的机器上达到5000Log 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

   

  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

 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类型(RAID10RAID5的写快多了)等。

 

 

 

Leave a Comment
  • Please add 8 and 2 and type the answer here:
  • Post
  • 这样测试是不公平的,因为客户的实际使用环境并不是这样的。我要是用Hekaton不写日志不是更快。

  • bulk insert t1 from'\t.bcp'

    刚才测试了bcp文件是导入不了的

  • 这篇文章只是提供了一种思路供大家参考,很有意义。说什么公平不公平、不写日志什么的就理解偏激了

Page 1 of 1 (3 items)