SQL Server批量插入数据sql与性能比较

作者：袖梨 2022-06-29

有这样一个表【OperatorInfo】，字段有[OPERATOR_NO]，[OPERATOR_PWD]，[OPERATOR_NAME]，[GROUP_ID]，[GROUP_NO]，[SKILL_LEVEL]，[OPERATOR_TYPE]，[PAUSENUM], [OPERATOR_ACD_TYPE]

其中[OPERATOR_NO]需要指定一个起始数值，然后实现规定的累加，其他的一些都是默认值，可以不用理会。

以下是代码：

代码如下

复制代码

DECLARE @recordNmber int
DECLARE @OPERATOR_NO int

SET @recordNmber   = 0
SET @OPERATOR_NO = 6990 --座席起始工号，例如6990，将从6991开始插入

WHILE (@recordNmber) < $30 --需要添加的座席数量
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,'123456',@OPERATOR_NO,'4204','4204',1,'1',1,'1')
END

这里我只加了30条，如果你需要，可以自行定义

性能对比

.创建表值参数类型

我们打开查询分析器，然后在查询分析器中执行下列代码：

代码如下	复制代码
Create Type PassportTableType as Table ( PassportKey nvarchar(50) )

执行成功以后，我们打开企业管理器，按顺序依次展开下列节点--数据库、展开可编程性、类型、用户自定义表类型，就可以看到我们创建好的表值类型了如下图所示：

说明我们创建表值类型成功了。

3.编写存储过程

存储过程的代码为：

代码如下

复制代码

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:
-- Create date: <2010-3-1>
-- Description: <创建通行证>
-- =============================================
Create PROCEDURE [dbo].[CreatePassportWithTVP]
@TVP PassportTableType readonly
AS
BEGIN
SET NOCOUNT ON;
Insert into Passport(PassportKey) select PassportKey from @TVP
END

可能在查询分析器中，智能提示会提示表值类型有问题，会出现红色下划线（见下图），不用理会，继续运行我们的代码，完成存储过程的创建 4.编写代码调用存储过程。

三种数据库的插入方式代码如下，由于时间比较紧，代码可能不那么易读，特别代码我加了些注释。

主要部分的代码

代码如下

复制代码

using System;
using System.Diagnostics;
using System.Data;
using System.Data.SqlClient;
using com.DataAccess;

namespace ConsoleAppInsertTest
{
    class Program
    {
        static string connectionString = SqlHelper.ConnectionStringLocalTransaction;    //数据库连接字符串
        static int count = 1000000;           //插入的条数
        static void Main(string[] args)
        {
            //long commonInsertRunTime = CommonInsert();
            //Console.WriteLine(string.Format("普通方式插入{1}条数据所用的时间是{0}毫秒", commonInsertRunTime, count));

long sqlBulkCopyInsertRunTime = SqlBulkCopyInsert();
Console.WriteLine(string.Format("使用SqlBulkCopy插入{1}条数据所用的时间是{0}毫秒", sqlBulkCopyInsertRunTime, count));

            long TVPInsertRunTime = TVPInsert();
            Console.WriteLine(string.Format("使用表值方式（TVP）插入{1}条数据所用的时间是{0}毫秒", TVPInsertRunTime, count));
        }

///

/// 普通调用存储过程插入数据
///

        ///
        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;
        }

///

/// 使用SqlBulkCopy方式插入数据
///

        ///
        ///
        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();

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;
        }

}
}

比较神秘的代码其实就下面这两行，该代码是将一个dataTable做为参数传给了我们的存储过程。简单吧。

代码如下	复制代码
SqlParameter[] sqlParameter = { new SqlParameter("@TVP", dataTable) }; SqlHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, "CreatePassportWithTVP", sqlParameter);

5.测试并记录测试结果

1.创建表。

为了简单，表中只有一个字段，如下图所示：

第一组测试，插入记录数1000

第二组测试，插入记录数10000

第三组测试，插入记录数1000000

通过以上测试方案，不难发现，技术方案二的优势还是蛮高的。无论是从通用性还是从性能上考虑，都应该是
优先被选择的，还有一点，它的技术复杂度要比技术方案三要简单一些

SQL Server批量插入数据sql与性能比较

相关文章

精彩推荐