sql-server – 在这种特定情况下,为什么使用表变量的速度是#temp

我在看这篇文章
Temporary Tables vs. Table Variables and Their Effect on SQL Server Performance和SQL Server 2008能够再现与2005年那里显示的结果类似的结果.

当执行只有10行的存储过程(下面的定义)时,表变量版本out执行临时表版本的次数超过两次.

我清除了程序缓存并运行了两次存储过程,然后重复该过程再运行4次.以下结果(每批ms的时间)

T2_Time     V2_Time
----------- -----------
8578        2718      
6641        2781    
6469        2813   
6766        2797
6156        2719

我的问题是：表变量版本性能更好的原因是什么？

我做了一些调查.例如用性能计数器看

SELECT cntr_value
from sys.dm_os_performance_counters
where counter_name = 'Temp Tables Creation Rate';

确认在两种情况下,临时对象在第一次运行as expected之后被缓存,而不是在每次调用时从头开始再次创建.

类似地跟踪Profiler中的Auto Stats,SP：Recompile,SQL：StmtRecompileevents(下面的屏幕截图)显示这些事件只发生一次(在第一次调用#temp表存储过程时),而其他9,999次执行不会引发任何这些事件事件. (表变量版本不会获得任何这些事件)

第一次运行存储过程的稍微大一些的开销绝不能解释大的整体差异,但是因为它仍然只需要几毫秒来清除过程高速缓存并运行两个过程所以我不相信统计数据或重新编译可能是原因.

创建所需数据库对象

CREATE DATABASE TESTDB_18Feb2012;

GO

USE TESTDB_18Feb2012;

CREATE TABLE NUM 
  ( 
     n INT PRIMARY KEY,s VARCHAR(128) 
  ); 

WITH NUMS(N) 
     AS (SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY $/0) 
         FROM   master..spt_values v1,master..spt_values v2) 
INSERT INTO NUM 
SELECT N,'Value: ' + CONVERT(VARCHAR,N) 
FROM   NUMS 

GO

CREATE PROCEDURE [dbo].[T2] @total INT 
AS 
  CREATE TABLE #T 
    ( 
       n INT PRIMARY KEY,s VARCHAR(128) 
    ) 

  INSERT INTO #T 
  SELECT n,s 
  FROM   NUM 
  WHERE  n%100 > 0 
         AND n <= @total 

  DECLARE @res VARCHAR(128) 

  SELECT @res = MAX(s) 
  FROM   NUM 
  WHERE  n <= @total 
         AND NOT EXISTS(SELECT * 
                        FROM   #T 
                        WHERE  #T.n = NUM.n) 
GO

CREATE PROCEDURE [dbo].[V2] @total INT 
AS 
  DECLARE @V TABLE ( 
    n INT PRIMARY KEY,s VARCHAR(128)) 

  INSERT INTO @V 
  SELECT n,s 
  FROM   NUM 
  WHERE  n%100 > 0 
         AND n <= @total 

  DECLARE @res VARCHAR(128) 

  SELECT @res = MAX(s) 
  FROM   NUM 
  WHERE  n <= @total 
         AND NOT EXISTS(SELECT * 
                        FROM   @V V 
                        WHERE  V.n = NUM.n) 


GO

测试脚本

SET NOCOUNT ON;

DECLARE @T1 DATETIME2,@T2 DATETIME2,@T3 DATETIME2,@Counter INT = 0

SET @T1 = SYSDATETIME()

WHILE ( @Counter < 10000)
BEGIN
EXEC dbo.T2 10
SET @Counter += 1
END

SET @T2 = SYSDATETIME()
SET @Counter = 0

WHILE ( @Counter < 10000)
BEGIN
EXEC dbo.V2 10
SET @Counter += 1
END

SET @T3 = SYSDATETIME()

SELECT DATEDIFF(MILLISECOND,@T1,@T2) AS T2_Time,DATEDIFF(MILLISECOND,@T2,@T3) AS V2_Time

解决方法

SET STATISTICS IO ON;
PRINT 'V2'
EXEC dbo.V2 10
PRINT 'T2'
EXEC dbo.T2 10

给

V2
Table '#58B62A60'. Scan count 0,logical reads 20
Table 'NUM'. Scan count 1,logical reads 3

Table '#58B62A60'. Scan count 10,logical reads 3

T2
Table '#T__ ... __00000000E2FE'. Scan count 0,logical reads 3

Table '#T__ ... __00000000E2FE'. Scan count 0,logical reads 3

正如Aaron在评论中指出的那样,表变量版本的计划实际上效率较低,因为虽然两者都有一个由dbo.NUM上的索引搜索驱动的嵌套循环计划,但#temp表版本执行了对[#的索引的搜索] T] .n = [dbo].[NUM].[n]具有残差谓词[#T].[n]< = [@ total]而表变量版本对@Vn执行索引搜索< = [ @total]与剩余谓词@V.[n] = [dbo].[NUM].[n]然后处理更多行(这就是为什么这个计划对大量行表现不佳的原因) 使用Extended Events查看特定spid的等待类型,可以为10,000次EXEC执行结果dbo.T2 10

+---------------------+------------+----------------+----------------+----------------+
|                     |            |     Total      | Total Resource |  Total Signal  |
| Wait Type           | Wait Count | Wait Time (ms) | Wait Time (ms) | Wait Time (ms) |
+---------------------+------------+----------------+----------------+----------------+
| SOS_SCHEDULER_YIELD | 16         | 19             | 19             | 0              |
| PAGELATCH_SH        | 39998      | 14             | 0              | 14             |
| PAGELATCH_EX        | 1          | 0              | 0              | 0              |
+---------------------+------------+----------------+----------------+----------------+

这些结果是10,000次执行EXEC dbo.V2 10

+---------------------+------------+----------------+----------------+----------------+
|                     |            |     Total      | Total Resource |  Total Signal  |
| Wait Type           | Wait Count | Wait Time (ms) | Wait Time (ms) | Wait Time (ms) |
+---------------------+------------+----------------+----------------+----------------+
| PAGELATCH_EX        | 2          | 0              | 0              | 0              |
| PAGELATCH_SH        | 1          | 0              | 0              | 0              |
| SOS_SCHEDULER_YIELD | 676        | 0              | 0              | 0              |
+---------------------+------------+----------------+----------------+----------------+

（编辑：青岛站长网）

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