主页 / dba / 问题 / 159333
Accepted
Paul White
Asked: 2016-12-29 05:20:49 +0800 CST

SQL Server 是否缓存多语句表值函数的结果？

  • 772

多语句表值函数在表变量中返回其结果。

这些结果是否曾经被重用，或者每次调用时总是对函数进行全面评估？

sql-server execution-plan

1 个回答

  1. Best Answer

    多语句表值函数 (msTVF) 的结果永远不会被缓存或跨语句（或连接）重用，但有几种方法可以在同一语句中重用 msTVF 结果。在这种情况下，不必在每次调用 msTVF 时重新填充它。

    示例 msTVF

    这个（故意低效的）msTVF 返回指定范围的整数，每行都有一个时间戳：

    IF OBJECT_ID(N'dbo.IntegerRange', 'TF') IS NOT NULL
    DROP FUNCTION dbo.IntegerRange;
GO
CREATE FUNCTION dbo.IntegerRange (@From integer, @To integer)
RETURNS @T table 
(
    n integer PRIMARY KEY, 
    ts datetime DEFAULT CURRENT_TIMESTAMP
)
WITH SCHEMABINDING
AS
BEGIN
    WHILE @From <= @To
    BEGIN
        INSERT @T (n)
        VALUES (@From);

        SET @From = @From + 1;
    END;
    RETURN;
END;

    静态表变量

    如果函数调用的所有参数都是常量（或运行时常量），则执行计划将填充表变量结果一次。计划的其余部分可能会多次访问表变量。表变量的静态性质可以从执行计划中识别出来。例如：

    SELECT
    IR.n,
    IR.ts 
FROM dbo.IntegerRange(1, 5) AS IR
ORDER BY
    IR.n;

    返回类似于以下内容的结果：

    简单的结果

    执行计划是：

    简单的执行计划

    Sequence 运算符首先调用 Table Valued Function 运算符，该运算符填充表变量（注意此运算符不返回任何行）。接下来，序列调用它的第二个输入，它返回表变量的内容（在本例中使用聚集索引扫描）。

    该计划使用“静态”表变量结果的赠品是序列下方的表值函数运算符 - 在计划的其余部分可以开始之前，表变量需要完全填充一次。

    多重访问

    为了显示表变量结果被多次访问，我们将使用第二个表，其中行编号从 1 到 5：

    IF OBJECT_ID(N'dbo.T', 'U') IS NOT NULL
    DROP TABLE dbo.T;

CREATE TABLE dbo.T (i integer NOT NULL);

INSERT dbo.T (i) 
VALUES (1), (2), (3), (4), (5);

    还有一个将这个表连接到我们的函数的新查询（这同样可以写成一个APPLY）：

    SELECT T.i,
       IR.n,
       IR.ts
FROM dbo.T AS T
JOIN dbo.IntegerRange(1, 5) AS IR
    ON IR.n = T.i;

    结果是：

    加入结果

    执行计划：

    加入计划

    和以前一样，序列首先填充表变量 msTVF 结果。接下来，使用嵌套循环将表T中的每一行连接到 msTVF 结果中的一行。由于函数定义包括对表变量有用的索引，因此可以使用索引查找。

    关键是当 msTVF 的参数是常量（包括变量和参数）或被执行引擎视为语句的运行时常量时，该计划将为 msTVF 表变量结果提供两个单独的运算符：一个填充桌子; 另一个访问结果，可能多次访问表，并可能使用函数定义中声明的索引。

    相关参数和非常量参数

    为了突出使用相关参数（外部引用）或非常量函数参数时的差异，我们将更改 table 的内容，T以便函数有更多工作要做：

    TRUNCATE TABLE dbo.T;

INSERT dbo.T (i) 
VALUES (50001), (50002), (50003), (50004), (50005);

    以下修改后的查询现在T在函数参数之一中使用对表的外部引用：

    SELECT T.i,
       IR.n,
       IR.ts
FROM dbo.T AS T
CROSS APPLY dbo.IntegerRange(1, T.i) AS IR
WHERE IR.n = T.i;

    此查询大约需要8 秒才能返回如下结果：

    相关结果

    注意 column 中各行之间的时间差ts。该WHERE子句限制了合理大小的输出的最终结果，但效率低下的函数仍然需要一段时间才能用 50,000 多行填充 table 变量（取决于ifrom table的相关值T）。

    执行计划是：

    相关执行计划

    请注意缺少序列运算符。现在，有一个表值函数运算符填充表变量并在嵌套循环连接的每次迭代中返回其行。

    需要明确的是：表 T 中只有 5 行，表值函数运算符运行 5 次。它在第一次迭代时生成 50,001 行，在第二次迭代时生成 50,002 行……以此类推。表变量在迭代之间被“丢弃”（截断），因此五个调用中的每一个都是完整的填充。这就是它如此缓慢的原因，并且每一行都需要大约相同的时间才能出现在结果中。

    旁注：

    当然，上面的场景是故意设计的，以显示当 msTVF 在每次迭代中填充许多行时性能有多差。

    上述代码的合理实现会将两个msTVF 参数设置为i，并删除冗余WHERE子句。表变量在每次迭代时仍会被截断并重新填充，但每次只有一行。

    我们还可以在前面的步骤中i从变量中获取最小值和最大值并将它们存储在变量中。T如前所述，使用变量而不是相关参数调用函数将允许使用“静态”表变量模式。

    缓存未更改的相关参数

    再次回到原始问题，在无法使用序列静态模式的情况下，如果自上一次嵌套循环连接迭代以来没有任何相关参数发生更改，SQL Server可以避免截断和重新填充 msTVF 表变量。

    为了证明这一点，我们将T用五个相同 i的值替换 的内容：

    TRUNCATE TABLE dbo.T;

INSERT dbo.T (i) 
VALUES (50005), (50005), (50005), (50005), (50005);

    再次带有相关参数的查询：

    SELECT T.i,
       IR.n,
       IR.ts
FROM dbo.T AS T
CROSS APPLY dbo.IntegerRange(1, T.i) AS IR
WHERE IR.n = T.i;

    这次结果出现在1.5 秒左右：

    相同的行结果

    注意每一行的相同时间戳。表变量中的缓存结果被重用于相关值i不变的后续迭代。重用结果比每次插入 50,005 行要快得多。

    执行计划看起来与之前非常相似：

    计划相同的行

    主要区别在于表值函数运算符的实际重新绑定和实际回绕属性：

    运算符属性

    当相关参数不变时，SQL Server 可以重放（倒回）表变量中的当前结果。当相关性发生变化时，SQL Server 必须截断并重新填充表变量（重新绑定）。一次重新绑定发生在第一次迭代中；T.i由于 的值不变，随后的四次迭代都是倒带的。

    • 26

相关问题