Sql Server 2012批量处理注意！！_如何查看sqlserver 2012是否支持批处理-优快云博客

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 plaincopyprint? 
   
 IF OBJECT_ID('DemoPager2012') IS NOT NULL  
 DROP DataBase DemoPager2012  
 GO  
   
 CREATE Database DemoPager2012  
 GO  
   
 USE DemoPager2012  
 GO  

示例表，该表只有四个字段。

[sql]view plaincopyprint? 
   
 /*  
 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 plaincopyprint? 
   
 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 plaincopyprint? 
   
 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 plaincopyprint? 
   
 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 plaincopyprint? 
   
 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  