.NET批量大数据插入性能分析及比较(5.使用SqlBulkCopy)

[c-sharp]  view plain copy

1. #region 使用SqlBulkCopy  
2.         public static bool ExecuteTransactionScopeInsert(DataTable dt, int batchSize)  
3.         {  
4.             int count = dt.Rows.Count;  
5.             string tableName = "TestTable";  
6.             int copyTimeout = 600;  
7.             bool flag = false;  
8.             try  
9.             {  
10.                 using (SqlConnection cn = new SqlConnection(connectionString))  
11.                 {  
12.                     using (TransactionScope scope = new TransactionScope())  
13.                     {  
14.                         cn.Open();  
15.                         using (SqlBulkCopy sbc = new SqlBulkCopy(cn))  
16.                         {  
17.                             //服务器上目标表的名称     
18.                             sbc.DestinationTableName = tableName;  
19.                             sbc.BatchSize = batchSize;  
20.                             sbc.BulkCopyTimeout = copyTimeout;  
21.                             for (int i = 0; i < dt.Columns.Count; i++)  
22.                             {  
23.                                 //列映射定义数据源中的列和目标表中的列之间的关系     
24.                                 sbc.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);  
25.                             }  
26.                             sbc.WriteToServer(dt);  
27.                             flag = true;  
28.                             scope.Complete();//有效的事务     
29.                         }  
30.                     }  
31.                 }  
32.             }  
33.             catch (Exception ex)  
34.             {  
35.                 LogHelper.Error(ex.Message);  
36.                 return false;  
37.             }  
38.             return flag;  
39.         }  
40.         #endregion  

 

结果如下:

Use SqlServer TransactionScope Insert;RecordCount:40000;BatchSize:10;Time:10314;

Use SqlServer TransactionScope Insert;RecordCount:40000;BatchSize:20;Time:4476;

Use SqlServer TransactionScope Insert;RecordCount:40000;BatchSize:50;Time:2021;

Use SqlServer TransactionScope Insert;RecordCount:40000;BatchSize:100;Time:1332;

Use SqlServer TransactionScope Insert;RecordCount:40000;BatchSize:200;Time:978;

Use SqlServer TransactionScope Insert;RecordCount:40000;BatchSize:400;Time:730;

Use SqlServer TransactionScope Insert;RecordCount:40000;BatchSize:500;Time:649;

Use SqlServer TransactionScope Insert;RecordCount:40000;BatchSize:600;Time:623;

Use SqlServer TransactionScope Insert;RecordCount:40000;BatchSize:700;Time:669;

Use SqlServer TransactionScope Insert;RecordCount:40000;BatchSize:800;Time:585;

Use SqlServer TransactionScope Insert;RecordCount:40000;BatchSize:1000;Time:681;

 

SqlBulkCopy原理是采用了SQL Server的BCP协议进行数据的批量复制，结合使用事务，就我们的案例而言，大约每批800条是平衡点，性能比逐条插入提高了100多倍，并前面同样使用事务批量插入的案例性能提升了7倍以上。

 

全文链接:

.NET批量大数据插入性能分析及比较(1.准备工作)

.NET批量大数据插入性能分析及比较(2.普通插入与拼接sql批量插入)

.NET批量大数据插入性能分析及比较(3.使用事务)

.NET批量大数据插入性能分析及比较(4.使用DataAdapter批量插入)

.NET批量大数据插入性能分析及比较(5.使用SqlBulkCopy)

.NET批量大数据插入性能分析及比较(6.使用表值参数)

bcp 实用工具可以在 Microsoft SQL Server 实例和用户指定格式的数据文件间大容量复制数据。 使用 bcp 实用工具可以将大量新行导入 SQL Server 表，或将表数据导出到数据文件。 除非与 queryout 选项一起使用，否则使用该实用工具不需要了解 Transact-SQL 知识。 若要将数据导入表中，必须使用为该表创建的格式文件，或者必须了解表的结构以及对于该表中的列有效的数据类型。

Microsoft SQL Server 提供一个称为 bcp 的流行的命令提示符实用工具，用于将数据从一个表移动到另一个表（表既可以在同一个服务器上，也可以在不同服务器上）。SqlBulkCopy 类允许编写提供类似功能的托管代码解决方案。还有其他将数据加载到 SQL Server 表的方法（例如 INSERT 语句），但相比之下 SqlBulkCopy 提供明显的性能优势。

使用 SqlBulkCopy 类只能向 SQL Server 表写入数据。但是，数据源不限于 SQL Server；可以使用任何数据源，只要数据可加载到 DataTable 实例或可使用 IDataReader 实例读取数据。