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.

1  Public classUser2 {3      PublicUser ()4     {5         6     }7      Public stringUserName {Get;Set; }8      Public stringPassWord {Get;Set; }9      Public stringAddress {Get;Set; }Ten      Public stringTelephone {Get;Set; } One}

View Code

Database User Table structure:

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

1     protectedDataTable getdatatabledata ()2     {3List<user> userlist=NewList<user>();4          for(inti =0; I <10000; i++)5         {6User user=NewUser ();7User. UserName ="Zhangsan";8User. PassWord ="123456";9User. Address ="Beijing Road No.";TenUser. Telephone ="13232323232"; One userlist.add (user); A         } -         using(DataTable dt =NewDataTable ()) -         { theDt. Columns.Add ("UserName",typeof(string)); -Dt. Columns.Add ("PassWord",typeof(string)); -Dt. Columns.Add ("Address",typeof(string)); -Dt. Columns.Add ("Telephone",typeof(string)); +             foreach(User Userinchuserlist) -             { +DataRow dr =dt. NewRow (); Adr["UserName"] =user. UserName; atdr["PassWord"] =user. PassWord; -dr["Address"] =user. Address; -dr["Telephone"] =user. Telephone; - dt. Rows.Add (DR); -             } -             returnDT; in         } -}

View Code

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

1     protected voidButton1_Click (Objectsender, EventArgs e)2     {3         stringConstr = configurationmanager.connectionstrings["Constr"]. ConnectionString;4SqlConnection connection=NewSqlConnection (constr);5         Try6         {7             using(SqlBulkCopy bulkcopy =NewSqlBulkCopy (Constr, sqlbulkcopyoptions.keepidentity|sqlbulkcopyoptions.useinternaltransaction))8             {9Bulkcopy.destinationtablename ="dbo. [User]";//The target table, which is what table you're going to insert the data into .TenBULKCOPY.COLUMNMAPPINGS.ADD ("UserName","UserName");//mapping relationship between column names in the data source and properties of the target table OneBULKCOPY.COLUMNMAPPINGS.ADD ("PassWord","PassWord"); ABULKCOPY.COLUMNMAPPINGS.ADD ("Address","Address"); -BULKCOPY.COLUMNMAPPINGS.ADD ("Telephone","Telephone"); -DataTable dt = Getdatatabledata ();//Data source Data the                 //bulkcopy.batchsize = 3; -Stopwatch stopwatch=NewStopwatch ();//stopwatch, this class can be time-counted -Stopwatch. Start ();//Stopwatch Start -Bulkcopy.writetoserver (DT);//writing data source data to the target table +Response.Write ("time to insert the data:"+stopwatch. Elapsed);//stopwatch end, elapsed is the time to count -             }  +         } A         Catch(Exception ex) at         {  -             Throw NewException (ex. Message); -         } -Response.Write ("<br/> Insert success!!! "); -}

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.

1 CREATE PROC usp_insertusertable2  as3 BEGIN4 DECLARE @i INT5SET @i=16While @i<100017 BEGIN8 INSERT into dbo. [User]9 (UserName,Ten PassWord, One Address, A Telephone -                 ) -VALUES (N'Zhangsan',--Username-nvarchar ( -) theN'123456',--Password-nvarchar (6) -N'Beijing Road No.',--Address-nvarchar ( -) -                   '13232323232'--Telephone-varchar ( One) -                 ) +  -SET @[email protected]+1 + END AEND

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.