. Net batch big data insertion performance analysis and comparison (5. Use sqlbulkcopy)

# Region use sqlbulkcopy <br/> Public static bool executetransactionscopeinsert (datatable DT, int batchsize) <br/>{< br/> int COUNT = DT. rows. count; <br/> string tablename = "testtable"; <br/> int copytimeout = 600; <br/> bool flag = false; <br/> try <br/> {<br/> using (sqlconnection Cn = new sqlconnection (connectionstring )) <br/>{< br/> using (transactionscope scope = new transactionscope () <br/>{< br/> CN. open (); <br/> using (sqlbulkcopy SBC = new sqlbulkcopy (CN )) <br/>{< br/> // name of the target table on the server <br/> SBC. destinationtablename = tablename; <br/> SBC. batchsize = batchsize; <br/> SBC. bulkcopytimeout = copytimeout; <br/> for (INT I = 0; I <DT. columns. count; I ++) <br/>{< br/> // column ing defines the relationship between the columns in the data source and the columns in the target table <br/> SBC. columnmappings. add (DT. columns [I]. columnname, DT. columns [I]. columnname); <br/>}< br/> SBC. writetoserver (DT); <br/> flag = true; <br/> scope. complete (); // valid transaction <br/>}< br/> catch (exception ex) <br/> {<br/> loghelper. error (ex. message); <br/> return false; <br/>}< br/> return flag; <br/>}< br/> # endregion

 

The result is as follows:

Use sqlserver transactionscope insert; recordcount: 40000; batchsize: 10; Time: 10314;

Use sqlserver transactionscope insert; recordcount: 40000; batchsize: 20; Time: 4476;

Use sqlserver transactionscope insert; recordcount: 40000; batchsize: 50; Time: 2021;

Use sqlserver transactionscope insert; recordcount: 40000; batchsize: 100; Time: 1332;

Use sqlserver transactionscope insert; recordcount: 40000; batchsize: 200; Time: 978;

Use sqlserver transactionscope insert; recordcount: 40000; batchsize: 400; Time: 730;

Use sqlserver transactionscope insert; recordcount: 40000; batchsize: 500; Time: 649;

Use sqlserver transactionscope insert; recordcount: 40000; batchsize: 600; Time: 623;

Use sqlserver transactionscope insert; recordcount: 40000; batchsize: 700; Time: 669;

Use sqlserver transactionscope insert; recordcount: 40000; batchsize: 800; Time: 585;

Use sqlserver transactionscope insert; recordcount: 40000; batchsize: 1000; Time: 681;

 

Sqlbulkcopy adopts the BCP Protocol of SQL Server for Batch Data Replication. In combination with transactions, about 800 entries per batch is the balance point in our case, the performance is improved by more than 100 times than insert one by one, and the performance of the case with transaction batch insert is also improved by more than 7 times.

 

Full text link:

. Net batch big data insertion performance analysis and comparison (1. Preparation)

. Net batch big data insertion performance analysis and comparison (2. Normal insertion and splicing SQL batch insertion)

. Net batch big data insertion performance analysis and comparison (3. Use transactions)

. Net batch big data insertion performance analysis and comparison (4. Batch insertion using dataadapter)

. Net batch big data insertion performance analysis and comparison (5. Use sqlbulkcopy)

. Net batch big data insertion performance analysis and comparison (6. Using Table value parameters)