sql-server – 为什么DELETE会对性能产生影响？

最后是一个测试脚本,用于比较@table变量和#temp表之间的性能.我想我已经正确设置了 – 性能时间是在DELETE / TRUNCATE命令之外进行的.我得到的结果如下(以毫秒为单位的时间).

@Table Variable  #Temp (delete)  #Temp (truncate)
---------------  --------------  ----------------
5723             5180            5506
15636            14746           7800
14506            14300           5583
14030            15460           5386
16706            16186           5360

为了确保我的理智,这表明CURRENT_TIMESTAMP(又名GetDate())是在语句时而不是批处理中进行的,因此TRUNCATE / DELETE与SET @StartTime = CURRENT_TIMESTAMP之间不应该有任何交互.声明.

select current_timestamp
waitfor delay '00:00:04'
select current_timestamp

-----------------------
2012-10-21 11:29:20.290

-----------------------
2012-10-21 11:29:24.290

当使用DELETE清除表时,第一次运行和后续运行之间的跳转非常一致.我对DELETE的理解中缺少什么？我重复了这么多次,交换了订单,调整tempdb大小以不需要增长等.

CREATE TABLE #values (
  id int identity primary key,-- will be clustered
  name varchar(100) null,number int null,type char(3) not null,low int null,high int null,status smallint not null
);
GO
SET NOCOUNT ON;

DECLARE @values TABLE (
  id int identity primary key clustered,name varchar(100) null,status smallint not null
);
DECLARE  @ExecutionTime  TABLE(      Duration bigINT    ) 
DECLARE  @StartTime DATETIME,@i INT = 1; 
WHILE (@i <= 5) 
  BEGIN 
    DELETE @values;
    DBCC freeproccache With NO_InfoMSGS;
    DBCC DROPCLEANBUFFERS With NO_InfoMSGS;
    SET @StartTime = CURRENT_TIMESTAMP -- alternate getdate() 
    /****************** measured process ***********************/ 

    INSERT @values SELECT a.* FROM master..spt_values a join master..spt_values b on b.type='P' and b.number < 1000;

    /**************** end measured process *********************/ 
    INSERT @ExecutionTime 
    SELECT DurationInMilliseconds = datediff(ms,@StartTime,CURRENT_TIMESTAMP) 
    SET @i +=  1 
  END -- WHILE 

SELECT DurationInMilliseconds = Duration FROM   @ExecutionTime 
GO 

-- Temporary table
DECLARE  @ExecutionTime  TABLE(      Duration bigINT    ) 
DECLARE  @StartTime DATETIME,@i INT = 1; 
WHILE (@i <= 5) 
  BEGIN 
    delete #values;
    -- TRUNCATE TABLE #values;
    DBCC freeproccache With NO_InfoMSGS;
    DBCC DROPCLEANBUFFERS With NO_InfoMSGS;
    SET @StartTime = CURRENT_TIMESTAMP -- alternate getdate() 
    /****************** measured process ***********************/ 

    INSERT #values SELECT a.* FROM master..spt_values a join master..spt_values b on b.type='P' and b.number < 1000;

    /**************** end measured process *********************/ 
    INSERT @ExecutionTime 
    SELECT DurationInMilliseconds = datediff(ms,CURRENT_TIMESTAMP) 
    SET @i +=  1 
  END -- WHILE 

SELECT DurationInMilliseconds = Duration FROM   @ExecutionTime 
GO

DROP TABLE  #values 
SET NOCOUNT OFF;

解决方法

2560
2120
2080
2130
2140

但是在PK中,我在测试中发现了类似的模式以及下面的典型结果.

+--------+--------+---------+-------------------+
| @table | #table | ##table | [permanent_table] |
+--------+--------+---------+-------------------+
|   2670 |   2683 |    9603 |              9703 |
|   6823 |   6840 |    9723 |              9790 |
|   6813 |   6816 |    9626 |              9703 |
|   6883 |   6816 |    9600 |              9716 |
|   6840 |   6856 |    9610 |              9673 |
+--------+--------+---------+-------------------+

我的理论是,当对本地临时B树进行批量插入时,存在一些可用的优化,仅在它尚未分配任何页面时才适用.

我基于以下观察结果.

>运行各种版本的测试代码时,我只看到了@table_variables和#temp表的这种模式.不是tempdb中的永久表,也不是##表.
>为了获得较慢的性能,不必先从表中添加和删除大量行.只需添加一行并将其留在那里即可.
> TRUNCATE从表中取消分配所有页面. DELETE不会导致表中的最后一页被释放.
>使用VS 2012探查器显示,在更快的情况下,SQL Server使用不同的代码路径. 36％的时间花在sqlmin.dll！RowsetBulk :: InsertRow vs sqlmin.dll花费的时间的61％！RowsetNewSS :: InsertRow用于较慢的情况.

运行

SELECT * 
FROM sys.dm_db_index_physical_stats(2,OBJECT_ID('tempdb..#values'),1,NULL,'DETAILED')

删除后返回

+-------------+------------+--------------+--------------------+
| index_level | page_count | record_count | ghost_record_count |
+-------------+------------+--------------+--------------------+
|           0 |          1 |            0 |                  1 |
|           1 |          1 |            1 |                  0 |
|           2 |          1 |            1 |                  0 |
+-------------+------------+--------------+--------------------+

我发现有可能在enabling trace flag 610之间减少时间差异.

这样可以大大减少后续插入的日志记录量(从350 MB减少到103 MB,因为它不再记录单个插入的行值),但这对第二次和后续的@table的时间只有很小的改进.,#table案例和差距仍然存在.跟踪标志显着提高了插入到其他两种表类型的一般性能.

+--------+--------+---------+-------------------+
| @table | #table | ##table | [permanent_table] |
+--------+--------+---------+-------------------+
|   2663 |   2670 |    5403 |              5426 |
|   5390 |   5396 |    5410 |              5403 |
|   5373 |   5390 |    5410 |              5403 |
|   5393 |   5410 |    5406 |              5433 |
|   5386 |   5396 |    5390 |              5420 |
+--------+--------+---------+-------------------+

通过查看事务日志,我注意到针对空本地临时表的初始插入似乎记录得更少(96 MB).

（编辑：淮北站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!