SqlBulkCopy class for Big data (more than 100 million) insert Test

Many days did not write a blog, just graduated one months, on the road of it is really confused ah!

As mentioned in the previous blog, in the bulk data insert database can be passed to the stored Procedure Type table parameters for related operations, in this process I did not perform efficiency test. Later discovery found that large data insertions can be done through the SqlBulkCopy class, this article introduces the role of this class and makes the relevant efficiency tests (compared to the INSERT statement).

SqlBulkCopy can only write data to a SQL Server table, which can be on the same server or on a remote server. Of course, the data to be written is not just SQL Server, it can be any data source, as long as the data can be loaded into the DataTable or DataReader can read the data.

The SqlBulkCopy class BULK insert data code is as follows:

1. First create a user class that corresponds to the user table of the database.

View Code

Database User Table structure:

2. Create a DataTable method that returns 10,000 data stored:

View Code

3. Put a button on the page, click the button to insert the data, the button click event code is as follows:

View Code

The code for bulk inserting data through INSERT statements in SQL Server is as follows:

1. First create a stored procedure that inserts 10,000 data that is identical to the data inserted in the SqlBulkCopy.

View Code

2. Execute the stored procedure.
EXEC dbo. Usp_insertusertable

The SqlBulkCopy and INSERT statement efficiency tests are compared as follows:

SqlBulkCopy Time Statistics (5 times):

SQL Server INSERT statement takes a time statistic (5 times), which is tested by the tools---SQL Servers profiler, using a template of tsql_duration:

One thing to note: whether it's a sqlbulkcopy or an INSERT statement, the buffer cleanup should be done before each test, and the old data should be deleted to avoid impact on the test, the code is as follows:

DBCC dropcleanbuffers
DBCC Freesystemcache (' all ')
DELETE dbo. [User]

Conclusion: Through the statistical analysis above, it can be seen that the efficiency performance of sqlbulkcopy operation Big Data insertion is significantly more efficient than INSERT statement.

I Caishuxueqian, where there is not a welcome point.