SQL Server 2012中快速插入批量数据的示例及疑惑

最新推荐文章于 2018-08-15 19:23:10 发布
最新推荐文章于 2018-08-15 19:23:10 发布
阅读量406 收藏
点赞数
分类专栏： SQLSERVER

SQL Server 2008中SQL应用系列--目录索引

今天在做一个案例演示时，在SQL Server 2012中使用Insert语句插入1万条数据，结果遇到了一个奇怪的现象，现将过程分享出来，以供有兴趣的同学参考。


附：我的测试环境为：
SQL Server 2012，命名实例

Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
Feb 10 2012 19:13:17
Copyright (c) Microsoft Corporation
Enterprise Edition on Windows NT 6.0 <X86> (Build 6002: Service Pack 2)


创建示例数据库

[sql] view plain copy print ?
  1. IF OBJECT_ID('DemoPager2012') IS NOT NULL
  2. DROP DataBase DemoPager2012
  3. GO

  5. CREATE Database DemoPager2012
  6. GO

  8. USE DemoPager2012
  9. GO 
IF OBJECT_ID('DemoPager2012') IS NOT NULL
DROP DataBase DemoPager2012
GO

CREATE Database DemoPager2012
GO

USE DemoPager2012
GO
示例表，该表只有四个字段。
[sql] view plain copy print ?
  1. /*
  2. Setup script to create the sample table and fill it with
  3. sample data.
  4. */
  5. IF OBJECT_ID('Customers','U') IS NOT NULL
  6. DROP TABLE Customers

  8. CREATE TABLE Customers ( CustomerID INT primary key identity(1,1),
  9. CustomerNumber CHAR(4),
  10. CustomerName VARCHAR(50),
  11. CustomerCity VARCHAR(20) )
  12. GO 
/*
Setup script to create the sample table and fill it with
sample data.
*/
IF OBJECT_ID('Customers','U') IS NOT NULL
DROP TABLE Customers

CREATE TABLE Customers ( CustomerID INT primary key identity(1,1),
CustomerNumber CHAR(4),
CustomerName VARCHAR(50),
CustomerCity VARCHAR(20) )
GO
现在展示批量插入10000条数据到该表中，语句如下：
[sql] view plain copy print ?
  1. TRUNCATE table Customers
  2. GO

  4. ----清除干扰查询
  5. DBCC DROPCLEANBUFFERS
  6. DBCC FREEPROCCACHE

  8. SET STATISTICS IO ON;
  9. SET STATISTICS TIME ON;
  10. GO

  12. DECLARE @d Datetime
  13. SET @d=getdate();

  15. declare @i int=1
  16. while @i<=10000
  17. begin
  18. INSERT INTO Customers (CustomerNumber, CustomerName,
  19. CustomerCity)
  20. SELECT REPLACE(STR(@i, 4), ' ', '0'),'Customer ' + STR(@i,6),
  21. CHAR(65 + (@i % 26)) + '-City'
  22. set @i=@i+1
  23. end

  25. select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())

  27. SET STATISTICS IO OFF ;
  28. SET STATISTICS TIME OFF;
  29. GO 
TRUNCATE table Customers
GO

----清除干扰查询
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

DECLARE @d Datetime
SET @d=getdate();

declare @i int=1
while @i<=10000
begin
INSERT INTO Customers (CustomerNumber, CustomerName,
CustomerCity)
SELECT REPLACE(STR(@i, 4), ' ', '0'),'Customer ' + STR(@i,6),
CHAR(65 + (@i % 26)) + '-City'
set @i=@i+1
end

select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())

SET STATISTICS IO OFF ;
SET STATISTICS TIME OFF;
GO

该插入语句在SQL Server 2008 r2版本和SQL Server 2012版本中，测试结果如下：

邀月工作室

令我惊讶的是，SQL Server 2012居然耗时达到5分多钟，而SQL Server 2008R2版，只需要大约6秒钟。更令人费解的是：查询的I/O统计和elapsed time，在这两个版本中几乎一样。对此异象，我只能理解为每次Insert时的毫秒级精度可能不足以度量该次操作带来的细小差距，然而累积起来就非常可观了。

解决方案一：使用 Set NoCount On，效果立竿见影

[sql] view plain copy print ?
  1. TRUNCATE table Customers
  2. GO

  4. ----清除干扰查询
  5. DBCC DROPCLEANBUFFERS
  6. DBCC FREEPROCCACHE

  8. SET STATISTICS IO ON;
  9. SET STATISTICS TIME ON;
  10. GO

  12. DECLARE @d Datetime
  13. SET @d=getdate();
  14. set nocount on
  15. declare @i int=1
  16. while @i<=10000
  17. begin
  18. INSERT INTO Customers (CustomerNumber, CustomerName,
  19. CustomerCity)
  20. SELECT REPLACE(STR(@i, 4), ' ', '0'),'Customer ' + STR(@i,6),
  21. CHAR(65 + (@i % 26)) + '-City'
  22. set @i=@i+1
  23. end

  25. select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())

  27. SET STATISTICS IO OFF ;
  28. SET STATISTICS TIME OFF;
  29. GO 
TRUNCATE table Customers
GO

----清除干扰查询
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

DECLARE @d Datetime
SET @d=getdate();
set nocount on
declare @i int=1
while @i<=10000
begin
INSERT INTO Customers (CustomerNumber, CustomerName,
CustomerCity)
SELECT REPLACE(STR(@i, 4), ' ', '0'),'Customer ' + STR(@i,6),
CHAR(65 + (@i % 26)) + '-City'
set @i=@i+1
end

select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())

SET STATISTICS IO OFF ;
SET STATISTICS TIME OFF;
GO

邀月工作室

Set NoCount On（http://msdn.microsoft.com/zh-cn/library/ms189837.aspx）的作用：使返回的结果中不包含有关受 Transact-SQL 语句影响的行数的信息。这在批量插入时将显著提高性能。至于 本例中，为什么SQL Server 2008 R2版中却不受该开关影响，希望知道的同学不吝赐教，非常感谢。

改进解决方案二：使用 Set NoCount On+Transaction

[sql] view plain copy print ?
  1. TRUNCATE table Customers
  2. GO

  4. ----清除干扰查询
  5. DBCC DROPCLEANBUFFERS
  6. DBCC FREEPROCCACHE



  10. SET STATISTICS IO ON;
  11. SET STATISTICS TIME ON;
  12. GO

  14. DECLARE @d Datetime
  15. SET @d=getdate();
  16. set nocount on
  17. declare @i int=1
  18. BEGIN TRANSACTION
  19. while @i<=10000
  20. begin
  21. INSERT INTO Customers (CustomerNumber, CustomerName,
  22. CustomerCity)
  23. SELECT REPLACE(STR(@i, 4), ' ', '0'),'Customer ' + STR(@i,6),
  24. CHAR(65 + (@i % 26)) + '-City'
  25. set @i=@i+1
  26. end
  27. COMMIT
  28. select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())

  30. SET STATISTICS IO OFF ;
  31. SET STATISTICS TIME OFF;
  32. GO 
TRUNCATE table Customers
GO

----清除干扰查询
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

 

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

DECLARE @d Datetime
SET @d=getdate();
set nocount on
declare @i int=1
BEGIN TRANSACTION
while @i<=10000
begin
INSERT INTO Customers (CustomerNumber, CustomerName,
CustomerCity)
SELECT REPLACE(STR(@i, 4), ' ', '0'),'Customer ' + STR(@i,6),
CHAR(65 + (@i % 26)) + '-City'
set @i=@i+1
end
COMMIT
select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())

SET STATISTICS IO OFF ;
SET STATISTICS TIME OFF;
GO

邀月工作室

解决方案三：使用递归CTE插入

[sql] view plain copy print ?
  1. TRUNCATE table Customers
  2. GO

  4. DBCC DROPCLEANBUFFERS
  5. DBCC FREEPROCCACHE

  7. SET STATISTICS IO ON;
  8. SET STATISTICS TIME ON;
  9. GO

  11. DECLARE @d Datetime
  12. SET @d=getdate();

  14. /*****运用CTE递归插入，速度较快，邀月注***********************/
  15. WITH Seq (num,CustomerNumber, CustomerName, CustomerCity) AS
  16. (SELECT 1,cast('0000'as CHAR(4)),cast('Customer 0' AS NVARCHAR(50)),cast('X-City' as NVARCHAR(20))
  17. UNION ALL
  18. SELECT num + 1,Cast(REPLACE(STR(num, 4), ' ', '0') AS CHAR(4)),
  19. cast('Customer ' + STR(num,6) AS NVARCHAR(50)),
  20. cast(CHAR(65 + (num % 26)) + '-City' AS NVARCHAR(20))
  21. FROM Seq
  22. WHERE num <= 10000
  23. )
  24. INSERT INTO Customers (CustomerNumber, CustomerName, CustomerCity)
  25. SELECT CustomerNumber, CustomerName, CustomerCity
  26. FROM Seq
  27. OPTION (MAXRECURSION 0)

  29. select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())

  31. SET STATISTICS IO OFF ;
  32. SET STATISTICS TIME OFF;
  33. GO 
TRUNCATE table Customers
GO

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

DECLARE @d Datetime
SET @d=getdate();

/*****运用CTE递归插入，速度较快，邀月注***********************/
WITH Seq (num,CustomerNumber, CustomerName, CustomerCity) AS
(SELECT 1,cast('0000'as CHAR(4)),cast('Customer 0' AS NVARCHAR(50)),cast('X-City' as NVARCHAR(20))
UNION ALL
SELECT num + 1,Cast(REPLACE(STR(num, 4), ' ', '0') AS CHAR(4)),
cast('Customer ' + STR(num,6) AS NVARCHAR(50)),
cast(CHAR(65 + (num % 26)) + '-City' AS NVARCHAR(20))
FROM Seq
WHERE num <= 10000
)
INSERT INTO Customers (CustomerNumber, CustomerName, CustomerCity)
SELECT CustomerNumber, CustomerName, CustomerCity
FROM Seq
OPTION (MAXRECURSION 0)

select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())

SET STATISTICS IO OFF ;
SET STATISTICS TIME OFF;
GO

邀月工作室

小结：SQL Server 2012中批量插入数据时，请记得Set NoCount ON，并尽可能加上Transaction，当然，推荐使用CTE，这可能会带来性能上的巨大提升。

邀月补充：

后来与微软亚太工程师多次沟通，得出初步结论：

在不打开“set nocount on”时，SSMS 2012与SSMS 2008r2版本的UI在执行效率上可能有极大差异，而与SQL Server引擎没有明显相关。

邀月注：本文版权由邀月和CSDN共同所有，转载请注明出处。
助人等于自助! 3w@live.cn

mituan1234567
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
sql下三种批量插入数据的方法
12-15
第二种方法使用的是SqlBulkCopy，使您可以用其他源的数据有效批量加载 SQL Server 表；第三种使用的方法是sql server中的表值参数方法，表值参数是 SQL Server 2008 中的新参数类型。表值参数是使用用户定义的表类型...
SQL Server 批量插入数据的两种方法
灰太狼的博客
07-18 16万+
      在SQL Server 中插入一条数据使用Insert语句，但是如果想要批量插入一堆数据的话，循环使用Insert不仅效率低，而且会导致SQL一系统性能问题。下面介绍SQL Server支持的两种批量数据插入方法：Bulk和表值参数(Table-Valued Parameters)。运行下面的脚本，建立测试数据库和表值参数。--Create DataBasecreate d
SQL Server数据表中数据的增加（插入）、查询、修改、删除
热门推荐
秦哥的博客
08-15 20万+
目录 零、码仙励志 一、数据表中数据的增加（插入） 二、数据表中数据的查询 三、数据表中数据的修改 四、数据表中数据的删除   零、码仙励志 伟人所达到并保持着的高处，并不是一飞就到的，而是他们在同伴们都睡着的时候，一步步艰辛地向上攀爬的 特别提醒（一定要看一下）： 这篇文章写的是：SQL Server数据表中数据的增加（插入）、查询、修改、删除 不是：SQL Server中...
【SQL Server 批量插入数据】
HilaryHe
07-21 1万+
在SQL Server 中插入一条数据使用Insert语句，但是如果想要批量插入一堆数据的话，循环使用Insert不仅效率低，而且会导致SQL一系统性能问题。下面介绍SQL Server支持的两种批量数据插入方法：Bulk和表值参数(Table-Valued Parameters)，高效插入数据。 新建数据库： --Create DataBase create database BulkT
批量高效向sqlserver中插入数据
月
09-13 4693
原文：http://blog.csdn.net/tjvictor/article/details/4360030  表值参数是SQL Server 2008新特性，简称TVPs   代码如下： 数据库创建： --Create DataBase create database BulkTestDB; go use BulkTestDB; go --Create
SQLServer用存储过程实现插入更新数据示例
09-10
主要介绍了SQLServer如何用存储过程实现插入更新数据,需要的朋友可以参考下
21天学习SQL V1.0
11-09
21天学习SQL V1.0.pdf 66 SQL 21 日自学通(V1.0) 翻译人笨猪 EMAIL [email protected] 2 日期/时间函数.........................................................................................................
SQL21日自学通
12-19
示例表及数据394 一个简单的PL/SQL 语句块395 又一个程序398 存储过程包和触发机制403 总结406 问与答407 校练场407 练习407 第19 天TRANSACT-SQL 简介408 目标408 TRANSACT-SQL 概貌408 对ANSI SQL 的扩展408 谁...
Spring中文帮助文档
08-05
11.5.1. 使用SimpleJdbcInsert插入数据 11.5.2. 使用SimpleJdbcInsert来获取自动生成的主键 11.5.3. 指定SimpleJdbcInsert所使用的字段 11.5.4. 使用SqlParameterSource提供参数值 11.5.5. 使用SimpleJdbcCall...
MySQL 5权威指南(第3版) 中文版 下载地址
12-11
　19.5 转换器：从Microsoft SQL Server到MySQL 　19.6 VBMySQLDirect 　第20章 Visual Basic .NET和C# 　20.1 ADO .NET与MySQL之间的通信 　20.2 编程技巧 　20.3 示例：把新图书记录存入mylibrary数据库 　...
企业内训系统2021版|员工在线学习平台|适合企事业单位及机关部门内部在线培训考试平台搭建
04-07
操作系统：推荐使用 windows server 2012 64位中文版， 数据库：SQL2008， IIS：IIS7或以上版本， .NET：.NET4.6或以上版本 【初始账号密码】 学员登录入口： http://访问网址/student/index.ashx 学员账号：...
MybatisGeneratorPlus.zip
07-19
扩展 Mybatis-Generator，自动生成支持 Oracle、Mysql、Sqlserver 分页查询和批量插入操作的自动代码，支持从 Mapper 接口读取数据源名称;支持 Oracle 使用 SEQUENCE 实现自增主键:需要事先建立好表主键对应的...
MySQL5 权威指南第3版中文版_part1
02-12
　19.5 转换器：从Microsoft SQL Server到MySQL 　19.6 VBMySQLDirect 　第20章 Visual Basic .NET和C# 　20.1 ADO .NET与MySQL之间的通信 　20.2 编程技巧 　20.3 示例：把新图书记录存入mylibrary数据库 　20.4 ...
用SQL语句向表格中插入数据
wangjihua的专栏
12-21 6476
向表格中插入数据SQL语言使用insert语句向数据库表格中插入或添加新的数据行。Insert语句的使用格式如下： insert into tablename (first_column,...last_column) values (first_value,...last_value);  例如： insert into employee (firstname, lastname, age, a
自我总结：sql中一次插入多条数据的写法
ShangQuan2012的博客
03-19 2万+
sql中一次插入多条数据的写法
sql server批量插入数据
最新发布
09-02
你可以使用 SQL Server 的 INSERT INTO 语句来批量插入数据。有几种方法可以实现批量插入，以下是两种常见的方法： 1. 使用INSERT INTO SELECT语句：首先，创建一个临时表或使用已有的表来存储要插入的数据，然后...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值