About SQL Bulk Insert Data method comparison

Technical programme I:
The first version written by the programmer in time of compression, just to complete the task, did not do any optimization from the program, the implementation of the method is to use the database tutorial access class call stored procedures, the use of loop-by-article insertion. Obviously, this approach is not efficient, so there are two former colleagues to discuss the problem of inefficiency.
Technical Programme II:
Due to the large volume of data inserted, I thought of a new feature of ado.net2.0: SqlBulkCopy. As for this performance, I did the performance test myself very early, and the efficiency was very high. This is also a technical proposal that I recommend to my colleagues in the company.
Technical Programme III:
Take advantage of the new features of SQLServer2008-table-valued parameters (table-valued Parameter). Table-valued parameters are a new feature of SQLServer2008, and with this new feature we can pass a table type as a parameter to a function or stored procedure. However, it also has a feature: table-valued parameters perform well when inserting fewer than 1000 rows.
Technical Programme IV:
For single-column fields, you can concatenate the data you want to insert, and then split it into an array in the stored procedure, and then insert one by one. Check the maximum length of the string in the stored procedure, then divide by the length of the field to calculate a value, which is obviously enough to meet the requirements, but it doesn't seem to improve with the first method, because the principle is the same.
Technical Programme V:
Consider asynchronous creation, Message Queuing, and so on. This kind of scheme is not only difficult in design but also in development.
The technical solution is definitely going to be lost, and the rest is to make a choice between technical programme two and technical programme three, in view of the current situation of the company, the technical programme IV and the technical programme five will not be considered first.
Next, in order to give you a more perceptual understanding of the creation and invocation of table-valued parameters, I'll write in more detail, and the article may be slightly longer, and friends who don't pay attention to details can choose to read in leaps and bounds.
Again the test plan, the test is divided into three groups, the number of inserts is less than 1000, the other two groups are inserted more than 1000 of the data (we take 10000 and 1000000 respectively), each group of tests are divided 10 times, take the average. How to do all understand, let ' s go!

1. Create a table.

For simplicity, there is only one field in the table, as shown in the following illustration:

2. Create table-valued parameter types

We open the Query Analyzer and execute the following code in Query Analyzer:

Create Type Passporttabletype as Table
(
Passportkey nvarchar (50)
After successful execution, we open Enterprise Manager, expand the following nodes sequentially--database, expand Programmability, types, user-defined table types, and you can see the table value types we created, as shown in the following illustration:

It means that we created the table value type successfully.

3. Writing Stored Procedures

The code for the stored procedure is:

Copy code code as follows:
Use [Testinsert]
Go
/****** object:storedprocedure [dbo]. [CREATEPASSPORTWITHTVP] Script date:03/02/2010 00:14:45 ******/
SET ANSI_NULLS on
Go
SET QUOTED_IDENTIFIER ON
Go
-- =============================================
--Author: <Kevin>
--Create Date: <2010-3-1>
--Description: < Create pass >
-- =============================================
Create PROCEDURE [dbo]. [CREATEPASSPORTWITHTVP]
@TVP Passporttabletype readonly
As
BEGIN
SET NOCOUNT on;
Insert into Passport (passportkey) Select Passportkey from @TVP
End

Perhaps in Query Analyzer, the smart prompt prompts for a problem with the table value type, a red underline (see below), no need to ignore, continue to run our code, complete the creation of the stored procedure

4. Write code to call the stored procedure.
Three kinds of database insert way code as follows, because the time is tight, the code may not be so easy to read, special code I added some comments.
Copy code code as follows:
Using System;
Using System.Diagnostics;
Using System.Data;
Using System.Data.SqlClient;
Using COM. DataAccess;
Namespace Consoleappinserttest
{
Class Program
{
static string connectionString = Sqlhelper.connectionstringlocaltransaction; Database connection string
static int count = 1000000; Number of bars inserted
static void Main (string[] args)
{
Long commoninsertruntime = Commoninsert ();
Console.WriteLine (String. Format (the time spent in the normal way to insert {1} data is {0} milliseconds, Commoninsertruntime, count));
Long sqlbulkcopyinsertruntime = Sqlbulkcopyinsert ();
Console.WriteLine (String. Format ("The time used to insert {1} data using SqlBulkCopy is {0} milliseconds", Sqlbulkcopyinsertruntime, Count));
Long tvpinsertruntime = Tvpinsert ();
Console.WriteLine (String. Format ("The time used to insert {1} Data using table-valued method (TVP) is {0} milliseconds", Tvpinsertruntime, Count));
}
<summary>
Normal call stored procedure Insert data
</summary>
<returns></returns>
private static long Commoninsert ()
{
Stopwatch stopwatch = new stopwatch ();
Stopwatch. Start ();
String Passportkey;
for (int i = 0; i < count; i++)
{
Passportkey = Guid.NewGuid (). ToString ();
Sqlparameter[] SqlParameter = {new SqlParameter ("@passport", Passportkey)};
Sqlhelper.executenonquery (connectionString, CommandType.StoredProcedure, "Createpassport", SqlParameter);
}
Stopwatch. Stop ();
Return stopwatch. Elaps tutorial Edmilliseconds;
}
<summary>
Inserting data using the SqlBulkCopy method
</summary>
<param name= "DataTable" ></param>
<returns></returns>
private static long Sqlbulkcopyinsert ()
{
Stopwatch stopwatch = new stopwatch ();
Stopwatch. Start ();
DataTable datatable = GetTableSchema ();
String Passportkey;
for (int i = 0; i < count; i++)
{
Passportkey = Guid.NewGuid (). ToString ();
DataRow DataRow = Datatable.newrow ();
Datarow[0] = Passportkey;
DATATABLE.ROWS.ADD (DataRow);
}
SqlBulkCopy sqlbulkcopy = new SqlBulkCopy (connectionString);
Sqlbulkcopy.destinationtablename = "Passport";
Sqlbulkcopy.batchsize = DataTable.Rows.Count;
SqlConnection SqlConnection = new SqlConnection (connectionString);
Sqlconnection.open ();
if (datatable!=null && datatable.rows.count!=0)
{
Sqlbulkcopy.writetoserver (dataTable);
}
Sqlbulkcopy.close ();
Sqlconnection.close ();
Stopwatch. Stop ();
Return stopwatch. Elapsedmilliseconds;
}
private static long Tvpinsert ()
{
Stopwatch stopwatch = new stopwatch ();
Stopwatch. Start ();
DataTable datatable = GetTableSchema ();
String Passportkey;
for (int i = 0; i < count; i++)
{
Passportkey = Guid.NewGuid (). ToString ();
DataRow DataRow = Datatable.newrow ();
Datarow[0] = Passportkey;
DATATABLE.ROWS.ADD (DataRow);
}
Sqlparameter[] SqlParameter = {new SqlParameter ("@TVP", DataTable)};
Sqlhelper.executenonquery (connectionString, CommandType.StoredProcedure, "CREATEPASSPORTWITHTVP", SqlParameter);
Stopwatch. Stop ();
Return stopwatch. Elapsedmilliseconds;
}
private static DataTable GetTableSchema ()
{
DataTable datatable = new DataTable ();
DataTable.Columns.AddRange (new datacolumn[] {new DataColumn ("Passportkey")});
return dataTable;
}
}
}

The more cryptic code is actually the following two lines, which pass a DataTable as a parameter to our stored procedure. It's easy.

Sqlparameter[] SqlParameter = {new SqlParameter ("@TVP", DataTable)};
Sqlhelper.executenonquery (connectionString, CommandType.StoredProcedure, "CREATEPASSPORTWITHTVP", SqlParameter); 5. Test and record the test results of the first set of tests, insert record number 1000 second group test, insert record number 10000 Third group test, insert record number 1000000 pass above test plan, it is not difficult to find that the advantage of technical scheme two is quite high. Whether in terms of versatility or performance, it should be
Priority is chosen, and one point, its technical complexity is more simple than technical solution three,
Imagine that we have all the tables created a table value type, the workload is still available. So I still hold on to my decision at the beginning,
Recommend the use of a second technical solution to the company.