C # Three ways to bulk insert data into SQL Server _c# tutorial

In this article, I'll explain a lot about inserting data in SQL Server in the future.

To create a database and table to test, the primary key in the table is a GUID, and no indexes are created in the table in order for the data to be inserted faster. GUIDs are bound to be faster than self growth, because the time you take to generate a GUID algorithm is certainly less than the number of times you requery the ID of the previous record from the datasheet and then add a 1 operation. In the case of an index, indexing is rebuilt every time the insert record is present, which can be very performance-intensive. If there is an unavoidable index in the table, we can improve efficiency by deleting the index first, then inserting it in bulk, and then rebuilding the index.

Create database Carsys; 
Go use 
carsys; 
Go 
CREATE TABLE Product (
Id uniqueidentifier PRIMARY KEY,
NAME VARCHAR (m) not NULL, price
DECIMAL (18,2 ) Not NULL
)

We insert data through SQL scripts, which is common in the following three ways.

Way One, One insertion, the least performance, is not recommended.

INSERT into Product (id,name,price) VALUES (NEWID (), ' Cattle 1 ', 160);
INSERT into Product (id,name,price) VALUES (NEWID (), ' Pen 2 ', and ') ';
......

Mode two

INSERT into Product (id,name,price) VALUES
(NEWID (), ' pen 1 ', 160)
, (NEWID (), ' Pen 2 ',) ...

Mode three

INSERT into Product (id,name,price)
 Select NEWID (), ' pen 1 ', 160 
 UNION all 
 SELECT NEWID (), ' Cattle 2 ', 180
 UNION All
... 

There are also three ways to implement bulk operations through Ado.net in C #.

Mode one: Insert by article

 static void Insertone () {Console.WriteLine ("implemented in one insert Way");
   stopwatch SW = new Stopwatch ();

   Long totalrow = 1000000;
   using (SqlConnection conn = new SqlConnection (strconnmsg))//using 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", "commodity" + i); Cmd.
      Parameters.addwithvalue ("@d", I); Sw.
      Start (); Cmd.

      ExecuteNonQuery (); Console.WriteLine (String. Format ("Insert 1 records, Time: {0}", SW.)
     Elapsedmilliseconds)); } if (i = = 1000) {sw.
      Stop ();
     Break }} Console.WriteLine (String. Format (Insert {0} records, each 1000 insertion time is {1} milliseconds, the estimated total 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 the 100w record is expected to take 50 minutes, and it may take about 3 milliseconds to insert a record each time.

Mode two: using Sqlbulk

  #region mode two static void Inserttwo () {Console.WriteLine ("Use BULK INSERT implementation");
   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} records spend {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:

Insert 100w record only 8s more, is not very slip.

mode three: inserting data using TVPs (table-valued parameters)

Support for TVPS starting with SQL Server 2008. Create the cached table Producttemp and execute the following SQL.

CREATE TYPE producttemp as TABLE (
Id uniqueidentifier PRIMARY KEY,
NAME VARCHAR not NULL, price
DECIMAL (1 8,2) not NULL
)

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

It is more than 11 seconds to insert the 100w record into the visible section.

Summary: Large Data Bulk insert mode as far as possible to avoid the use, and mode two and way Sandu is very efficient BULK INSERT data mode. It is inserted through the construction of a DataTable, and we know that the DataTable exists in memory, so when the volume of the data is particularly large, it is too large to be stored in memory at once, and can be inserted in segments. For example, you need to insert 90 million pieces of data, can be divided into 9 paragraphs to insert, one insertion 10 million. 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 #, which is when we use using or conn. Close (), when the connection is released, it does not actually shut down the database connection, it just makes the connection exist in a way that is similar to hibernation, and when you do it again, you will find a dormant connection from the connection pool to wake it up, which can effectively improve the concurrency and reduce the connection loss. We can configure the number of connections in the connection pool.

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

The above is the entire content of this article, I hope to help you learn, but also hope that we support the cloud habitat community.