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