Company Technical Background: Database access class (XXX. DATABASE.DLL) invokes a stored procedure to implement access to the database.
Technical programme I:
The first version written by the programmer in the compression time, just to complete the task, did not do any optimization from the program, the implementation is to use the database access class to call the 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. Elapsedmilliseconds;
}
<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.
New SqlParameter ("@TVP", dataTable)};
Sqlhelper.executenonquery (connectionString, CommandType.StoredProcedure, "CREATEPASSPORTWITHTVP", SqlParameter);
5. Test and record test results
First set of tests, insert record number 1000
Second set of tests, insert record number 10000
Third group of tests, insert record number 1000000
Through the above test scenario, it is not difficult to find that the technical solution two advantages are quite high. Whether from versatility or performance considerations, it should be
prioritized, and one point, its technical complexity is much simpler than technical solution three,
Imagine that we've created all tables with table value types, and the workload is still there. So I'm still sticking to my decision to start with,
recommend the second technology to the company.
This is the end of this article, but the study of new technology is still continuing. There are still a lot of things to do.
to facilitate learning and communication, code files have been packaged and uploaded, and welcome to study together.
Code Download
Author: Mountains and forests
Source: http://wlb.cnblogs.com/
