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

表值参数(Table-valued Parameter)是SQL Server 2008增加的新特性，可以将DataTable做为参数传递给存储过程。

 

数据库执行脚本如下

CREATE TYPE TestType AS TABLE
(
 Id int NOT NULL
 ,Name nvarchar(20) NOT NULL
)

CREATE PROC InsertData
 @rows TestType READONLY
as
begin
 set nocount on
 insert into TestTable(Id, Name) 
 select Id, Name from @rows
end

 

 

代码如下：

[c-sharp]  view plain copy

1. #region 使用表值参数  
2.         public static bool ExecuteTableTypeInsert(DataTable dt, int batchSize)  
3.         {  
4.             int count = dt.Rows.Count;  
5.             bool flag = false;  
6.             SqlConnection cn = null;  
7.             SqlCommand cmd = null;  
8.             DataTable tempTable = Tools.MakeDataTable();  
9.             DataRow row = null;  
10.             try  
11.             {  
12.                 cn = new SqlConnection(connectionString);  
13.                 cmd = new SqlCommand();  
14.                 cmd.Connection = cn;  
15.                 cmd.CommandType = CommandType.StoredProcedure;  
16.                 cmd.CommandText = "InsertData";  
17.   
18.                 cn.Open();  
19.                 for (int i = 0; i < count; i += batchSize)  
20.                 {  
21.                     for (int j = i; j < i + batchSize && j < count; j++)  
22.                     {  
23.                         row = tempTable.NewRow();  
24.                         row["Id"] = dt.Rows[j]["Id"];  
25.                         row["Name"] = dt.Rows[j]["Name"];  
26.                         tempTable.Rows.Add(row);  
27.                     }  
28.                     SqlParameter param = cmd.Parameters.AddWithValue("@rows", tempTable);  
29.                     param.SqlDbType = SqlDbType.Structured;  
30.                     param.TypeName = "TestType";  
31.                     cmd.ExecuteNonQuery();  
32.                     tempTable.Clear();  
33.                     cmd.Parameters.Clear();  
34.                 }  
35.                 flag = true;  
36.             }  
37.             catch (Exception ex)  
38.             {  
39.                 LogHelper.Error(ex.Message);  
40.                 return false;  
41.             }  
42.             finally  
43.             {  
44.                 if (cn != null)  
45.                 {  
46.                     if (cn.State == ConnectionState.Open)  
47.                     {  
48.                         cn.Close();  
49.                     }  
50.                     cn.Dispose();  
51.                 }  
52.                 if (cmd != null) cmd.Dispose();  
53.             }  
54.             return flag;  
55.         }  
56.         #endregion  

 

结果如下：

Use SqlServer TableType Insert;RecordCount:40000;BatchSize:10;Time:15312;

Use SqlServer TableType Insert;RecordCount:40000;BatchSize:20;Time:7806;

Use SqlServer TableType Insert;RecordCount:40000;BatchSize:50;Time:3767;

Use SqlServer TableType Insert;RecordCount:40000;BatchSize:100;Time:2217;

Use SqlServer TableType Insert;RecordCount:40000;BatchSize:200;Time:1743;

Use SqlServer TableType Insert;RecordCount:40000;BatchSize:400;Time:1575;

Use SqlServer TableType Insert;RecordCount:40000;BatchSize:500;Time:1566;

Use SqlServer TableType Insert;RecordCount:40000;BatchSize:600;Time:1374;

Use SqlServer TableType Insert;RecordCount:40000;BatchSize:700;Time:1286;

Use SqlServer TableType Insert;RecordCount:40000;BatchSize:800;Time:1463;

Use SqlServer TableType Insert;RecordCount:40000;BatchSize:1000;Time:1272;

Use SqlServer TableType Insert;RecordCount:40000;BatchSize:2000;Time:1069;

Use SqlServer TableType Insert;RecordCount:40000;BatchSize:4000;Time:1001;

 

从时间上来看，似乎并不必前面的案例强，但批处理量得增加，写性能在持续提高，而且实际上程序中花费了大量的时间在创建DataTable及填充其数据上面，如果传递给函数的就是一个DataTable集合，相信使用表值参数的表现会更好。

 

但考虑到需要为插入的表创建类型，创建存储过程，个人认为其通用性不是很好

 

全文链接:

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

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

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

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

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

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