Run the following script to set up the test database and table-valued parameters.
Copy Code code as follows:
--create DataBase
Create Database Bulktestdb;
Go
Use Bulktestdb;
Go
--create Table
Create Table Bulktesttable (
Id int PRIMARY KEY,
UserName nvarchar (32),
PWD varchar (16))
Go
--create Table Valued
CREATE TYPE Bulkudt as TABLE
(Id int,
UserName nvarchar (32),
PWD varchar (16))
Below we use the simplest INSERT statement to insert 1 million data, the following code:
Copy Code code as follows:
stopwatch SW = new Stopwatch ();
SqlConnection sqlconn = new SqlConnection (
configurationmanager.connectionstrings["ConnStr"]. ConnectionString);//Connect database
SqlCommand Sqlcomm = new SqlCommand ();
Sqlcomm.commandtext = string. Format ("INSERT into bulktesttable (ID,USERNAME,PWD) VALUES (@p0, @p1, @p2)");//Parameterized SQL
SQLCOMM.PARAMETERS.ADD ("@p0", SqlDbType.Int);
SQLCOMM.PARAMETERS.ADD ("@p1", SqlDbType.NVarChar);
SQLCOMM.PARAMETERS.ADD ("@p2", SqlDbType.VarChar);
Sqlcomm.commandtype = CommandType.Text;
Sqlcomm.connection = sqlconn;
Sqlconn.open ();
Try
{
The loop inserts 1 million data, inserts 100,000 at a time, inserts 10 times.
for (int multiply = 0; multiply < multiply++)
{
for (int count = multiply * 100000 count < (multiply + 1) * 100000; count++)
{
sqlcomm.parameters["@p0"]. Value = count;
sqlcomm.parameters["@p1"]. Value = string. Format ("user-{0}", Count * multiply);
sqlcomm.parameters["@p2"]. Value = string. Format ("pwd-{0}", Count * multiply);
Sw. Start ();
Sqlcomm.executenonquery ();
Sw. Stop ();
}
Displays the time of the insertion after each insert of 100,000 data
Console.WriteLine (String. Format ("Elapsed time is {0} milliseconds", SW. Elapsedmilliseconds));
}
}
catch (Exception ex)
{
Throw ex;
}
Finally
{
Sqlconn.close ();
}
Console.ReadLine ();
The time consuming diagram is as follows:
Because it was too slow to insert 100,000, it took a 72390 milliseconds, so I forced it to stop manually.
Here's a look at the use of bulk inserts:
The main idea of the bulk method is to insert data from a table into a database at once by slowing down the data on the client table and then using SqlBulkCopy
The code is as follows:
Copy Code code as follows:
public static void Bulktodb (DataTable dt)
{
SqlConnection sqlconn = new SqlConnection (
configurationmanager.connectionstrings["ConnStr"]. ConnectionString);
SqlBulkCopy bulkcopy = new SqlBulkCopy (sqlconn);
Bulkcopy.destinationtablename = "Bulktesttable";
bulkcopy.batchsize = dt. Rows.Count;
Try
{
Sqlconn.open ();
if (dt!= null && dt. Rows.Count!= 0)
Bulkcopy.writetoserver (DT);
}
catch (Exception ex)
{
Throw ex;
}
Finally
{
Sqlconn.close ();
if (bulkcopy!= null)
Bulkcopy.close ();
}
}
public static DataTable GetTableSchema ()
{
DataTable dt = new DataTable ();
Dt. Columns.addrange (New datacolumn[]{
New DataColumn ("Id", typeof (int)),
New DataColumn ("UserName", typeof (String)),
New DataColumn ("Pwd", typeof (String))};
return DT;
}
static void Main (string[] args)
{
stopwatch SW = new Stopwatch ();
for (int multiply = 0; multiply < multiply++)
{
DataTable dt = Bulk.gettableschema ();
for (int count = multiply * 100000 count < (multiply + 1) * 100000; count++)
{
DataRow r = dt. NewRow ();
R[0] = count;
R[1] = string. Format ("user-{0}", Count * multiply);
R[2] = string. Format ("pwd-{0}", Count * multiply);
Dt. Rows.Add (R);
}
Sw. Start ();
BULK.BULKTODB (DT);
Sw. Stop ();
Console.WriteLine (String. Format ("Elapsed time is {0} milliseconds", SW. Elapsedmilliseconds));
}
Console.ReadLine ();
}
The time consuming diagram is as follows:
Visible, the use of bulk, efficiency and performance increased significantly. Inserting 100,000 data with inserts takes 72390, and now inserting 1 million data using bulk is a 17583-time consuming operation.
Finally look at the efficiency of using table-valued parameters, which you would be surprised to see.
The table-valued parameter is the new SQL Server 2008 feature, referred to as TVPs. For friends who are not familiar with table-valued parameters, you can refer to the latest book online and I will write another blog about table-valued parameters, but this does not introduce the concept of table-valued parameters too much. Anyway, look at the code:
Copy Code code as follows:
public static void Tablevaluedtodb (DataTable dt)
{
SqlConnection sqlconn = new SqlConnection (
configurationmanager.connectionstrings["ConnStr"]. ConnectionString);
Const string tsqlstatement =
"INSERT into bulktesttable (id,username,pwd)" +
"Select NC. Id, NC. Username,nc. PWD "+
"From @NewBulkTestTvp as NC";
SqlCommand cmd = new SqlCommand (tsqlstatement, sqlconn);
SqlParameter catparam = cmd. Parameters.addwithvalue ("@NewBulkTestTvp", DT);
Catparam.sqldbtype = sqldbtype.structured;
The name of the table-valued parameter is Bulkudt, which is in the SQL of the build test environment above.
Catparam.typename = "dbo." Bulkudt ";
Try
{
Sqlconn.open ();
if (dt!= null && dt. Rows.Count!= 0)
{
Cmd. ExecuteNonQuery ();
}
}
catch (Exception ex)
{
Throw ex;
}
Finally
{
Sqlconn.close ();
}
}
public static DataTable GetTableSchema ()
{
DataTable dt = new DataTable ();
Dt. Columns.addrange (New datacolumn[]{
New DataColumn ("Id", typeof (int)),
New DataColumn ("UserName", typeof (String)),
New DataColumn ("Pwd", typeof (String))};
return DT;
}
static void Main (string[] args)
{
stopwatch SW = new Stopwatch ();
for (int multiply = 0; multiply < multiply++)
{
DataTable dt = Tablevalued.gettableschema ();
for (int count = multiply * 100000 count < (multiply + 1) * 100000; count++)
{
DataRow r = dt. NewRow ();
R[0] = count;
R[1] = string. Format ("user-{0}", Count * multiply);
R[2] = string. Format ("pwd-{0}", Count * multiply);
Dt. Rows.Add (R);
}
Sw. Start ();
TABLEVALUED.TABLEVALUEDTODB (DT);
Sw. Stop ();
Console.WriteLine (String. Format ("Elapsed time is {0} milliseconds", SW. Elapsedmilliseconds));
}
Console.ReadLine ();
}
The time consuming diagram is as follows:
It's 5 seconds faster than bulk.
This article originally from Csdn Tjvictor
