SQLServer 批量插入数据的两种方法

SQLServer 批量插入数据的两种方法  内容:在SQL Server 中插入一条数据使用Insert语句,但是如果想要批量插入一堆数据的话,循环使用Insert不仅效率低,而且会导致SQL一系统性能问题。下面介绍SQL Server支持的两种批量数据插入方法:Bulk和表值参数(Table-Valued Parameters)。

核心导读:SQLServer 批量插入数据的两种方法  内容:在SQL Server 中插入一条数据使用Insert语句,但是如果想要批量插入一堆数据的话,循环使用Insert不仅效率低,而且会导致SQL一系统性能问题。下面介绍SQL Server支持的两种批量数据插入方法:Bulk和表值参数(Table-Valued Parameters)。


运行下面的脚本,建立测试数据库和表值参数。
代码如下:

--Create DataBase 
create database BulkTestDB; 
go 
use BulkTestDB; 
go 
--Create Table 
Create table BulkTestTable( 
Id int primary key, 
UserName nvarchar(32), 
Pwd varchar(16)) 
go 
--Create Table Valued 
CREATE TYPE BulkUdt AS TABLE 
(Id int, 
UserName nvarchar(32), 
Pwd varchar(16))

下面我们使用最简单的Insert语句来插入100万条数据,代码如下:
代码如下:

Stopwatch sw = new Stopwatch(); 
SqlConnection sqlConn = new SqlConnection( 
ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString);//连接数据库 
SqlCommand sqlComm = new SqlCommand(); 
sqlComm.CommandText = string.Format("insert into BulkTestTable(Id,UserName,Pwd)values(@p0,@p1,@p2)");//参数化SQL 
sqlComm.Parameters.Add("@p0", SqlDbType.Int); 
sqlComm.Parameters.Add("@p1", SqlDbType.NVarChar); 
sqlComm.Parameters.Add("@p2", SqlDbType.VarChar); 
sqlComm.CommandType = CommandType.Text; 
sqlComm.Connection = sqlConn; 
sqlConn.Open(); 
try 
{ 
//循环插入100万条数据,每次插入10万条,插入10次。 
for (int multiply = 0; multiply < 10; multiply++) 
{ 
for (int count = multiply * 100000; count < (multiply + 1) * 100000; count++) 
{ 
sqlComm.Parameters["@p0"].Value = count; 
sqlComm.Parameters["@p1"].Value = string.Format("User-{0}", count * multiply); 
sqlComm.Parameters["@p2"].Value = string.Format("Pwd-{0}", count * multiply); 
sw.Start(); 
sqlComm.ExecuteNonQuery(); 
sw.Stop(); 
} 
//每插入10万条数据后,显示此次插入所用时间 
Console.WriteLine(string.Format("Elapsed Time is {0} Milliseconds", sw.ElapsedMilliseconds)); 
} 
} 
catch (Exception ex) 
{ 
throw ex; 
} 
finally 
{ 
sqlConn.Close(); 
} 
Console.ReadLine();

耗时图如下:
由于运行过慢,才插入10万条就耗时72390 milliseconds,所以我就手动强行停止了。
下面看一下使用Bulk插入的情况:
bulk方法主要思想是通过在客户端把数据都缓存在Table中,然后利用SqlBulkCopy一次性把Table中的数据插入到数据库
代码如下:
复制代码 代码如下:

public static void BulkToDB(DataTable dt) 
{ 
SqlConnection sqlConn = new SqlConnection( 
ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString); 
SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn); 
bulkCopy.DestinationTableName = "BulkTestTable"; 
bulkCopy.BatchSize = dt.Rows.Count; 
try 
{ 
sqlConn.Open(); 
if (dt != null && dt.Rows.Count != 0) 
bulkCopy.WriteToServer(dt); 
} 
catch (Exception ex) 
{ 
throw ex; 
} 
finally 
{ 
sqlConn.Close(); 
if (bulkCopy != null) 
bulkCopy.Close(); 
} 
} 
public static DataTable GetTableSchema() 
{ 
DataTable dt = new DataTable(); 
dt.Columns.AddRange(new DataColumn[]{ 
new DataColumn("Id",typeof(int)), 
new DataColumn("UserName",typeof(string)), 
new DataColumn("Pwd",typeof(string))}); 
return dt; 
} 
static void Main(string[] args) 
{ 
Stopwatch sw = new Stopwatch(); 
for (int multiply = 0; multiply < 10; multiply++) 
{ 
DataTable dt = Bulk.GetTableSchema(); 
for (int count = multiply * 100000; count < (multiply + 1) * 100000; count++) 
{ 
DataRow r = dt.NewRow(); 
r[0] = count; 
r[1] = string.Format("User-{0}", count * multiply); 
r[2] = string.Format("Pwd-{0}", count * multiply); 
dt.Rows.Add(r); 
} 
sw.Start(); 
Bulk.BulkToDB(dt); 
sw.Stop(); 
Console.WriteLine(string.Format("Elapsed Time is {0} Milliseconds", sw.ElapsedMilliseconds)); 
} 
Console.ReadLine(); 
}

耗时图如下:

可见,使用Bulk后,效率和性能明显上升。使用Insert插入10万数据耗时72390,而现在使用Bulk插入100万数据才耗时17583。
最后再看看使用表值参数的效率,会另你大为惊讶的。
表值参数是SQL Server 2008新特性,简称TVPs。对于表值参数不熟悉的朋友,可以参考最新的

book _disibledevent= string.Format("Pwd-{0}", count * multiply); 
dt.Rows.Add(r); 
} 
sw.Start(); 
TableValued.TableValuedToDB(dt); 
sw.Stop(); 
Console.WriteLine(string.Format("Elapsed Time is {0} Milliseconds", sw.ElapsedMilliseconds)); 
} 
Console.ReadLine(); 
}

 

历史上的今天

2020年:Zabbix 5.0 LTS 源码包编译部署以及新特性介绍(0条评论)

原创文章《SQLServer 批量插入数据的两种方法》,作者:Admin,未经允许不得转载。
转载或复制时,请注明出处:https://www.xuxiaokun.com/88.html,本文由 Mr.xu 博客网 整理。

发表评论

登录后才能评论
线上沟通

笔者期待与您共勉 · 互赢

有言必答:点击这里给我发消息

商务洽谈:交互沟通 · 共赢互利

邮件:admin@xuxiaokun.com

线上沟通:周一至周五 9:30 - 17:30

其余时间Mail或QQ我,有言必应。

微信关注
微信关注
投递稿件 我要留言
分享本页
返回顶部