昨天下午快下班的时候,无意中听到公司两位同事在探讨批量向数据库插入数据的性能优化问题,顿时来了兴趣,把自己的想法向两位同事说了一下,于是有了本文。 公司技术背景:数据库访问类(xxx.DataBase.Dll)调用存储过程实现数据库的访问。 技术方案一: 压
昨天下午快下班的时候,无意中听到公司两位同事在探讨批量向数据库插入数据的性能优化问题,顿时来了兴趣,把自己的想法向两位同事说了一下,于是有了本文。1.创建表。
为了简单,表中只有一个字段,如下图所示:
2.创建表值参数类型
我们打开查询分析器,然后在查询分析器中执行下列代码:
Create Type PassportTableType as Table ( PassportKey nvarchar(50)<br> )
执行成功以后,我们打开企业管理器,按顺序依次展开下列节点--数据库、展开可编程性、类型、用户自定义表类型,就可以看到我们创建好的表值类型了如下图所示:
说明我们创建表值类型成功了。
3.编写存储过程
存储过程的代码为:
USE [TestInsert]<br> GO <span>/****** Object: StoredProcedure [dbo].[CreatePassportWithTVP] Script Date: 03/02/2010 00:14:45 ******/</span> SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO <span>-- =============================================</span> <span>-- Author: <kevin></kevin></span> <span>-- Create date: </span> <span>-- Description: </span> <span>-- =============================================</span> Create PROCEDURE [dbo].[CreatePassportWithTVP] <br> @TVP PassportTableType readonly<br> AS BEGIN SET NOCOUNT ON;<br> Insert into Passport(PassportKey) select PassportKey from @TVP<br> END
<pre class="brush:php;toolbar:false">可能在查询分析器中,智能提示会提示表值类型有问题,会出现红色下划线(见下图),不用理会,<br>继续运行我们的代码,完成存储过程的创建
<img src="/inc/test.jsp?url=http%3A%2F%2Fimages.cnblogs.com%2Fcnblogs_com%2Fwlb%2F2010-3-2-3.jpg&refer=http%3A%2F%2Fwww.cnblogs.com%2Fwlb%2Farchive%2F2010%2F03%2F02%2F1676136.html" alt="SQLServer中批量插入数据方式的性能对比" >
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();
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;
}
}
}
比较神秘的代码其实就下面这两行,该代码是将一个dataTable做为参数传给了我们的存储过程。简单吧。
SqlParameter[] sqlParameter = { <span>new</span> SqlParameter("<span>@TVP</span>", dataTable) };<br> SqlHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, "<span>CreatePassportWithTVP</span>", sqlParameter);
5.测试并记录测试结果
第一组测试,插入记录数1000<br><img src="/inc/test.jsp?url=http%3A%2F%2Fimages.cnblogs.com%2Fcnblogs_com%2Fwlb%2F2010-3-2-4.jpg&refer=http%3A%2F%2Fwww.cnblogs.com%2Fwlb%2Farchive%2F2010%2F03%2F02%2F1676136.html" alt="SQLServer中批量插入数据方式的性能对比" ><br>
第二组测试,插入记录数10000<br><img src="/inc/test.jsp?url=http%3A%2F%2Fimages.cnblogs.com%2Fcnblogs_com%2Fwlb%2F2010-3-2-5.jpg&refer=http%3A%2F%2Fwww.cnblogs.com%2Fwlb%2Farchive%2F2010%2F03%2F02%2F1676136.html" alt="SQLServer中批量插入数据方式的性能对比" ><br>
第三组测试,插入记录数1000000<br><img src="/inc/test.jsp?url=http%3A%2F%2Fimages.cnblogs.com%2Fcnblogs_com%2Fwlb%2F2010-3-2-6.jpg&refer=http%3A%2F%2Fwww.cnblogs.com%2Fwlb%2Farchive%2F2010%2F03%2F02%2F1676136.html" alt="SQLServer中批量插入数据方式的性能对比" ><br>通过以上测试方案,不难发现,技术方案二的优势还是蛮高的。无论是从通用性还是从性能上考虑,都应该是
代码下载