最后是一个测试脚本,用于比较@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;
解决方法
当对象是B树时,这种差异似乎只适用.删除表变量上的主键,因此它是一个堆,我得到了以下结果
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).
值得注意的是,这些更快的插入只有657个事务(LOP_BEGIN_XACT / LOP_COMMIT_XACT对),而在较慢的情况下超过10,000.特别是LOP_FORMAT_PAGE操作似乎大大减少了.对于表中的每个页面(约10,270),较慢的情况具有针对此的事务日志条目,而在快速情况下仅有4个这样的条目.
在所有三种情况下使用的日志如下(我已删除日志记录以更新系统基表以减少文本量,但它们仍包含在总计中)
记录针对@table_var的第一个插入(96.5 MB)
记录后续插入TF 610关闭(350 MB)
+-----------------------+----------+----------------------------------------------+---------------+---------+ | Operation | Context | AllocUnitName | Size in Bytes | Cnt | +-----------------------+----------+----------------------------------------------+---------------+---------+ | LOP_BEGIN_XACT | LCX_NULL | NULL | 83876 | 658 | | LOP_COMMIT_XACT | LCX_NULL | NULL | 34164 | 657 | | LOP_CREATE_ALLOCCHAIN | LCX_NULL | NULL | 120 | 3 | | LOP_FORMAT_PAGE | LCX_HEAP | dbo.#531856C7 | 84 | 1 | | LOP_FORMAT_PAGE | LCX_IAM | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 | 84 | 1 | | LOP_FORMAT_PAGE | LCX_IAM | dbo.#531856C7 | 84 | 1 | | LOP_FORMAT_PAGE | LCX_IAM | Unknown Alloc Unit | 84 | 1 | | LOP_HOBT_DDL | LCX_NULL | NULL | 216 | 6 | | LOP_HOBT_DELTA | LCX_NULL | NULL | 320 | 5 | | LOP_IDENT_NEWVAL | LCX_NULL | NULL | 100240000 | 25记录后续插入TF 610(103 MB)
2560 2120 2080 2130 2140|2560 2120 2080 2130 2140
| LOP_INSERT_ROWS | LCX_HEAP | dbo.#531856C7 | 72 | 1 |
| LOP_MODIFY_ROW | LCX_IAM | dbo.#531856C7 | 88 | 1 |
| LOP_MODIFY_ROW | LCX_PFS | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 | 158592 | 1848 |
| LOP_MODIFY_ROW | LCX_PFS | dbo.#531856C7 | 80 | 1 |
| LOP_MODIFY_ROW | LCX_PFS | Unknown Alloc Unit | 216016 | 2455 |
| LOP_SET_BITS | LCX_GAM | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 | 84360 | 1406 |
| LOP_SET_BITS | LCX_GAM | Unknown Alloc Unit | 147120 | 2452 |
| LOP_SET_BITS | LCX_IAM | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 | 84360 | 1406 |
| LOP_SET_BITS | LCX_IAM | Unknown Alloc Unit | 147120 | 2452 |
| Total | NULL | NULL | 101209792 | 2519475 |
+-----------------------+----------+----------------------------------------------+---------------+---------+
+-----------------------+--------------------+----------------------------------------------+---------------+---------+ | Operation | Context | AllocUnitName | Size in Bytes | Cnt | +-----------------------+--------------------+----------------------------------------------+---------------+---------+ | LOP_BEGIN_CKPT | LCX_NULL | NULL | 96 | 1 | | LOP_BEGIN_XACT | LCX_NULL | NULL | 1520696 | 12521 | | LOP_COMMIT_XACT | LCX_NULL | NULL | 651040 | 12520 | | LOP_CREATE_ALLOCCHAIN | LCX_NULL | NULL | 40 | 1 | | LOP_DELETE_SPLIT | LCX_INDEX_INTERIOR | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 | 2160 | 36 | | LOP_END_CKPT | LCX_NULL | NULL | 136 | 1 | | LOP_FORMAT_PAGE | LCX_HEAP | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 | 859236 | 10229 | | LOP_FORMAT_PAGE | LCX_IAM | Unknown Alloc Unit | 84 | 1 | | LOP_FORMAT_PAGE | LCX_INDEX_INTERIOR | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 | 3108 | 37 | | LOP_HOBT_DDL | LCX_NULL | NULL | 648 | 18 | | LOP_HOBT_DELTA | LCX_NULL | NULL | 657088 | 10267 | | LOP_IDENT_NEWVAL | LCX_NULL | NULL | 100239960 | 2505999 | | LOP_INSERT_ROWS | LCX_CLUSTERED | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 | 258628000 | 252560 2120 2080 2130 2140|2560 2120 2080 2130 2140
| LOP_INSERT_ROWS | LCX_HEAP | dbo.#531856C7 | 72 | 1 |
| LOP_INSERT_ROWS | LCX_INDEX_INTERIOR | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 | 1042776 | 10302 |
| LOP_MODIFY_HEADER | LCX_HEAP | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 | 859236 | 10229 |
| LOP_MODIFY_HEADER | LCX_INDEX_INTERIOR | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 | 3192 | 38 |
| LOP_MODIFY_ROW | LCX_IAM | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 | 704 | 8 |
| LOP_MODIFY_ROW | LCX_PFS | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 | 934264 | 11550 |
| LOP_MODIFY_ROW | LCX_PFS | Unknown Alloc Unit | 783984 | 8909 |
| LOP_SET_BITS | LCX_GAM | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 | 76980 | 1283 |
| LOP_SET_BITS | LCX_GAM | Unknown Alloc Unit | 534480 | 8908 |
| LOP_SET_BITS | LCX_IAM | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 | 76980 | 1283 |
| LOP_SET_BITS | LCX_IAM | Unknown Alloc Unit | 534480 | 8908 |
| LOP_SHRINK_NOOP | LCX_NULL | NULL | 32 | 1 |
| LOP_XACT_CKPT | LCX_NULL | NULL | 92 | 1 |
| Total | NULL | NULL | 367438748 | 5119297 |
+-----------------------+--------------------+----------------------------------------------+---------------+---------+
+-------------------------+-------------------------+----------------------------------------------+---------------+---------+ | Operation | Context | AllocUnitName | Size in Bytes | Cnt | +-------------------------+-------------------------+----------------------------------------------+---------------+---------+ | LOP_BEGIN_CKPT | LCX_NULL | NULL | 192 | 2 | | LOP_BEGIN_XACT | LCX_NULL | NULL | 1339796 | 11099 | | LOP_BULK_EXT_ALLOCATION | LCX_NULL | NULL | 20616 | 162 | | LOP_COMMIT_XACT | LCX_NULL | NULL | 577096 | 11098 | | LOP_CREATE_ALLOCCHAIN | LCX_NULL | NULL | 40 | 1 | | LOP_DELETE_SPLIT | LCX_INDEX_INTERIOR | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 | 2160 | 36 | | LOP_END_CKPT | LCX_NULL | NULL | 272 | 2 | | LOP_FORMAT_PAGE | LCX_BULK_OPERATION_PAGE | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 | 863520 | 10280 | | LOP_FORMAT_PAGE | LCX_IAM | Unknown Alloc Unit | 84 | 1 | | LOP_FORMAT_PAGE | LCX_INDEX_INTERIOR | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 | 3108 | 37 | | LOP_HOBT_DELTA | LCX_NULL | NULL | 666496 | 10414 | | LOP_IDENT_NEWVAL | LCX_NULL | NULL | 100239960 | 2505999 | | LOP_INSERT_ROWS | LCX_CLUSTERED | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 | 23544 | 218 | | LOP_INSERT_ROWS | LCX_HEAP | dbo.#719CDDE7 | 72 | 1 | | LOP_INSERT_ROWS | LCX_INDEX_INTERIOR | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 | 1042776 | 10302 | | LOP_MODIFY_HEADER | LCX_BULK_OPERATION_PAGE | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 | 780216 | 10266 | | LOP_MODIFY_HEADER | LCX_HEAP | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 | 1718472 | 20458 | | LOP_MODIFY_HEADER | LCX_INDEX_INTERIOR | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 | 3192 | 38 | | LOP_MODIFY_ROW | LCX_IAM | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 | 704 | 8 | | LOP_MODIFY_ROW | LCX_PFS | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 | 114832 | 1307 | | LOP_MODIFY_ROW | LCX_PFS | Unknown Alloc Unit | 231696 | 2633 | | LOP_RANGE_INSERT | LCX_NULL | NULL | 48 | 1 | | LOP_SET_BITS | LCX_GAM | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 | 77100 | 1285 | | LOP_SET_BITS | LCX_GAM | Unknown Alloc Unit | 157920 | 2632 | | LOP_SET_BITS | LCX_IAM | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 | 77100 | 1285 | | LOP_SET_BITS | LCX_IAM | Unknown Alloc Unit | 157920 | 2632 | | LOP_XACT_CKPT | LCX_NULL | NULL | 92 | 1 | | Total | NULL | NULL | 108102960 | 2602218 | +-------------------------+-------------------------+----------------------------------------------+---------------+---------+
