SQL Server 利用批量(batchsize)提交加快数据生成/导入

在最小化日志操作解析,应用的文章中有朋友反映生成测试数据较慢.在此跟大家分享一个简单的应用,在生成数据过程中采用批量提交的方式以加快数据导入.

此应用不光生成测试数据上,在BCP导入数据中,复制初始化快照过程中等都可以根据系统环境调整 batchSize 的大小来提高导入/初始化速度.

应用思想:这里简单介绍下组提交概念,由于关系型数据库依靠日志来保证数据完整性,即先写日志,每当一个事务完成时就需要commit日志刷入磁盘,在高并发短小事务的前提下由于日志频繁落盘导致整体写吞吐下降.用Group Commit方式将一批事务(相同,或不同session)成组批量提交完成,降低日志写的频繁度,使得日志批量刷入磁盘,从而提高性能.但此方式会一定程度降低响应时间(因为提交的事务可能等待其他事务一起提交)

Sql server中没有提供组提交的响应方式,但开发人员可以在应用可控前提下,自行根据环境实现类似功能:)

这里引用生成测试数据的方式分别应用"代码 1"一般生成数据方式,"代码 2"批量提交生成数据方式给大家做个简单的实例.

图1-1为两种生成方式下性能计数器Log Flushs/sec的比较,用来描述“Sql Server Group Commit"的优势

代码1 按照一般方式生成测试数据:在我本机的执行时间为56s

create table t1(id int not null identity (1,1),dystr varchar(200),fixstr char(500));
go

declare @beginTime datetime,@endTime datetime
set @beginTime=GETDATE()
set nocount on
declare @i int
set @i=0
while(@i<200000)
begin
  insert into t1(dystr,fixstr)values('aaa'+str(RAND()*100000000),'bbb'+str(RAND()*100000000))
  set @i=@i+1
end
set @endTime=GETDATE()
select @endTime-@beginTime
----------56s my PC

代码2  按照批量方式(组提交)生成测试数据. 在我本机的执行时间为4s!

Checkpoint-----flush data to disk
Dbcc dropcleanbuffers -----drop data cache
create table t2(id int not null identity (1,1),dystr varchar(200),fixstr char(500));
go
declare @beginTime datetime,@endTime datetime
set @beginTime=GETDATE()

set nocount on 

declare @batchSize int
set @batchSize=1000
declare @i int
set @i=0
while(@i<20000)
begin
  if (@i%@batchSize=0)
    begin
      if (@@TRANCOUNT>0)COMMIT TRAN
      BEGIN TRAN
    end

  insert into t2(dystr,fixstr)values('aaa'+str(RAND()*100000000),'bbb'+str(RAND()*100000000))
  set @i=@i+1

end
 if (@@TRANCOUNT>0)COMMIT TRAN
select @endTime-@beginTime
----------4s my PC

两种方式下Perf count中Log Flushs/sec对比

                                                   1-1

BCP简单实例:

批量导入时控制batchsize

bulkinsert t1 from'\t.bcp'
 with (
 fire_triggers,
 datafiletype='native',
 tablock,
 batchsize=1000
 )

快照代理配置文件中配置batchsize

Involuntary DBA