在做大批量数据插入的时候,如果用Insert into ... values (...)这种方式的话效率极低,这里介绍两种性能比较好的批量插入方法。
1. 使用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;
}
使用SqlBulkCopy类进行数据插入其原理是采用了SQL Server的BCP协议进行数据的批量复制。这里我们先要建好一个DataTable(最好是通过DataAdapter来灌数据得到,因为这样出来的DataTable就已经有跟数据表相同的列定义,可以免去之后Mapping Column的步骤),把要插入的数据加进这个DataTable中,然后用SqlBulkCopy的实例来插入到数据库中。经过测试,SqlBulkCopy方法比直接用Sql语句插入数据的效率高出将近25倍。
2. 使用数据库中的Table类型变量实现数据插入
这种方法的前提是数据库中必须支持Table类型的变量。
Create Type PassportTableType as Table
(
PassportKey nvarchar(50)
)Create PROCEDURE [dbo].[CreatePassportWithTVP]
@TVP PassportTableType readonly
AS
BEGIN
SET NOCOUNT ON;
Insert into Passport(PassportKey) select PassportKey from @TVP
END
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;
}
这种方法实现起来有点儿复杂。首先, 在数据库里新建一个自定义类型,继承Table类型的,然后建立Stored Procedure,将刚建立的自定义类型作为参数传入。关键点在这里了:使用insert into ... select ... from ...这个语句来实现批量插入。最后就剩下在ADO.Net中调用这个存储过程,将DataTable作为参数传入就大功告成了。
PS. 可能有人要说,DataAdapter不是提供了一个Update的方法,可以实现批量Insert、Delete、Update 的么?但其实DataAdapter的这种做法跟逐条逐条sql语句输入数据库然后执行没什么区别,甚至更慢。还是上面提供的这两种方法可以从根本上提升批量插入的性能。