SQL Server 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 CorporationEnterprise Edition on Windows NT 6.0 <X86> (Build 6002: Service Pack 2)

创建示例数据库

IF OBJECT_ID(‘DemoPager2012’) IS NOT NULLDROP DataBase DemoPager2012GOCREATE Database DemoPager2012GOUSE DemoPager2012GO示例表,该表只有四个字段。/*Setup script to create the sample table and fill it withsample data.*/IF OBJECT_ID(‘Customers’,’U’) IS NOT NULLDROP TABLE CustomersCREATE TABLE Customers ( CustomerID INT primary key identity(1,1),CustomerNumber CHAR(4),CustomerName VARCHAR(50),CustomerCity VARCHAR(20) )GO现在展示批量插入10000条数据到该表中,语句如下:TRUNCATE table CustomersGO—-清除干扰查询DBCC DROPCLEANBUFFERSDBCC FREEPROCCACHESET STATISTICS IO ON;SET STATISTICS TIME ON;GODECLARE @d DatetimeSET @d=getdate();declare @i int=1while @i<=10000beginINSERT INTO Customers (CustomerNumber, CustomerName,CustomerCity)SELECT REPLACE(STR(@i, 4), ‘ ‘, ‘0’),’Customer ‘ + STR(@i,6),CHAR(65 + (@i % 26)) + ‘-City’set @i=@i+1endselect [语句执行花费时间(毫秒)]=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,效果立竿见影

TRUNCATE table CustomersGO—-清除干扰查询DBCC DROPCLEANBUFFERSDBCC FREEPROCCACHESET STATISTICS IO ON;SET STATISTICS TIME ON;GODECLARE @d DatetimeSET @d=getdate();set nocount ondeclare @i int=1while @i<=10000beginINSERT INTO Customers (CustomerNumber, CustomerName,CustomerCity)SELECT REPLACE(STR(@i, 4), ‘ ‘, ‘0’),’Customer ‘ + STR(@i,6),CHAR(65 + (@i % 26)) + ‘-City’set @i=@i+1endselect [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())SET STATISTICS IO OFF ;SET STATISTICS TIME OFF;GO

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

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

TRUNCATE table CustomersGO—-清除干扰查询DBCC DROPCLEANBUFFERSDBCC FREEPROCCACHESET STATISTICS IO ON;SET STATISTICS TIME ON;GODECLARE @d DatetimeSET @d=getdate();set nocount ondeclare @i int=1BEGIN TRANSACTIONwhile @i<=10000beginINSERT INTO Customers (CustomerNumber, CustomerName,CustomerCity)SELECT REPLACE(STR(@i, 4), ‘ ‘, ‘0’),’Customer ‘ + STR(@i,6),CHAR(65 + (@i % 26)) + ‘-City’set @i=@i+1endCOMMITselect [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())SET STATISTICS IO OFF ;SET STATISTICS TIME OFF;GO

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

TRUNCATE table CustomersGODBCC DROPCLEANBUFFERSDBCC FREEPROCCACHESET STATISTICS IO ON;SET STATISTICS TIME ON;GODECLARE @d DatetimeSET @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 ALLSELECT 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 SeqWHERE num <= 10000)INSERT INTO Customers (CustomerNumber, CustomerName, CustomerCity)SELECT CustomerNumber, CustomerName, CustomerCityFROM SeqOPTION (MAXRECURSION 0)select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())SET STATISTICS IO OFF ;SET STATISTICS TIME OFF;GO你曾经说,你曾经说。走在爱的旅途,我们的脚步多么轻松……

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

相关文章:

你感兴趣的文章:

标签云: