解决方法
示例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;
静态表变量
如果函数调用的所有参数都是常量(或运行时常量),则执行计划将填充表变量result一次.计划的其余部分可以多次访问表变量.可以从执行计划中识别表变量的静态特性.例如：
SELECT
IR.n,IR.ts
FROM dbo.IntegerRange(1,5) AS IR
ORDER BY
IR.n;
返回类似于的结果：
执行计划是：
Sequence运算符首先调用Table Valued Function运算符,该运算符填充表变量(注意此运算符不返回任何行).接下来,Sequence调用其第二个输入,该输入返回表变量的内容(在这种情况下使用Clustered Index Scan).
计划使用“静态”表变量结果的赠品是序列下面的表值函数运算符 – 表变量需要在计划的其余部分开始之前完全填充一次.
多次访问
要显示多次访问的表变量结果,我们将使用第二个表,其中行的编号从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;
结果是：
执行计划：
和以前一样,Sequence首先填充表变量msTVF结果.接下来,嵌套循环用于将表T中的每一行连接到msTVF结果中的一行.由于函数定义包含对表变量的有用索引,因此可以使用索引查找.
关键点是当msTVF的参数是常量(包括变量和参数)或被执行引擎视为语句的运行时常量时,该计划将为msTVF表变量结果提供两个独立的运算符：一个填充桌子;另一个访问结果,可能多次访问表,并可能使用函数定义中声明的索引.
相关参数和非常数参数
要在使用相关参数(外部引用)或非常量函数参数时突出显示差异,我们将更改表T的内容,以便该函数还有更多工作要做：
TRUNCATE TABLE dbo.T; INSERT dbo.T (i) VALUES (50001),(50002),(50003),(50004),(50005);
以下修改后的查询现在在其中一个函数参数中使用对表T的外部引用：
SELECT T.i,IR.ts FROM dbo.T AS T CROSS APPLY dbo.IntegerRange(1,T.i) AS IR WHERE IR.n = T.i;
此查询大约需要8秒才能返回结果,如：
注意列ts中行之间的时差. WHERE子句限制了一个合理大小的输出的最终结果,但是低效的函数仍然需要一段时间来填充表变量,其中包含50,000多行(取决于表T中的i的相关值).
执行计划是：
注意缺少Sequence运算符.现在,有一个Table Valued Function运算符填充表变量并在嵌套循环join的每次迭代中返回其行.
需要明确的是：表T中只有5行,表值函数运算符运行5次.它在第一次迭代时生成50,001行,在第二次迭代时生成50,002行……依此类推.表变量在迭代之间被“抛弃”(截断),因此五个调用中的每一个都是完整填充.这就是它如此缓慢的原因,并且每行大约需要在结果中出现相同的时间.
附注：
当然,上面的场景是故意设计的,以显示当msTVF在每次迭代中填充许多行时性能有多差.
上述代码的合理实现会将msTVF参数设置为i,并删除冗余的WHERE子句.表变量仍将在每次迭代时被截断并重新填充,但每次只有一行.
我们还可以从T中获取最小和最大i值,并将它们存储在前一步骤中的变量中.使用变量而不是相关参数调用函数将允许使用’static’表变量模式,如前所述.
缓存未更改的相关参数
返回再次解决原始问题,在不能使用Sequence静态模式的情况下,如果自嵌套循环连接的先前迭代以来没有任何相关参数发生更改,则sql Server可以避免截断并重新填充msTVF表变量.
为了证明这一点,我们将用五个相同的i值替换T的内容：
TRUNCATE TABLE dbo.T; INSERT dbo.T (i) VALUES (50005),(50005),(50005);
再次使用相关参数的查询：
SELECT T.i,T.i) AS IR WHERE IR.n = T.i;
这次结果出现在1.5秒左右：
请注意每行上的相同时间戳.表变量中的缓存结果重用于后续迭代,其中相关值i不变.重复使用结果比每次插入50,005行快得多.
执行计划看起来与以前非常相似：
关键区别在于Table Valued Function运算符的Actual Rebinds和Actual Rewinds属性：
当相关参数不变时,sql Server可以重放(倒回)表变量中的当前结果.当关联更改时,sql Server必须截断并重新填充表变量(重新绑定).一次重新绑定发生在第一次迭代;由于T.i的值不变,所以后续四次迭代都是倒带.
