首页 > 代码库 > SQL Server 利用批量(batchsize)提交加快数据生成/导入

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

2024-07-14 00:02:41   221人阅读

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

此应用不光生成测试数据上,在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));godeclare @beginTime datetime,@endTime datetimeset @beginTime=GETDATE()set nocount ondeclare @i intset @i=0while(@i<200000)begin  insert into t1(dystr,fixstr)values(‘aaa‘+str(RAND()*100000000),‘bbb‘+str(RAND()*100000000))  set @i=@i+1endset @endTime=GETDATE()select @endTime-@beginTime----------56s my PC

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

Checkpoint-----flush data to diskDbcc dropcleanbuffers -----drop data cachecreate table t2(id int not null identity (1,1),dystr varchar(200),fixstr char(500));godeclare @beginTime datetime,@endTime datetimeset @beginTime=GETDATE()set nocount on declare @batchSize intset @batchSize=1000declare @i intset @i=0while(@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+1end if (@@TRANCOUNT>0)COMMIT TRANselect @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


联系
我们