性能动态SQL与临时表

Question

我想知道，与动态SQL相比，将现有的表复制到临时表中是否会导致较差的性能。

具体地说，我想知道以下两个SQL Server存储过程之间是否应该有不同的性能：

    CREATE PROCEDURE UsingDynamicSQL
    (
      @ID INT ,
      @Tablename VARCHAR(100)
    )
 AS
    BEGIN
        DECLARE @SQL VARCHAR(MAX)
        SELECT  @SQL = 'Insert into Table2 Select Sum(ValColumn) From '
                + @Tablename + ' Where ID=' + @ID
        EXEC(@SQL)
    END

    CREATE PROCEDURE UsingTempTable
        (
          @ID INT ,
          @Tablename Varachar(100)
        )
    AS
        BEGIN
            Create Table #TempTable (ValColumn float, ID int)
            DECLARE @SQL VARCHAR(MAX)
            SELECT  @SQL = 'Select ValColumn, ID From ' + @Tablename
                    + ' Where ID=' + @ID
            INSERT  INTO #TempTable
                    EXEC ( @SQL );
            INSERT  INTO Table2
                    SELECT  SUM(ValColumn)
                    FROM    #TempTable;
            DROP TABLE #TempTable;
        END

我之所以问这个问题，是因为我目前使用的是后一种风格的过程构建，我在开始时创建了许多临时表，作为现有表的简单提取，然后使用这些临时表。我是否可以通过去掉临时表并使用动态SQL来提高存储过程的性能？在我看来，动态SQL版本的编程要难看得多，因此我首先使用了临时表。

Answer 1

表变量会遇到性能问题，因为查询优化器总是假设其中只有一行。如果有超过100行的表变量，我会将它们切换到临时表。

使用带有EXEC(@sql)而不是exec sp_executesql @sql的dynamic sql将阻止缓存查询计划，这可能会影响性能。

但是，您对这两个查询都使用了动态sql。唯一的区别是，第二个查询有不必要的步骤，首先加载到表变量，然后加载到最终的表中。使用您拥有的第一个存储过程，但切换到sp_executesql。

Answer 2

在posted查询中，临时表是额外的写操作。

这是不会有帮助的。

不要只是给查询计时，要看查询计划。

如果您有两个查询，查询计划将告诉您拆分。

并且表变量和临时表之间存在差异

临时表更快-查询优化器对临时表执行的操作更多

临时表在某些情况下会有所帮助

1. select的输出将被多次使用

您将输出具体化，以便它只执行一次

您看到的是一个昂贵的CTE，该CTE经过多次评估

人们错误地认为CTE只执行一次-不，它只是语法

• 查询优化器需要帮助。

一个例子

您正在对具有多个条件的大型表执行自连接，其中一些条件消除了大部分行

对#temp的查询可以过滤行，还可以减少的连接条件数

Answer 3

我同意其他人的观点，你总是需要同时测试两个……我把它放在这里的答案中，这样就更清楚了。

如果您有一个非常适合最终查询的索引设置，那么转到临时表可能只是额外的工作。

如果不是这样，预过滤到临时表可能会更快，也可能不会。

您可以在极端情况下预测它-如果您从一百万行筛选到十几行，我敢打赌它会有所帮助。

但除此之外，不去尝试就很难知道。

我同意您的观点，维护也是一个问题，大量的动态sql是需要考虑的维护成本。