SQL Server batch data insertion SQL comparison with performance

The OperatorInfo table contains [OPERATOR_NO], [OPERATOR_PWD], [OPERATOR_NAME], [GROUP_ID], [GROUP_NO], [SKILL_LEVEL], [OPERATOR_TYPE], [PAUSENUM], [OPERATOR_ACD_TYPE]

[OPERATOR_NO] must specify a starting value and accumulate the value. Others are default values, so ignore them.

The following code is used:

The code is as follows:

Copy code

DECLARE @ recordNmber int DECLARE @ OPERATOR_NO int   SET @ recordNmber = 0 SET @ OPERATOR_NO = 6990 -- the starting employee ID of the agent, for example, 6990, which is inserted from 6991   WHILE (@ recordNmber) <$30 -- number of agents to be added BEGIN SET @ recordNmber = @ recordNmber + 1 SET @ OPERATOR_NO = @ OPERATOR_NO + 1 Insert into [OperatorInfo] ([OPERATOR_NO], [OPERATOR_PWD], [OPERATOR_NAME], [GROUP_ID], [GROUP_NO], [SKILL_LEVEL], [OPERATOR_TYPE], [PAUSENUM], [OPERATOR_ACD_TYPE]) VALUES (@ OPERATOR_NO, '000000', @ OPERATOR_NO, '000000', '000000', 1, '1', 1, '1 ') END

I only added 30 entries here. If you need them, you can customize them.

Performance Comparison

 

. Create table value parameter type

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

The code is as follows:	Copy code
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, we can see the type of the created table value, as shown in the following figure:

 

The table value type is successfully created.

3. Write the stored procedure

The stored procedure code is:

The code is as follows:

Copy code

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 a pass> -- ===================================================== ====== Create PROCEDURE [dbo]. [CreatePassportWithTVP] @ TVP PassportTableType readonly AS BEGIN Set nocount on; Insert into Passport (PassportKey) select PassportKey from @ TVP END

In the query analyzer, you may be prompted that the table value type is incorrect, and there will be a red underline (see the figure below). Ignore this and continue running our code, create a stored procedure 4. write code to call the stored procedure.

The code for inserting the three databases is as follows. Due to the tight time, the code may be less readable. I added some comments to the code.

Main part of the code

The code is as follows:

Copy 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 items Static void Main (string [] args)         { // Long commonInsertRunTime = CommonInsert (); // Console. WriteLine (string. Format ("the time used to insert {1} pieces of data in normal mode is {0} millisecond", commonInsertRunTime, count )); Long sqlBulkCopyInsertRunTime = SqlBulkCopyInsert (); Console. WriteLine (string. Format ("the time used to insert {1} pieces of data using SqlBulkCopy is {0} millisecond", sqlBulkCopyInsertRunTime, count )); Long TVPInsertRunTime = TVPInsert (); Console. WriteLine (string. Format ("the time used to insert {1} pieces of data using the table value method (TVP) is {0} millisecond", TVPInsertRunTime, count ));         } /// <Summary> /// Insert data in a stored procedure /// </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> /// Use SqlBulkCopy to insert data /// </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 mysterious code is actually the following two lines. The code passes a dataTable as a parameter to our stored procedure. Simple.

The code is as follows:	Copy code
SqlParameter [] sqlParameter = {new SqlParameter ("@ TVP", dataTable )}; SqlHelper. ExecuteNonQuery (connectionString, CommandType. StoredProcedure, "CreatePassportWithTVP", sqlParameter );

5. Test and record test results

1. Create a table.

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

The first group of tests, with 1000 inserted records

Test Group 2, with 10000 inserted records

In the third group of tests, the number of inserted records is 1000000.

Through the above test scheme, it is not difficult to find that the advantage of technical solution 2 is still quite high. Whether in terms of versatility or performance, it should be
The technical complexity is more complex than the technical solution three.