SQL Server accelerates data generation/import with bulk (batchsize) submissions

In the minimized log operations resolution, the app 's article has friends that reflect the slow generation of test data. Here's a simple app to share with you to speed up data import in the form of batch submissions during data generation.

This application not only generates test data, but in bcp import data, the replication initialization snapshot process can be moderately sized to the system environment to improve the import/initialization speed of the batchsize.

Application Ideas: Here is a brief introduction to the concept of group submission, because the relational database relies on the log to ensure data integrity, that is, write the log, every time a transaction is completed, the commit log is required to brush into the disk, In the premise of high concurrency and short transactions, the overall write throughput decreases due to the log frequent landing. Group commit to set up a batch of transactions (the same, or different sessions) into groups of batches to complete, reduce the frequency of log write, so that the log batch brush into the disk, To improve performance. But this approach reduces response time to a certain extent (because committed transactions may wait for other transactions to be committed together)

There is no response for group submissions in SQL Server, but developers can implement similar functions on their own environment, with the application controllable:)

Here refers to the way to generate test data to apply "code 1" to generate data in general, "Code 2" batch commit to generate Data mode for you to make a simple example.

Figure 1-1 is a comparison of the performance counter log flushs/sec in two modes of generation to describe the benefits of SQL Server Group Commit

Code 1 generates test data in a generic manner: in my native execution time is 56s

Create TableT1 (IDint  not NULL Identity(1,1), Dystrvarchar( $), FixstrChar( -));GoDeclare @beginTime datetime,@endTime datetimeSet @beginTime=GETDATE()SetNocount onDeclare @i intSet @i=0 while(@i<200000)begin  Insert  intoT1 (DYSTR,FIXSTR)Values('AAA'+Str(RAND()*100000000),'BBB'+Str(RAND()*100000000))  Set @i=@i+1EndSet @endTime=GETDATE()Select @endTime-@beginTime----------56s My PC

Code 2 generates test data in batch mode (group submissions). In my native execution time is 4s!

Checkpoint-----Flush data to diskDBCCdropcleanbuffers-----Drop Data CacheCreate TableT2 (IDint  not NULL Identity(1,1), Dystrvarchar( $), FixstrChar( -));GoDeclare @beginTime datetime,@endTime datetimeSet @beginTime=GETDATE()SetNocount on Declare @batchSize intSet @batchSize= +Declare @i intSet @i=0 while(@i<20000)begin  if(@i%@batchSize=0)    begin      if(@ @TRANCOUNT>0)COMMIT TRAN      BEGIN TRAN    End  Insert  intoT2 (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 in two different ways log flushs/sec comparison

1-1

BCP Simple instance:

Control batchsize during Bulk import

 from ' \t.bcp '  with (fire_triggers, datafiletype='native', TABLOCK, BatchSize =  +  )

Configuring BatchSize in the Snapshot Agent configuration file