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

时间:2024-04-10 21:04:34

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)

创建示例数据库

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

  1. /*
    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条数据到该表中,语句如下:

  1. 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), ' ', ''),'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中快速插入批量数据的示例及疑惑

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

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

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

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

  1. 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), ' ', ''),'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 2012中快速插入批量数据的示例及疑惑

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

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

  1. 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), ' ', ''),'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

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

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

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

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

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

邀月补充:

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

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