热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

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

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

在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 online,我也会另外写一篇关于表值参数的博客,不过此次不对表值参数的概念做过多的介绍。言归正传,看代码:
代码如下:
public static void TableValuedToDB(DataTable dt)
{
SqlConnection sqlCOnn= new SqlConnection(
ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString);
const string TSqlStatement =
"insert into BulkTestTable (Id,UserName,Pwd)" +
" SELECT nc.Id, nc.UserName,nc.Pwd" +
" FROM @NewBulkTestTvp AS nc";
SqlCommand cmd = new SqlCommand(TSqlStatement, sqlConn);
SqlParameter catParam = cmd.Parameters.AddWithValue("@NewBulkTestTvp", dt);
catParam.SqlDbType = SqlDbType.Structured;
//表值参数的名字叫BulkUdt,在上面的建立测试环境的SQL中有。
catParam.TypeName = "dbo.BulkUdt";
try
{
sqlConn.Open();
if (dt != null && dt.Rows.Count != 0)
{
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlConn.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 = TableValued.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();
TableValued.TableValuedToDB(dt);
sw.Stop();
Console.WriteLine(string.Format("Elapsed Time is {0} Milliseconds", sw.ElapsedMilliseconds));
}

Console.ReadLine();
}

耗时图如下:

比Bulk还快5秒。
此文原创自CSDN TJVictor

推荐阅读
  • 提交后Activity4新 ... [详细]
  • 动态分页实现
    Code分页存储过程CREATEprocedurePagersqlstrnvarchar(4000),--查询字符串currentpageint,--第N页pagesizeint- ... [详细]
  • Oracle分析函数first_value()和last_value()的用法及原理
    本文介绍了Oracle分析函数first_value()和last_value()的用法和原理,以及在查询销售记录日期和部门中的应用。通过示例和解释,详细说明了first_value()和last_value()的功能和不同之处。同时,对于last_value()的结果出现不一样的情况进行了解释,并提供了理解last_value()默认统计范围的方法。该文对于使用Oracle分析函数的开发人员和数据库管理员具有参考价值。 ... [详细]
  • 本文介绍了一个在线急等问题解决方法,即如何统计数据库中某个字段下的所有数据,并将结果显示在文本框里。作者提到了自己是一个菜鸟,希望能够得到帮助。作者使用的是ACCESS数据库,并且给出了一个例子,希望得到的结果是560。作者还提到自己已经尝试了使用"select sum(字段2) from 表名"的语句,得到的结果是650,但不知道如何得到560。希望能够得到解决方案。 ... [详细]
  • 手把手教你使用GraphPad Prism和Excel绘制回归分析结果的森林图
    本文介绍了使用GraphPad Prism和Excel绘制回归分析结果的森林图的方法。通过展示森林图,可以更加直观地将回归分析结果可视化。GraphPad Prism是一款专门为医学专业人士设计的绘图软件,同时也兼顾统计分析的功能,操作便捷,可以帮助科研人员轻松绘制出高质量的专业图形。文章以一篇发表在JACC杂志上的研究为例,利用其中的多因素回归分析结果来绘制森林图。通过本文的指导,读者可以学会如何使用GraphPad Prism和Excel绘制回归分析结果的森林图。 ... [详细]
  • SeMITechnologies正在使用矢量搜索引擎Weaviate构建的内容。SeMI的首席执行官兼联合创始人BobvanLuijt说,它是一种独特的AI优先数据库,使用机器学习 ... [详细]
  • OleDbDataAdapter充当DataSet和数据源之间的桥梁,用于检索和保存数据。OleDbDataAdapter通过以下方法提供这个桥接器:使用Fill将数据从数 ... [详细]
  • 将熊猫数据框中的浮点数转换为整数原文:https://www. ... [详细]
  • 本文介绍了Python版Protobuf的安装和使用方法,包括版本选择、编译配置、示例代码等内容。通过学习本教程,您将了解如何在Python中使用Protobuf进行数据序列化和反序列化操作,以及相关的注意事项和技巧。 ... [详细]
  • ZSI.generate.Wsdl2PythonError: unsupported local simpleType restriction ... [详细]
  • sklearn数据集库中的常用数据集类型介绍
    本文介绍了sklearn数据集库中常用的数据集类型,包括玩具数据集和样本生成器。其中详细介绍了波士顿房价数据集,包含了波士顿506处房屋的13种不同特征以及房屋价格,适用于回归任务。 ... [详细]
  • Google Play推出全新的应用内评价API,帮助开发者获取更多优质用户反馈。用户每天在Google Play上发表数百万条评论,这有助于开发者了解用户喜好和改进需求。开发者可以选择在适当的时间请求用户撰写评论,以获得全面而有用的反馈。全新应用内评价功能让用户无需返回应用详情页面即可发表评论,提升用户体验。 ... [详细]
  • Python正则表达式学习记录及常用方法
    本文记录了学习Python正则表达式的过程,介绍了re模块的常用方法re.search,并解释了rawstring的作用。正则表达式是一种方便检查字符串匹配模式的工具,通过本文的学习可以掌握Python中使用正则表达式的基本方法。 ... [详细]
  • 拥抱Android Design Support Library新变化(导航视图、悬浮ActionBar)
    转载请注明明桑AndroidAndroid5.0Loollipop作为Android最重要的版本之一,为我们带来了全新的界面风格和设计语言。看起来很受欢迎࿰ ... [详细]
  • 使用Spring AOP实现切面编程的步骤和注意事项
    本文介绍了使用Spring AOP实现切面编程的步骤和注意事项。首先解释了@EnableAspectJAutoProxy、@Aspect、@Pointcut等注解的作用,并介绍了实现AOP功能的方法。然后详细介绍了创建切面、编写测试代码的过程,并展示了测试结果。接着讲解了关于环绕通知的使用方法,并修改了FirstTangent类以添加环绕通知方法。最后介绍了利用AOP拦截注解的方法,只需修改全局切入点即可实现。使用Spring AOP进行切面编程可以方便地实现对代码的增强和拦截。 ... [详细]
author-avatar
想挽回的-谎言
这个家伙很懒,什么也没留下!
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有