SqlBulkCopy加了事务真的会变快吗?

在上一篇文章不同版本的SQL Server之间数据导出导入的方法及性能比较中比较了5种方法进行数据迁移的效率,最后发现使用SqlBulkCopy具有极高的性能。

在文章的回复中,园友温景良(Jason)提到:sqlbulkcopy如果加上事务呢,速度应该快些吧!

当时我一想,SqlBulkCopy可以包含在事务里面执行吗?因为我记得SqlBulkCopy是基于BCP且不写log的。所以也没敢仓促回复。

然后我去查了下,的确是可以包含在事务中执行的,且提供了两种方法。

使用UseInternalTransaction

在构造函数SqlBulkCopy(String, SqlBulkCopyOptions)里面有SqlBulkCopyOptions的选项,有如下可选项。

我们看到可选项分别有保持Identity键,检查约束,是否锁表,保持null值,触发触发器,使用事务。

所以如果只是想把SqlBulkCopy包含在事务中,只要打开此属性即可。

使用System.Data.SqlClient.SqlTransaction

使用自定义的事务,将SqlBulkCopy和其他操作一起包含在一个事务中。

这种方法只要使用另一个构造函数即可:SqlBulkCopy(SqlConnection, SqlBulkCopyOptions, SqlTransaction)。

需要注意的是:当打开了UseInternalTransaction选项后,就不可以和自定义事务一起使用了,否则会抛出InvalidArgumentException。

事务影响性能?

下面是使用第一种开启事务的测试代码,没有使用事务的代码参见前一篇文章

使用UseInternalTransaction的代码。

 1 using System;
 2 using System.Data;
 3 using System.Data.SqlClient;
 4 
 5 namespace BulkInsert
 6 {
 7     static class Program
 8     {
 9         static void Main()
10         {
11             DateTime dateTimeStart = DateTime.Now;
12             Console.WriteLine("Start Insert:" + dateTimeStart.ToString("HH:mm:ss fff"));
13             //导入导出的数据库连接
14             //SqlConnection connectionDestination = new SqlConnection();
15             SqlConnection connectionSource = new SqlConnection("Server =.; User ID=sa; Password=password; Initial CataLog=ExportDataDemo_Source;");
16 
17             //实例化一个SqlBulkCopy
18             var bulker = new SqlBulkCopy("Server =.; User ID=sa; Password=password; Initial CataLog=ExportDataDemo_Destination;", SqlBulkCopyOptions.UseInternalTransaction) { DestinationTableName = "DEMOTABLE", BulkCopyTimeout = 600 };
19 
20             //获取源数据库的数据
21             SqlCommand sqlcmd = new SqlCommand("SELECT * FROM DEMOTABLE", connectionSource);
22             SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlcmd);
23             DataTable dataTableSource = new DataTable();
24             sqlDataAdapter.Fill(dataTableSource);
25 
26             //可以重新定义字段的Mapping关系
27             //SqlBulkCopyColumnMapping sqlBulkCopyColumnMapping = new SqlBulkCopyColumnMapping("COL1", "NEW_COL1");
28             //bulker.ColumnMappings.Add(sqlBulkCopyColumnMapping);
29             //connectionDestination.Open();
30             bulker.WriteToServer(dataTableSource);
31             bulker.Close();
32             DateTime dateTimeEnd = DateTime.Now;
33             Console.WriteLine("Insert Ending:" + dateTimeEnd.ToString("HH:mm:ss fff"));
34         }
35     }
36 } 

Source表同样还是10万的数据,每迁移一次后使用TRUNCATE TABLE清空数据,测试三次取平均值。

截图如下。

A:不使用事务

B:使用事务

最终对比数据。单位为秒,忘记加在图表上面了 :)

特定的测试环境与数据,测试结果仅供参考,欢迎交流、讨论。

posted @ 2012-09-11 15:45 Parry 阅读(...) 评论(...) 编辑 收藏