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              |
+---------------------+------------+----------------+----------------+----------------+

（编辑：湖南网）

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