近来有一个项目Feature需要有批量写入数据的场景，正巧整理资料发现自己以前也类似实现的项目，在重构的同时把相关资料做了一个简单的梳理，方便大家参考。
在SQL Server 2008未提供表值参数之前，需要将多行数据传递到存储过程或参数化sql命令我们一般会采用以下几个方法：
方案一
作为早期学习时出镜率最高的的实现方法我在这里就不特别说明了，在这里直接上码及测试数据：
public static void NormalInsertDate(DataTable dt) { using (var sqlConn = new SqlConnection(_testDataConnectionString)) { var sql = "INSERT INTO Student(Name,Age) VALUES(@Name,@Age)"; using (var cmd = new SqlCommand(sql, sqlConn)) { sqlConn.Open(); cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 50); cmd.Parameters.Add("@Age", SqlDbType.Int); for (int i = 0; i < dt.Rows.Count; i++) { cmd.Parameters["@Name"].Value = dt.Rows[i]["Name"]; cmd.Parameters["@Age"].Value = dt.Rows[i]["Age"]; cmd.ExecuteNonQuery(); } } } }
View Code
图一为每次10k条，写10次共计100k条数据总计15329ms
图二为每次100k条，写10次共计1000k条数据总计184395ms
方案二
作为早期批量写入的救星，批量写入的出镜指数4颗星。以下为测试数据：
public static void BulkInsertData(DataTable dt) { using (var sqlConn = new SqlConnection(_testDataConnectionString)) { using (var bulkCopy = new SqlBulkCopy(sqlConn) { DestinationTableName = "Student", BatchSize = dt.Rows.Count }) { sqlConn.Open(); bulkCopy.WriteToServer(dt); } } }
View Code
图一为每次10k条，写10次共计100k条数据总计1848ms
图二为每次100k条，写10次共计1000k条数据总计21584ms
方案三表值参数方式写入
表值参数提供一种将客户端应用程序中的多行数据封送到 SQL Server 的简单方式，而不需要多次往返或特殊服务器端逻辑来处理数据。您可以使用表值参数来包装客户端应用程序中的数据行，并使用单个参数化命令将数据发送到服务器。传入的数据行存储在一个表变量中，然后您可以通过使用 Transact-SQL 对该表变量进行操作。
可以使用标准的 Transact-SQL SELECT 语句来访问表值参数中的列值。表值参数为强类型，其结构会自动进行验证。表值参数的大小仅受服务器内存的限制。
注意：表值参数只能是输入参数，不能作为输出参数。
以下为相关实现：
1.创建表值参数类型(UDT)
USE Test --CREATE TABLE CREATE TABLE Student ( Id INT IDENTITY(1,1) PRIMARY KEY, Name NVARCHAR(50), Age INT ) --create table parameter type CREATE TYPE StudentUDT AS TABLE ( Name NVARCHAR(50), Age INT )
View Code
public static void TableParameterInsertData(DataTable dt) { using (var sqlConn = new SqlConnection(_testDataConnectionString)) { var sql = "INSERT INTO Student(Name,Age) SELECT Name, Age FROM @StudentTVPS";//在这里直接访问表值参数 using (var cmd = new SqlCommand(sql, sqlConn)) { var catParam = cmd.Parameters.AddWithValue("@StudentTVPS", dt); catParam.SqlDbType = SqlDbType.Structured; catParam.TypeName = "StudentUDT";//我们自定义的表值参数类型名称 sqlConn.Open(); cmd.ExecuteNonQuery(); } } }
View Code
图一为每次10k条，写10次共计100k条数据总计390ms
图二为每次100k条，写10次共计1000k条数据总计4451ms
最后我们再横向比较一下：
就我本机测试的情况来看，normal=9*bulk=42*tvps
另外我就一次性大量数据写入对bulk和tvps单独进行了测试，一次性写入100K条数据两种方案基本持平490ms
但在一次性写入1000K条数据时差距又再次被拉开,bulk=1.5tvps
就测试数据表明bulk在一次性大量写入依然有不小的优势，毕竟ms就是专门让他来做这个事情的。
然而也可以通过tvps进行分范围写入的方式，总消耗时间有小幅度改善。
所有呢，有大量数据一次性写入场景直接使用bulk copy方式吧。他当仁不让可以高效完成使命。
如果就一些普通业务批量场景无需考虑直接上TVPS方式。他的效率相对于较之前xml参数，复杂参数实现批量写入已经是数量级的提升。
你绝对值得拥有。
由于客户端硬件环境原因，测试环境应该不能非常精确。所以以上数据仅供参考。
欢迎大家一起分享交流。
附件本机测试硬件环境:i7 4770+128 ssd+8G内存；
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。