快捷搜索:

SQLServer中批量插入数据方式的性能对比

昨世界午快放工的时刻,无意入耳到公司两位同事在探究批量向数据库插入数据的机能优化问题,立时来了兴趣,把自己的设法主见向两位同事说了一下,于是有了本文。

公司技巧背景:数据库造访类(xxx.DataBase.Dll)调用存储历程实现数据库的造访。

技巧规整洁:

压缩光阴下法度榜样员写出的第一个版本,仅仅为了完成义务,没有从法度榜样上做任何优化,实现要领是使用数据库造访类调用存储历程,使用轮回逐条插入。很显着,这种要领效率并不高,于是有了前面的两位同事评论争论效率低的问题。

技巧规划二:

因为是斟酌到大年夜数据量的批量插入,于是我想到了ADO.NET2.0的一个新的特点:SqlBulkCopy。有关这个的机能,很早之前我是亲身做过机能测试的,效率异常高。这也是我向公司同事保举的技巧规划。

技巧规划三:

使用SQLServer2008的新特点--表值参数(Table-Valued Parameter)。表值参数是SQLServer2008才有的一个新特点,应用这个新特点,我们可以把一个表类型作为参数通报到函数或存储历程里。不过,它也有一个特征:表值参数在插入数目少于 1000 的行时具有很好的履行机能。

技巧规划四:

对付单列字段,可以把要插入的数据进行字符串拼接,着末再在存储历程中拆分成数组,然后逐条插入。查了一下存储历程中参数的字符串的最大年夜长度,然后除以字段的长度,算出一个值,很显着是可以满意要求的,只是这种要领跟第一种要领比起来,彷佛没什么前进,由于道理都是一样的。

技巧规划五:

斟酌异步创建、消息行列步队等等。这种规划无论从设计上照样开拓上,难度都是有的。

技巧规整洁肯定是要被否掉落的了,剩下的便是在技巧规划二跟技巧规划三之间做一个决定,鉴于公司今朝的环境,技巧规划四跟技巧规划五就先不斟酌了。

接下来,为了让大年夜家对表值参数的创建跟调用有更感性的熟识,我将写的更具体些,文章可能也会稍长些,不关注细节的同伙们可以选择跳跃式的涉猎要领。

再说一下测试规划吧,测试统共分三组,一组是插入数量小于1000的,别的两组是插入数据量大年夜于1000的(这里我们分手取10000跟1000000),每组测试又分10次,取匀称值。怎么做都明白了,Let’s go!

1.创建表。

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

2.创建表值参数类型

我们打开查询阐发器,然后在查询阐发器中履行下列代码:

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:

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

}

}

}

您可能还会对下面的文章感兴趣: