`
peizhiinfo
  • 浏览: 1417413 次
文章分类
社区版块
存档分类
最新评论

SQLServer中批量插入数据方式的性能对比

 
阅读更多
昨天下午快下班的时候,无意中听到公司两位同事在探讨批量向数据库插入数据的性能优化问题,顿时来了兴趣,把自己的想法向两位同事说了一下,于是有了本文。

公司技术背景:数据库访问类(xxx.DataBase.Dll)调用存储过程实现数据库的访问。

技术方案一:

压缩时间下程序员写出的第一个版本,仅仅为了完成任务,没有从程序上做任何优化,实现方式是利用数据库访问类调用存储过程,利用循环逐条插入。很明显,这种方式效率并不高,于是有了前面的两位同事讨论效率低的问题。

技术方案二:

由于是考虑到大数据量的批量插入,于是我想到了ADO.NET2.0的一个新的特性:SqlBulkCopy。有关这个的性能,很早之前我是亲自做过性能测试的,效率非常高。这也是我向公司同事推荐的技术方案。

技术方案三:

利用SQLServer2008的新特性--表值参数(Table-Valued Parameter)。表值参数是SQLServer2008才有的一个新特性,使用这个新特性,我们可以把一个表类型作为参数传递到函数或存储过程里。不过,它也有一个特点:表值参数在插入数目少于 1000 的行时具有很好的执行性能。

技术方案四:

对于单列字段,可以把要插入的数据进行字符串拼接,最后再在存储过程中拆分成数组,然后逐条插入。查了一下存储过程中参数的字符串的最大长度,然后除以字段的长度,算出一个值,很明显是可以满足要求的,只是这种方式跟第一种方式比起来,似乎没什么提高,因为原理都是一样的。

技术方案五:

考虑异步创建、消息队列等等。这种方案无论从设计上还是开发上,难度都是有的。

技术方案一肯定是要被否掉的了,剩下的就是在技术方案二跟技术方案三之间做一个抉择,鉴于公司目前的情况,技术方案四跟技术方案五就先不考虑了。

接下来,为了让大家对表值参数的创建跟调用有更感性的认识,我将写的更详细些,文章可能也会稍长些,不关注细节的朋友们可以选择跳跃式的阅读方式。

再说一下测试方案吧,测试总共分三组,一组是插入数量小于1000的,另外两组是插入数据量大于1000的(这里我们分别取10000跟1000000),每组测试又分10次,取平均值。怎么做都明白了,Let’s go!

1.创建表。

为了简单,表中只有一个字段,如下图所示:

2.创建表值参数类型

我们打开查询分析器,然后在查询分析器中执行下列代码:

Create Type PassportTableType as Table
(
PassportKey nvarchar(50)

)

执行成功以后,我们打开企业管理器,按顺序依次展开下列节点--数据库、展开可编程性、类型、用户自定义表类型,就可以看到我们创建好的表值类型了如下图所示:

说明我们创建表值类型成功了。

3.编写存储过程

存储过程的代码为:

USE [TestInsert]

GO
/****** Object: StoredProcedure [dbo].[CreatePassportWithTVP] Script Date: 03/02/2010 00:14:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:	<Kevin>
-- Create date: <2010-3-1>
-- Description:	<创建通行证>
-- =============================================
Create PROCEDURE [dbo].[CreatePassportWithTVP] 

@TVP PassportTableType readonly

AS
BEGIN
SET NOCOUNT ON;

Insert into Passport(PassportKey) select PassportKey from @TVP

END
可能在查询分析器中,智能提示会提示表值类型有问题,会出现红色下划线(见下图),不用理会,
继续运行我们的代码,完成存储过程的创建

 

4.编写代码调用存储过程。

三种数据库的插入方式代码如下,由于时间比较紧,代码可能不那么易读,特别代码我加了些注释。

主要部分的代码
分享到:
评论

相关推荐

    批量插入sql语句.txt

    bulkCopy 批量插入sql语句 测试过性能比较高,10w数据几毫秒就可以了,真的超级快,超级快,超级快

    SQL批量插入数据几种方案的性能详细对比

    技术方案二:由于是考虑到大数据量的批量插入,于是我想到了ADO.NET2.0的一个新的特性:SqlBulkCopy。有关这个的性能,很早之前我是亲自做过性能测试的,效率非常高。这也是我向公司同事推荐的技术方案。技术方案三...

    SQLServer安全及性能优化

    SQLServer安全及性能优化 修补漏洞 安装程序补丁修补漏洞 随时关注微软官方网站补丁升级 关闭不必要的端口 关闭联必要的服务 数据库引擎 SQL Server Analysis Services SQL Server Reporting Services SQL ...

    .NET批量大数据插入性能分析及比较

    拼接sql语句批量插入  3. 拼接sql语句并使用Transaction  4. 拼接sql语句并使用SqlTransaction  5. 使用DataAdapter  6. 使用TransactionScope及SqlBulkCopy  7. 使用表值参数  数据库使用SQL Server,...

    SQL21日自学通

    在PL/SQL 中的数据类型377 字符串类型377 数值数据类型378 二进制数据类型378 日期数据类型378 逻辑数据类型378 ROWID379 PL/SQL 块的结构379 注释380 DECLARE 部分380 变量声明380 常量定义381 指针定义381 %TYPE ...

    C#编程经验技巧宝典

    85 &lt;br&gt;0131 巧截字符串的数字 86 &lt;br&gt;0132 如何存储变长字符串 86 &lt;br&gt;0133 在进行字符串比较时忽略大小写 87 &lt;br&gt;0134 如何去除字符串尾空格 87 &lt;br&gt;0135 如何去掉字符串中所有空格 ...

    ASP.NET2.0高级编程(第4版)1/6

    本书全面介绍了ASP.NET各种编程技能和2.0版中的巨大变化,并详细阐述了2.0版中的每个新特性。书中提供了大量的实例,可帮助读者快速掌握如何在.NET平台下开发功能强大的ASP.NET应用程序。本书适合有一些基础的ASP...

    2.ASP.NET.2.0.高级编程(第4版) [1/7]

    12.2.1 从各种数据源中批量加载数据 372 12.2.2 批处理多个更新 381 12.2.3 多个活动的结果集(MARS) 388 12.2.4 命令的异步执行 394 12.2.5 异步连接 415 12.3 小结 416 第13章 使用XML 417 13.1 XML基础 417...

    易语言程序免安装版下载

    修改外部数据库在4.12版中导致的不兼容问题,并增加了对MS SQL Server数据库中image和text字段类型的说明。 7. 修改扩展界面支持库一,禁止透明标签在父窗口刷新时自动刷新,以解决其导致窗口刷新缓冲的问题。 8....

    ERP5.0概述

    作为网络信息系统,SQL Server 2000数据库具有数据容量大、速度快、数据安全性好等优点,针对信息管理系统数据量大、交换频繁、计算统计复杂的特点,本系统采用数据仓库和数据挖掘技术,实现决策人员可按任意时间段...

    mysql数据库my.cnf配置文件

    # 批量插入缓存大小, 这个参数是针对MyISAM存储引擎来说的。适用于在一次性插入100-1000+条记录时, 提高效率。默认值是8M。可以针对数据量的大小,翻倍增加。 myisam_sort_buffer_size = 1024M # MyISAM设置恢复表...

    21天学习SQL V1.0

    21天学习SQL V1.0.pdf 66 SQL 21 日自学通(V1.0) 翻译人笨猪 EMAIL wyhsillypig@163.com 2 日期/时间函数.........................................................................................................

    1000个【易语言模块大全汇总批量下载】

    易语言~模块~批量~下载 2008-11-08 14:41 文件夹 文件夹 易语言模块大全 2005-10-21 15:30 14489 3100 易语言模块大全\24位转单色位图模块.ec 2007-01-18 07:00 7110 2339 易语言模块大全\69msn.ec 2005-05-26 02:18...

    易语言540个易模块

    取注册表键句柄 全面操作SQL Server 2000 R RAR压缩模块 1.0 RC4 加密算法 1.0 RC4-林子深 RUN加减模块1.0 日期格式规范 日期设置模块 日期时间增强 软件注册模块 软件注册算号器模块 S Sqlite表管理 SQL...

    易语言模块大全汇总批量下载

    易语言~模块~批量~下载 2008-11-08 14:41 文件夹 文件夹 易语言模块大全 2005-10-21 15:30 14489 3100 易语言模块大全\24位转单色位图模块.ec 2007-01-18 07:00 7110 2339 易语言模块大全\69msn.ec 2005-05-26 02:18...

Global site tag (gtag.js) - Google Analytics