Performance Comparison of batch data insertion in SQLServer

When I got off work yesterday afternoon, I accidentally heard two colleagues in the company discuss the performance optimization problem of batch data insertion into the database. I suddenly came to an interest and told them my thoughts, so with this article.

Company Technical Background: DataBase Audit class (xxx. DataBase. Dll) calls the stored procedure to access the DataBase.

Technical solution 1:

The first version written by the programmer during the compression time is used to complete the task without optimization from the program. The implementation method is to use the database metadata class to call the stored procedure and insert data one by one in a loop. Obviously, this method is not very efficient, so we have two colleagues discussing the problem of low efficiency.

Technical solution 2:

Considering the large data volume of batch inserts, I came up with a new feature of ADO. NET2.0: SqlBulkCopy. For this performance, I personally tested the performance a long time ago and the efficiency was very high. This is also the technical solution I recommended to my colleagues.

Technical solution 3:

Use the new feature of SQLServer2008-Table-Valued Parameter ). The table value parameter is a new feature only available in SQLServer2008. With this feature, we can pass a table type as a parameter to a function or stored procedure. However, it also has a feature: Table value parameters have good execution performance when the number of inserted rows is less than 1000.

Technical solution 4:

For single-column fields, you can splice the data to be inserted into strings, split the data into arrays during storage, and insert the data one by one. Check the maximum length of the parameter string in the stored procedure, and divide it by the length of the field to calculate a value, which obviously meets the requirements, however, compared with the first method, this method does not seem to improve because the principles are the same.

Technical solution 5:

Consider asynchronous creation and message queue. This solution is difficult both in design and development.

Technical solution 1 must be rejected. The rest is to make a choice between technical solution 2 and technical solution 3. Given the current situation of the company, technical solution 4 and technical solution 5 will not be considered first.

Next, I will write more details about the creation and calling of Table value parameters, and the article may be a little longer, friends who do not pay attention to the details can choose a skip reading method.

Let's talk about the test scheme. There are three groups in the test. One group has less than 1000 inserts, and the other two groups have more than 1000 inserts (Here we take 10000 and 1000000 respectively ), each group of tests is divided into 10 times and the average value is obtained. Let's go!
 

1. Create a table.

For simplicity, the table has only one field, as shown in:

2. Create a table value parameter type

Open the query analyzer and execute the following code in the query Analyzer:

Create Type PassportTableType as Table(PassportKey nvarchar(50)
)

After the execution is successful, open the Enterprise Manager and expand the following nodes in sequence: database, scalability, type, and user-defined table type, you can see the Table value types we have created, as shown in:

The table value type is successfully created.

3. Write the Stored Procedure

The Stored Procedure Code is:

USE [TestInsert]
GO/***** Object: StoredProcedure [dbo]. [CreatePassportWithTVP] Script Date: 03/02/2010 00:14:45 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO -- ================ =================================-- Author: <Kevin> -- Create date: <2010-3-1> -- Description: <create a pass> -- ======================================== =========== Create PROCEDURE [dbo]. [CreatePassportWithTVP]
@ TVP PassportTableType readonly
Asbeginset nocount on;
Insert asp? So = RECCNT & siteid = us % 2 Fdev & p = 1 & nq = NEW & qu = into & IntlSearch = & boolean = PHRASE & ig = 01