SQL Server 利用批量(batchsize)提交加快資料產生/匯入

標籤：des   style   blog   http   color   os   strong   檔案   

在最小化日誌操作解析,應用的文章中有朋友反映產生測試資料較慢.在此跟大家分享一個簡單的應用,在產生資料過程中採用批量提交的方式以加快資料匯入.

此應用不光產生測試資料上,在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