SQL Server 2012中快速插入批量数据的示例及疑惑
来源:互联网 发布:网络贷款需要什么条件 编辑:程序博客网 时间:2024/06/03 14:52
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)
创建示例数据库
- IF OBJECT_ID('DemoPager2012')IS NOTNULL
- DROP DataBase DemoPager2012
- GO
- CREATE Database DemoPager2012
- GO
- USE DemoPager2012
- GO
- /*
- Setup script to create the sampletable and fill itwith
- sample data.
- */
- IF OBJECT_ID('Customers','U')IS NOTNULL
- DROP TABLE Customers
- CREATE TABLE Customers ( CustomerIDINT primarykey identity(1,1),
- CustomerNumber CHAR(4),
- CustomerName VARCHAR(50),
- CustomerCity VARCHAR(20) )
- GO
- TRUNCATE table Customers
- GO
- ----清除干扰查询
- DBCC DROPCLEANBUFFERS
- DBCC FREEPROCCACHE
- SET STATISTICS IOON;
- SET STATISTICSTIME 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 IOOFF ;
- SET STATISTICSTIME 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,效果立竿见影
- TRUNCATE table Customers
- GO
- ----清除干扰查询
- DBCC DROPCLEANBUFFERS
- DBCC FREEPROCCACHE
- SET STATISTICS IOON;
- SET STATISTICSTIME 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 IOOFF ;
- SET STATISTICSTIME OFF;
- GO
Set NoCount On(http://msdn.microsoft.com/zh-cn/library/ms189837.aspx)的作用:使返回的结果中不包含有关受 Transact-SQL 语句影响的行数的信息。这在批量插入时将显著提高性能。至于 本例中,为什么SQL Server 2008 R2版中却不受该开关影响,希望知道的同学不吝赐教,非常感谢。
改进解决方案二:使用 Set NoCount On+Transaction
- TRUNCATE table Customers
- GO
- ----清除干扰查询
- DBCC DROPCLEANBUFFERS
- DBCC FREEPROCCACHE
- SET STATISTICS IOON;
- SET STATISTICSTIME 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 IOOFF ;
- SET STATISTICSTIME OFF;
- GO
解决方案三:使用递归CTE插入
- TRUNCATE table Customers
- GO
- DBCC DROPCLEANBUFFERS
- DBCC FREEPROCCACHE
- SET STATISTICS IOON;
- SET STATISTICSTIME ON;
- GO
- DECLARE @d Datetime
- SET @d=getdate();
- /*****运用CTE递归插入,速度较快,邀月注***********************/
- WITH Seq (num,CustomerNumber, CustomerName, CustomerCity)AS
- (SELECT 1,cast('0000'asCHAR(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 IOOFF ;
- SET STATISTICSTIME OFF;
- GO
小结:SQL Server 2012中批量插入数据时,请记得Set NoCount ON,并尽可能加上Transaction,当然,推荐使用CTE,这可能会带来性能上的巨大提升。
邀月补充:
后来与微软亚太工程师多次沟通,得出初步结论:
在不打开“set nocount on”时,SSMS 2012与SSMS 2008r2版本的UI在执行效率上可能有极大差异,而与SQL Server引擎没有明显相关。助人等于自助! 3w@live.cn
- SQL Server 2012中快速插入批量数据的示例及疑惑
- [转]SQL Server 2012中快速插入批量数据的示例及疑惑 .
- SQL Server 2012中快速插入批量数据的示例及疑惑
- SQL Server 批量插入数据的方法
- 使用SqlBulkCopy批量快速插入大量数据到SQL SERVER
- [SQL Server] 批量插入数据
- 【SQL Server 批量插入数据】
- 用SQL批量插入数据示例
- SQL Server 批量插入数据的两种方法
- [SQL Server]SQLServer 批量插入数据的两种方法
- SQL Server 批量插入数据的两种方法
- SQL Server 批量插入数据的两种方法
- SQL SERVER批量插入相同的数据--懒人方法
- SQL Server 批量插入数据的两种方法
- SQL Server 批量插入数据的两种方法
- SQL Server 批量插入数据的两种方法
- SQL Server 批量插入数据的两种方法
- SQL Server 批量插入数据的两种方法
- Oracle表与索引的分析及索引重建
- 细数十大你不得不用的MySQL开发工具(1)
- 千万要避免的五种程序注释方式
- VC++6.0 下搭建 wxWidgets 开发环境
- TabActivity 嵌套是 处理阴影问题 。
- SQL Server 2012中快速插入批量数据的示例及疑惑
- 问题
- 读故事了解"云"
- JavaEE 绝对路径、相对路径获取方式
- poll函数实现多路复用
- 如何在W5200中实现DHCP
- JIRA 5安装
- 提高英语阅读
- cache menu 表的增长