Three ways to "reprint" C # BULK INSERT data into SQL Server

Citation: https://m.jb51.net/show/99543 This article is mainly for you to introduce in detail the C # BULK INSERT data into SQL Server three ways, with a certain reference value, interested in small partners can refer to

In this article, I will explain in the future the bulk insert data in SQL Server.

Create a database and table for testing, to make inserting data faster, the primary key in the table is a GUID, and no index is created in the table. GUIDs are bound to be faster than self-growth, because you will have less time to generate a GUID algorithm than to re-query the ID value of the previous record from the data table and then add the 1 operation. In the case of an index, the index is rebuilt every time the record is inserted, which is very performance-intensive. If there is no avoidable index in the table, we can improve efficiency by first deleting the index, then bulk inserting, and finally rebuilding the index.

Create Database Carsys; Go use Carsys; Go CREATE TABLE Product (Id uniqueidentifier PRIMARY key,name VARCHAR () not Null,price DECIMAL (18,2) not NULL)

We insert data through SQL scripts, which are common in three ways.

Way One, One insert, the worst performance, is not recommended.

Insert into product (Id,name,price) values (NEWID (), ' Barn 1 ', "INSERT INTO Product" (Id,name,price) VALUES (NEWID (), ' 2 section of the Barn ', 260);

Way Two

INSERT into Product (id,name,price) VALUES (NEWID (), ' Cattle Barn 1 ', (newid (), ' Cattle Barn 2 segment ', 260) ...

Mode three

INSERT into Product (id,name,price) SELECT NEWID (), ' Barn 1 segment ',  UNION all  

There are also three ways to implement bulk operations via ADO in C #.

Way One: Insert article by clause

  static void Insertone ()  {   Console.WriteLine ("Implemented in an insert-by-line manner");   Stopwatch SW = new Stopwatch ();   Long totalrow = 1000000;   The using (SqlConnection conn = new SqlConnection (strconnmsg))//using will automatically open and close connections.   {    String sql = "INSERT into Product (id,name,price) VALUES (NEWID (), @p,@d)";    Conn. Open ();    for (int i = 0; i < 1000000; i++)    {     using (SqlCommand cmd = new SqlCommand (SQL, conn))     {      cmd. Parameters.addwithvalue ("@p", "goods" + i);      Cmd. Parameters.addwithvalue ("@d", I);      Sw. Start ();      Cmd. ExecuteNonQuery ();      Console.WriteLine (String. Format ("Insert 1 records, Time: {0}", SW.) Elapsedmilliseconds));     }     if (i = =)     {      sw. Stop ();      Break   ;   }}} Console.WriteLine (String. Format ("Insert {0}" record, every 1000 insert time is {1} milliseconds, the estimated insertion time is {2} milliseconds, {3} minutes ", TotalRow, SW.) Elapsedmilliseconds, (SW. elapsedmilliseconds/1000) * TotalRow), Getminute (SW. elapsedmilliseconds/1000 * totalrow)));  }

The results of the operation are as follows:

We will find that inserting 100w Records, it is expected to take 50 minutes, each insert a record about 3 milliseconds.

Way Two: using Sqlbulk

  #region mode two static void Inserttwo () {Console.WriteLine ("Implementation using BULK Insert");   Stopwatch SW = new Stopwatch ();   DataTable dt = GetTableSchema ();    using (SqlConnection conn = new SqlConnection (strconnmsg)) {SqlBulkCopy bulkcopy = new SqlBulkCopy (conn);    Bulkcopy.destinationtablename = "Product"; bulkcopy.batchsize = dt.    Rows.Count; Conn.    Open (); Sw.    Start (); for (int i = 0; i < totalrow;i++) {DataRow dr = dt.     NewRow ();     Dr[0] = Guid.NewGuid (); DR[1] = string.     Format ("Commodity", I);     DR[2] = (decimal) i; Dt.    Rows.Add (DR); } if (dt! = null && dt.      Rows.Count! = 0) {bulkcopy.writetoserver (dt); Sw.     Stop (); } Console.WriteLine (String. Format ("Insert {0}" record has a total cost of {1} milliseconds, {2} minutes ", TotalRow, SW.) Elapsedmilliseconds, Getminute (SW.   (elapsedmilliseconds)));   }} static DataTable GetTableSchema () {datatable dt = new DataTable (); Dt. Columns.addrange (new datacolumn[] {new DataColumn ("Id", typeof (Guid)), New DataColumn ("Name", typeof(string)), New DataColumn ("Price", typeof (Decimal))});  return DT; } #endregion

The results of the operation are as follows:

Inserting 100w Records is more than 8s, is not very slip.

Way Three: inserting data using TVPs (table-valued parameters)

Support for TVPS starting from SQL Server 2008. Create the cache table Producttemp and execute the following SQL.

CREATE TYPE producttemp as TABLE (Id uniqueidentifier PRIMARY key,name VARCHAR () not Null,price DECIMAL (18,2) not NULL)

After execution, you will find that there is one more cache table below the database Carsys producttemp

It takes more than 11 seconds to insert a 100w record.

Summary: Big Data BULK Insert mode one avoids using as far as possible, while mode two and mode San du are very efficient BULK insert data way. It is inserted through the construction of a DataTable, and we know that the DataTable is in memory, so when the amount of data is particularly large, large to memory can not be stored in a single time, may be segmented insert. For example, you need to insert 90 million pieces of data, you can insert it into 9 segments, and insert 10 million pieces at a time. We should try to avoid the database operation directly in the For loop. Each time the database is connected, opened, and closed is time consuming, although there is a database connection pool in C #, that is, when we use using or conn. Close (), to release the connection, actually did not actually shut down the database connection, it just let the connection in a similar way to hibernate, when the operation again, the connection pool will find a dormant connection, wake it up, this can effectively improve concurrency, reduce connection loss. We can configure the number of connections in the connection pool.

SOURCE Download: Https://pan.baidu.com/s/1slKrrLr

Reprint "C # three ways to insert data into SQL Server in bulk