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).

（编辑：湖南网）

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