SQL Prompt教程：在表变量和临时表之间进行选择-控件新闻-慧都网

SQL Prompt教程：在表变量和临时表之间进行选择

翻译|使用教程|编辑：杨鹏连|2020-12-09 10:40:43.720|阅读 215 次

概述：Phil Factor演示了临时表和表变量的用法，并提供了一些简单的规则来确定表变量是否比临时表（ST011）更好，反之亦然（ST012）。

相关链接：

SQL Prompt是一款实用的SQL语法提示工具。SQL Prompt根据数据库的对象名称、语法和代码片段自动进行检索，为用户提供合适的代码选择。自动脚本设置使代码简单易读--当开发者不大熟悉脚本时尤其有用。SQL Prompt安装即可使用，能大幅提高编码效率。此外，用户还可根据需要进行自定义，使之以预想的方式工作。

点击下载SQL Prompt正式版

人们可以并且确实对表变量和临时表的相对优点争论不休。有时，就像编写函数时一样，您别无选择。但是当您这样做时，您会发现两者都有其用途，并且很容易找到其中一个速度更快的示例。在本文中，我将解释选择一个或另一个涉及的主要因素，并演示一些简单的“规则”以获得最佳性能。

假设您遵循基本的接触规则，那么在使用相对较小的数据集时，应将表变量视为首选。与使用临时表相比，它们更易于使用，并且在使用它们的例程中触发更少的重新编译。表变量还需要较少的锁定资源，因为它们是创建它们的过程和批处理的“专用”资源。SQL Prompt将此建议作为代码分析规则ST011实施-考虑使用表变量而不是临时表。

如果您要对临时数据进行更复杂的处理，或者需要使用其中的少量数据，那么本地临时表可能是一个更好的选择。根据他的建议，SQL Code Guard包含一个代码分析规则ST012 –考虑使用临时表代替表变量，但是SQL Prompt当前未实现。

表变量和临时表的优缺点

表变量趋向于“糟糕”，因为使用它们的查询偶尔会导致执行计划效率低下。但是，如果遵循一些简单的规则，它们对于中间“工作”表以及在例程之间传递结果是个不错的选择，在常规例程中数据集很小，所需的处理相对简单。

表变量的使用非常简单，主要是因为它们是“零维护”。它们的作用域仅限于创建它们的批处理或例程，一旦完成执行便会自动删除，因此在长期存在的连接中使用它们不会冒着tempdb中“资源占用”问题的风险。如果在存储过程中声明了表变量，则该表变量是该存储过程的本地变量，并且不能在嵌套过程中引用。表变量也没有基于统计信息的重新编译，因此您不能ALTER一种，因此使用它们的例程比使用临时表的例程倾向于更少的重新编译。它们也没有完全记录，因此创建和填充它们的速度更快，并且在事务日志中需要的空间更少。在存储过程中使用它们时，在高并发条件下，系统表上的争用较少。简而言之，保持事物整洁更容易。

当使用相对较小的数据集时，它们比类似的临时表快。但是，随着行数的增加（超过大约15,000行，但根据上下文而变化），您可能会遇到困难，这主要是由于它们缺乏对统计的支持。即使对表变量强制执行PRIMARY KEY和UNIQUE约束的索引也没有统计信息。因此，优化器将使用从表变量返回的1行的硬编码估计，因此倾向于选择最适合处理小型数据集（例如嵌套循环）的运算符。联接运算符）。表变量中的行越多，估计与实际之间的差异就越大，并且成为优化器计划选择的效率越低。最终的计划有时是可怕的。

有经验的开发人员或DBA会寻找此类问题，并准备将OPTION (RECOMPILE)查询提示添加到使用表变量的语句中。当我们提交包含表变量的批处理时，优化器将首先编译该批处理，此时表变量为空。当批处理开始执行时，提示将仅导致重新编译单个语句，此时将填充表变量，优化器可以使用实际行数为该语句编译新的计划。有时，但很少，即使这样也无济于事。同样，过度依赖此提示将在某种程度上抵消表变量比临时表具有更少的重新编译优势。

其次，在处理大型数据集时，表变量的某些索引限制变得更加重要。现在，您可以使用内联索引创建语法在表变量上创建非聚集索引，但是存在一些限制，并且仍然没有关联的统计信息。

即使行数相对较少，但如果您尝试执行作为联接的查询，也可能会遇到查询性能问题，而忘记了在用于联接的列上定义PRIMARY KEY或UNIQUE约束。没有它们提供的元数据，优化器将不知道数据的逻辑顺序，也不知道联接列中的数据是否包含重复值，并且可能会选择效率低下的联接操作，从而导致查询缓慢。如果使用表变量堆，则只能使用一个简单列表，该列表很可能在单个gulp中处理（表扫描）。如果您同时使用OPTION (RECOMPILE) 提示，准确的基数估计以及连接列上的键（可为优化器提供有用的元数据），然后对于较小的数据集，您通常可以达到与使用本地临时表相似或更好的查询速度。

一旦行数增加到表变量的允许范围之外，或者您需要执行更复杂的数据处理，那么最好切换为使用临时表。在这里，您可以使用完整的选项来建立索引，并且优化器将可以为每个索引使用统计信息。当然，缺点是临时表的维护成本较高。您需要确保自己清理一下，以避免tempdb拥塞。如果更改临时表或修改临时表中的数据，则可能会导致父例程的重新编译。

当需要大量删除和插入（行集共享）时，临时表会更好。如果必须从表中完全删除数据，尤其是这样，因为只有临时表支持截断。如果数据易变，则表变量设计中的折衷办法（例如缺乏统计信息和重新编译）会不利于它们。

何时需要使用表变量

我们将从一个表变量理想的示例开始，它可以带来更好的性能。我们将列出Adventureworks的员工列表，他们工作的部门以及工作班次。我们正在处理一个小的数据集（291行）。

我们会将结果放入第二个临时表中，就像将结果传递给下一批一样。清单1显示了代码。

USE AdventureWorks2016;
--initialise out timer
DECLARE @log TABLE (TheOrder INT IDENTITY(1,1), 
WhatHappened varchar(200), WHENItDid  Datetime2 DEFAULT GETDATE())
 
 
CREATE TABLE #employees
  (Employee NATIONAL CHARACTER VARYING(500) NOT NULL);
----start of timing
INSERT INTO @log(WhatHappened) 
SELECT 'Starting My_Section_of_code'--place at the start
 
--start by using a table variable for workpad
DECLARE @WorkPad TABLE
  (NameOfEmployee NATIONAL CHARACTER VARYING(100) NOT NULL,
BusinessEntityID INT PRIMARY KEY NOT NULL,
NationalIDNumber NATIONAL CHARACTER VARYING(15) NOT NULL);
 
INSERT INTO @WorkPad
  (NameOfEmployee, BusinessEntityID, NationalIDNumber)
  SELECT Coalesce(Person.Title + ' ', '') + Person.FirstName + ' '
         + Coalesce(Person.MiddleName + ' ', '') + Person.LastName
         + ': ' + Coalesce(Person.Suffix, '') + Employee.JobTitle,
    Employee.BusinessEntityID, Employee.NationalIDNumber
    FROM HumanResources.Employee
      INNER JOIN Person.Person
        ON Person.BusinessEntityID = Employee.BusinessEntityID;
 
INSERT INTO #Employees(Employee)
  SELECT TheList.NameOfEmployee + ' - '
         + Coalesce(
             Stuff(
               (SELECT ', ' + Department.Name + ' ('
                       + Department.GroupName + ') '
                       + Convert(CHAR(5), Shift.StartTime) + ' to '
                       + Convert(CHAR(5), Shift.EndTime)
                  FROM HumanResources.EmployeeDepartmentHistory
                    INNER JOIN HumanResources.Department
                      ON Department.DepartmentID = EmployeeDepartmentHistory.DepartmentID
                    INNER JOIN HumanResources.Shift
                      ON Shift.ShiftID = EmployeeDepartmentHistory.ShiftID
                  WHERE EmployeeDepartmentHistory.BusinessEntityID =
                            TheList.BusinessEntityID
               FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),  1,2,''),'?') 
                            AS Department
    FROM @WorkPad TheList;
INSERT INTO @log(WhatHappened) SELECT 'The use of a Table Variable took '--where the routine you want to time ends
 
--now use a temp table for workpad instead
CREATE TABLE #WorkPad
  (NameOfEmployee NATIONAL CHARACTER VARYING(100) NOT NULL,
BusinessEntityID INT PRIMARY KEY NOT NULL,
NationalIDNumber NATIONAL CHARACTER VARYING(15) NOT NULL);
 
INSERT INTO #WorkPad
  (NameOfEmployee, BusinessEntityID, NationalIDNumber)
  SELECT Coalesce(Person.Title + ' ', '') + Person.FirstName + ' '
         + Coalesce(Person.MiddleName + ' ', '') + Person.LastName
         + ': ' + Coalesce(Person.Suffix, '') + Employee.JobTitle,
    Employee.BusinessEntityID, Employee.NationalIDNumber
    FROM HumanResources.Employee
      INNER JOIN Person.Person
        ON Person.BusinessEntityID = Employee.BusinessEntityID;
 
INSERT INTO #Employees(Employee)
  SELECT TheList.NameOfEmployee + ' - '
    + Coalesce(
        Stuff(
           (SELECT ', ' + Department.Name + ' ('
              + Department.GroupName + ') '
              + Convert(CHAR(5), Shift.StartTime) + ' to '
              + Convert(CHAR(5), Shift.EndTime)
            FROM HumanResources.EmployeeDepartmentHistory
              INNER JOIN HumanResources.Department
                ON Department.DepartmentID = EmployeeDepartmentHistory.DepartmentID
              INNER JOIN HumanResources.Shift
                ON Shift.ShiftID = EmployeeDepartmentHistory.ShiftID
            WHERE EmployeeDepartmentHistory.BusinessEntityID =
                       TheList.BusinessEntityID
            FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),  1,2,''),'?') 
                         AS Department
    FROM #WorkPad TheList;
INSERT INTO @log(WhatHappened) 
  SELECT 'The use of a temporary Table took '--where the routine you want to time ends
DROP TABLE #Employees
DROP  TABLE #WorkPad
 
/* now we see how long each took. */
SELECT ending.WhatHappened, DateDiff(ms, starting.WHENItDid, ending.WHENItDid) AS ms
  FROM @log AS starting
    INNER JOIN @log AS ending
      ON ending.TheOrder = starting.TheOrder + 1;
--list out all the timings

这是我的慢速测试机器上的典型结果：

尽管临时运行的差异可能很大，但使用临时表的速度始终较慢。

规模问题和忘记提供关键或提示

如果我们联接两个表变量，性能如何？让我们尝试一下。在此示例中，我们需要两个简单的表，一个表包含英语中的所有常用单词（CommonWords），另一个表包含Bram Stoker的“ Dracula”中的所有单词的列表（WordsInDracula）。该TestTVsAndTTs下载包括脚本来创建这两个表，并填充和与之相关的文本文件中每一个。有60,000个常用词，但Bram Stoker仅使用了10,000个。前者远未达到收支平衡点，在那里人们开始偏爱临时表。

我们将使用四个简单的外部联接查询，测试结果的NULL值，以查找不存在于德古拉中的常见单词，不存在于德古拉中的常见单词，不存在于德古拉中的单词，最后是另一个查询以查找在德古拉语中很常见，但方向相反。当我显示测试装备的代码时，您很快就会看到查询。

以下是初始测试运行的结果。在第一次运行中，两个表变量都具有主键，而在第二次运行中，它们都是堆，只是为了查看我是否在夸大未在表变量中声明索引的问题。最后，我们对临时表运行相同的查询。出于说明目的，所有测试都故意在缓慢的开发服务器上运行；使用生产服务器，您将获得截然不同的结果。

结果表明，当表变量是堆时，您冒着查询运行10分钟而不是100毫秒的风险。这些举了一个很好的例子，说明了如果您不了解规则，就会遇到可怕的表现。即使使用主键，我们要处理的行数也意味着使用临时表的速度现在快一倍。

除了对主要差异进行一些广泛的解释之外，我不会深入研究这些绩效指标背后的执行计划的细节。对于临时表查询，优化器具有对基数和主键约束中的元数据的全面了解，因此选择了有效的“合并联接”运算符来执行联接操作。对于具有主键的表变量，优化器知道连接列中行的顺序，并且它们不包含重复项，但假定它仅处理一行，因此改为选择嵌套循环加入。在这里，它扫描一个表，然后针对返回的每一行执行另一表的单独查找。数据集越大，效率越低，并且在扫描CommonWords表变量的情况下尤其不利，因为这会导致对表变量的搜索超过60K Dracula。该嵌套循环联接达到“峰值效率”使用表变量堆二，十分钟的查询，因为它涉及数千表扫描CommonWords。有趣的是，这两个“德古拉中的常用单词”查询的性能要好得多，这是因为对于这两个查询，优化器选择了哈希匹配联接。

总体而言，临时表似乎是最佳选择，但我们还没有完成！让我们OPTION (RECOMPILE)向使用带有主键的表变量的查询添加提示，然后针对这些查询以及使用临时表的原始查询重新运行测试。我们暂时不去那些可怜的堆。

如您所见，临时表的性能优势消失了。有了正确的行数和有序的输入，优化器选择了效率更高的Merge Join。

如果您也给那些可怜的人OPTION (RECOMPILE)暗示，会发生什么呢？瞧，故事为他们而改变，所以所有三个时机都更加接近。

有趣的是，即使在堆上也很快速的两个“德古拉常用词”查询现在要慢得多。拥有正确的行数后，优化器会更改其策略，但是由于在定义约束和键时它仍然没有可用的有用元数据，因此，它是一个错误的选择。它扫描CommonWords堆，然后尝试“部分聚合”，估计它将从6万行聚合到几百行。它不知道没有重复项，因此实际上它根本不会聚合下来，并且聚合和随后的联接会溢出到tempdb。

试验台

请注意，这是最终形式的测试台，显示了三种不同类型表的大致相同的性能。您将需要删除OPTION (RECOMPILE)提示以恢复原始状态。

USE PhilFactor;
--create the working table with all the words from Dracula in it
DECLARE @WordsInDracula TABLE
  (word VARCHAR(40) NOT NULL PRIMARY KEY CLUSTERED);
 
INSERT INTO @WordsInDracula(word) SELECT WordsInDracula.word FROM dbo.WordsInDracula;
 
--create the other working table with all the common words in it
DECLARE @CommonWords TABLE
  (word VARCHAR(40) NOT NULL PRIMARY KEY CLUSTERED);
 
INSERT INTO @CommonWords(word) SELECT commonwords.word FROM dbo.commonwords;
 
--create a timing log 
DECLARE @log TABLE
  (TheOrder INT IDENTITY(1, 1),
WhatHappened VARCHAR(200),
WhenItDid DATETIME2 DEFAULT GetDate());
 
----start of the timing (never reported)
INSERT INTO @log(WhatHappened) SELECT 'Starting My_Section_of_code';
 
--place at the start
---------------section of code using table variables
--first timed section of code using table variables
SELECT Count(*) AS [common words not in Dracula]
  FROM @CommonWords AS c
    LEFT OUTER JOIN @WordsInDracula AS d
      ON d.word = c.word
  WHERE d.word IS NULL
OPTION(RECOMPILE);
 
INSERT INTO @log(WhatHappened)
  SELECT 'common words not in Dracula: Both table variables with primary keys ';
--where the routine you want to time ends
 
--Second timed section of code using table variables
SELECT Count(*) AS [common words in Dracula]
  FROM @CommonWords AS c
    LEFT OUTER JOIN @WordsInDracula AS d
      ON d.word = c.word
  WHERE d.word IS NOT NULL
OPTION(RECOMPILE);
 
INSERT INTO @log(WhatHappened)
  SELECT 'common words in Dracula: Both table variables with primary keys ';
--where the routine you want to time ends
 
--third timed section of code using table variables
SELECT Count(*) AS [uncommon words  in Dracula ]
  FROM @WordsInDracula AS d
    LEFT OUTER JOIN @CommonWords AS c
      ON d.word = c.word
  WHERE c.word IS NULL
OPTION(RECOMPILE);
 
INSERT INTO @log(WhatHappened)
  SELECT 'uncommon words in Dracula: Both table variables with primary keys ';
--where the routine you want to time ends
 
--last timed section of code using table variables
SELECT Count(*) AS [common words  in Dracula ]
  FROM @WordsInDracula AS d
    LEFT OUTER JOIN @CommonWords AS c
      ON d.word = c.word
  WHERE c.word IS NOT NULL
OPTION(RECOMPILE);
 
INSERT INTO @log(WhatHappened)
  SELECT 'more common words in Dracula: Both table variables with primary keys ';
--where the routine you want to time ends
 
---------------section of code using heap variables
DECLARE @WordsInDraculaHeap TABLE(word VARCHAR(40) NOT NULL);
 
INSERT INTO @WordsInDraculaHeap(word) SELECT WordsInDracula.word FROM dbo.WordsInDracula;
 
DECLARE @CommonWordsHeap TABLE(word VARCHAR(40) NOT NULL);
 
INSERT INTO @CommonWordsHeap(word) SELECT commonwords.word FROM dbo.commonwords;
 
INSERT INTO @log(WhatHappened) SELECT 'Test Rig Setup ';
--where the routine you want to time ends
 
--first timed section of code using heap variables
SELECT Count(*) AS [common words not in Dracula]
  FROM @CommonWordsHeap AS c
    LEFT OUTER JOIN @WordsInDraculaHeap AS d
      ON d.word = c.word
  WHERE d.word IS NULL
OPTION(RECOMPILE);
 
INSERT INTO @log(WhatHappened) SELECT 'common words not in Dracula: Both Heaps ';
--where the routine you want to time ends
 
--second timed section of code using heap variables
SELECT Count(*) AS [common words in Dracula]
  FROM @CommonWordsHeap AS c
    LEFT OUTER JOIN @WordsInDraculaHeap AS d
      ON d.word = c.word
  WHERE d.word IS NOT NULL
OPTION(RECOMPILE);
 
INSERT INTO @log(WhatHappened) SELECT 'common words in Dracula: Both Heaps ';
--where the routine you want to time ends
 
--third timed section of code using heap variables
SELECT Count(*) AS [uncommon words  in Dracula ]
  FROM @WordsInDraculaHeap AS d
    LEFT OUTER JOIN @CommonWordsHeap AS c
      ON d.word = c.word
  WHERE c.word IS NULL
OPTION(RECOMPILE);
 
INSERT INTO @log(WhatHappened) SELECT 'uncommon words in Dracula: Both Heaps ';
--where the routine you want to time ends
 
--last timed section of code using heap variables
SELECT Count(*) AS [common words  in Dracula ]
  FROM @WordsInDraculaHeap AS d
    LEFT OUTER JOIN @CommonWordsHeap AS c
      ON d.word = c.word
  WHERE c.word IS NOT NULL
OPTION(RECOMPILE);
 
INSERT INTO @log(WhatHappened) SELECT 'common words in Dracula: Both Heaps ';
--where the routine you want to time ends
 
---------------section of code using Temporary tables
CREATE TABLE #WordsInDracula (word VARCHAR(40) NOT NULL PRIMARY KEY);
 
INSERT INTO #WordsInDracula(word) SELECT WordsInDracula.word FROM dbo.WordsInDracula;
 
CREATE TABLE #CommonWords (word VARCHAR(40) NOT NULL PRIMARY KEY);
 
INSERT INTO #CommonWords(word) SELECT commonwords.word FROM dbo.commonwords;
 
INSERT INTO @log(WhatHappened) SELECT 'Temp Table Test Rig Setup ';
--where the routine you want to time ends
 
--first timed section of code using Temporary tables
SELECT Count(*) AS [common words not in Dracula]
  FROM #CommonWords AS c
    LEFT OUTER JOIN #WordsInDracula AS d
      ON d.word = c.word
  WHERE d.word IS NULL;
 
INSERT INTO @log(WhatHappened) SELECT 'common words not in Dracula: Both Temp Tables ';
--where the routine you want to time ends
 
--Second timed section of code using Temporary tables
SELECT Count(*) AS [common words in Dracula]
  FROM #CommonWords AS c
    LEFT OUTER JOIN #WordsInDracula AS d
      ON d.word = c.word
  WHERE d.word IS NOT NULL;
 
INSERT INTO @log(WhatHappened) SELECT 'common words in Dracula: Both Temp Tables ';
--where the routine you want to time ends
 
--third timed section of code using Temporary tables
SELECT Count(*) AS [uncommon words  in Dracula ]
  FROM #WordsInDracula AS d
    LEFT OUTER JOIN #CommonWords AS c
      ON d.word = c.word
  WHERE c.word IS NULL;
 
INSERT INTO @log(WhatHappened) SELECT 'uncommon words in Dracula:Both Temp Tables ';
--where the routine you want to time ends
 
--last timed section of code using Temporary tables
SELECT Count(*) AS [common words  in Dracula ]
  FROM #WordsInDracula AS d
    LEFT OUTER JOIN #CommonWords AS c
      ON d.word = c.word
  WHERE c.word IS NOT NULL;
 
INSERT INTO @log(WhatHappened) SELECT 'common words in Dracula: Both Temp Tables '; --where the routine you want to time ends
 
DROP TABLE #WordsInDracula;
DROP TABLE #CommonWords;
 
SELECT ending.WhatHappened AS [The test that was run],
  DateDiff(ms, starting.WhenItDid, ending.WhenItDid) AS [Time Taken (Ms)]
  FROM @log AS starting
    INNER JOIN @log AS ending
      ON ending.TheOrder = starting.TheOrder + 1;
--list out all the timings

清单2

结论

使用表变量没有什么鲁ck的事情。当用于预期目的时，它们可以提供更好的性能，并且可以自行清理。在某个时候，让他们获得更好性能的妥协（不触发重新编译，不提供统计信息，不回滚，不并行）成为他们的失败。

通常，SQL Server专家会就结果的大小提供一些明智的建议，这将导致表变量出现问题。我在本文中向您显示的结果将建议您过分简化问题。有两个重要因素：如果结果超过了，比如说1000行（该数字取决于上下文），那么对于连接到表变量的任何查询，都需要具有PRIMARY KEY或UNIQUE键。在某个时候，您还需要触发重新编译以获得一个体面的执行计划，该计划有其自身的开销。

即使这样，性能也会受到严重影响，尤其是在执行更复杂的处理时，因为优化器仍然无法访问统计信息，因此也不了解任何查询谓词的选择性。在这种情况下，您需要切换到使用临时表。

试用下载>>>

SQL Prompt 使用教程>>>

想要购买SQL Prompt正版授权，或了解更多产品信息请点击

标签：

本站文章除注明转载外，均为本站原创或翻译。欢迎任何形式的转载，但请务必注明出处、不得修改原文相关链接，如果存在内容上的异议请邮件反馈至chenjj@capbkgr.cn

文章转载自：

上一篇：PDF处理控件Aspose.PDF功能演示：使用Java从PDF文档中提取文本下一篇：VS/MFC编程入门：应用程序工程中文件的组成结构