Shulou(Shulou.com)06/02 Report--

This article mainly talks about "what are the ways of SQLServer inserting data in bulk". Interested friends may wish to take a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "what are the ways of SQLServer inserting data in bulk?"

Technical proposal 1:

The first version written by the programmer under the compression time is only to complete the task without any optimization from the program, which is realized by calling the stored procedure using the database access class and inserting it one by one using the loop. Obviously, this approach is not efficient, so the previous two colleagues discussed the problem of inefficiency.

Technical proposal II:

Considering the bulk insertion of a large amount of data, I thought of a new feature of ADO.NET2.0: SqlBulkCopy. With regard to the performance of this, I personally did a performance test a long time ago, and it is very efficient. This is also the technical solution that I recommend to my colleagues in the company.

Technical proposal III:

Take advantage of the new feature of SQLServer2008-table-valued parameter (Table-Valued Parameter). Table-valued parameters are a new feature of SQLServer2008. With this new feature, we can pass a table type as an argument to a function or stored procedure. However, it also has one feature: table-valued parameters perform well when inserting fewer than 1000 rows.

Technical proposal 4:

For single-column fields, you can concatenate the data to be inserted, split it into an array in the stored procedure, and then insert it one by one. Check the maximum length of the string of parameters in the stored procedure, then divide by the length of the field, and calculate a value, which obviously meets the requirements, but this way does not seem to improve compared with the first way, because the principle is the same.

Technical proposal 5:

Consider asynchronous creation, message queuing, and so on. No matter in terms of design or development, this kind of scheme is difficult.

Technical solution 1 is definitely going to be dropped, and the rest is to make a choice between technical solution 2 and technical solution 3. In view of the current situation of the company, technical solution 4 and technical solution 5 will not be considered.

Next, in order to give you a more perceptual understanding of the creation and invocation of table-valued parameters, I will write in more detail, the article may also be a little longer, friends who do not pay attention to details can choose to skip the way of reading.

Let's talk about the test plan again. the test is divided into three groups, one with less than 1000 inserts and the other two with more than 1000 inserts (here we take 10000 and 1000000 respectively). Each group is divided into 10 tests, taking the average. I know what to do, Let's go!

1. Create a table.

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

two。 Create a table-valued parameter type

We 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, we open the Enterprise Manager and expand the following nodes in order-- database, expand programmability, type, and user-defined table type. We can see that the table value type we created is as shown in the following figure:

It means that we have successfully created the table value type.

3. Write stored procedures

The code for the stored procedure is:

USE [TestInsert] GO/* Object: StoredProcedure [dbo]. [CreatePassportWithTVP] Script Date: 03Create date 02 TestInsert 00:14:45 * / SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =-- Author:-- Create date:-- Description:-- = Create PROCEDURE [dbo]. [CreatePassportWithTVP] @ TVP PassportTableType readonlyASBEGINSET NOCOUNT ON;Insert into Passport (PassportKey) select PassportKey from @ TVPEND

Perhaps in the query analyzer, the smart prompt will indicate that there is a problem with the table value type and there will be a red underscore (see figure below). Ignore it and continue to run our code to complete the creation of the stored procedure.

4. Write code to call the stored procedure.

The code for the insertion of the three databases is as follows. Due to the tight time, the code may not be easy to read. I added some comments to the special code.

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 inserted static void Main (string [] args) {/ / long commonInsertRunTime = CommonInsert () / / Console.WriteLine (string.Format ("normal time to insert {1} pieces of data is {0} milliseconds", commonInsertRunTime, count); long sqlBulkCopyInsertRunTime = SqlBulkCopyInsert (); Console.WriteLine ("time to insert {1} pieces of data using SqlBulkCopy is {0} milliseconds", sqlBulkCopyInsertRunTime, count); long TVPInsertRunTime = TVPInsert () Console.WriteLine (string.Format ("the time it takes to insert {1} pieces of data using table-valued mode (TVP) is {0} milliseconds", TVPInsertRunTime, count);} / ordinary call stored procedure inserts data / private static long CommonInsert () {Stopwatch stopwatch = new Stopwatch () Stopwatch.Start (); string passportKey; for (int I = 0; I < count; iTunes +) {passportKey = Guid.NewGuid () .ToString (); SqlParameter [] sqlParameter = {new SqlParameter ("@ passport", passportKey)}; SqlHelper.ExecuteNonQuery (connectionString, CommandType.StoredProcedure, "CreatePassport", sqlParameter) } stopwatch.Stop (); return stopwatch.ElapsedMilliseconds;} / insert data in SqlBulkCopy mode / private static long SqlBulkCopyInsert () {Stopwatch stopwatch = new Stopwatch (); stopwatch.Start () DataTable dataTable = GetTableSchema (); string passportKey; for (int I = 0; I < count; iTunes +) {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.Rows.CountForm0) {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; iTunes +) {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 mysterious code is actually the following two lines, which is the stored procedure that passes a dataTable as an argument to our stored procedure. It's easy.

SqlParameter [] sqlParameter = {new SqlParameter ("@ TVP", dataTable)}; SqlHelper.ExecuteNonQuery (connectionString, CommandType.StoredProcedure, "CreatePassportWithTVP", sqlParameter); 5. Test and record test results

The first set of tests, insert records 1000

The second set of tests, insert records 10000

The third set of tests, insert records 1000000

Through the above test scheme, it is not difficult to find that the advantage of technical solution 2 is quite high. No matter in terms of versatility or performance, it should be preferred, and its technical complexity is a little simpler than that of technical solution 3. Imagine that we create table value types once for all tables, and there is still some workload. Therefore, I still stick to my initial decision to recommend the second technical solution to the company.

At this point, I believe you have a deeper understanding of "what are the ways in which SQLServer inserts data in bulk?" you might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.