内联表值UDF性能

Question

我正在使用SQL Server 2008R2。我已经编写了下面的表值UDF，它接受标量值this或者this或者两者作为参数，并返回一个带有列ID的表，this和那个表。当我从一个复杂的查询中调用这个函数时，我看到了糟糕的表现，但当我在简单的查询中调用它时，却看不到这种性能。我想知道是否有人对我正在做的事情有任何想法，这会减慢速度。函数的定义如下：

CREATE function dbo.fn_getThisThat (@this nvarchar(255), @that nvarchar(255)) 
RETURNS TABLE 
RETURN 

SELECT These.this, Those.that, COALESCE(These.ID, Those.ID) as ID 
FROM 
    (
    SELECT col1 as ‘this’, value1, value2, ID 
    FROM (
     SELECT t1.col1, t1.col2, t1.col3, t2.col1 
     FROM t1 
     JOIN t2 
      ON t1.col1 = t2.col1 
     WHERE t2.col2 = ‘this’ 
     AND t1.col1 in (‘value1’, ‘value2’) 
     ) SOURCE 
    PIVOT (
     MAX(t1.col3) FOR t1.col1 in (value1, value2) 
     ) AS pvt 
    ) These 
JOIN 
    (
    SELECT t1.col1, t1.col2, t2.col1, t3.ID 
    FROM t3 
    JOIN t1 
     ON t3.col1 = t1.col1 
    JOIN t2 
     ON t2.col1 = t1.col1 
    WHERE t3.col3 = ‘value3’ 
    AND t1.col3 = ‘value1’ 
    AND t2.col3 = ‘value2’ 
    ) Those 
WHERE that = @that 
OR this = @this 

下面的语句过程非常迅速（< 1秒）时传递标量参数：

SELECT * FROM dbo.fn_getThisThat(scalarValue, null) 

或者在像一个相对简单的查询：

SELECT t1.col1, t1.col2, fn.This 
FROM t1 
CROSS APPLY dbo.fn_getThisThat(t1.col3, null) 

...但是在像这样更复杂的查询中调用时（在伪代码中：让我知道它是否没有足够的信息），它滞后HORRIBLY（从处理时间约1秒到约2:30秒）：

DECLARE @table (a, b, c) 
INSERT @table (a, b, c) 
SELECT (values) 

SELECT t1.c1, t1.c2, t1.c3 
FROM 
    (
    SELECT a.c1, COUNT(fn.That) as c2, COUNT(a.c2) as c3 
    FROM a 
    JOIN b ON (join terms) 
    CROSS APPLY dbo.fn_getThisThat(a.c2, null) fn 
    WHERE a.c1 IN (SELECT a FROM @table) 
    GROUP BY a.c1 
    ) t1 

有没有人有什么建议我在做什么来杀死在这第二个查询的速度？我改变了函数接受一个数组而不是标量参数，但是这消除了我的跨应用能力（在最后的代码片段中）。就我从查询分析器中可以看出的性能影响来自于我的函数的交叉应用。我以为我不会遇到RBAR，因为我的UDF不是多语言，但也许我是错的......？

编辑： 还有一件事：查询执行计划显示该函数本身只贡献2％的批处理;较大的查询贡献了98％，但其大部分成本来自索引搜索和表扫描，而不是来自并行性。这使我认为函数调用可能不是指责查询缓慢的原因，而是某些表所涉及的索引缺乏（不幸的是，我并没有很多控制权来增加指数）。我在没有调用函数的情况下运行查询，并且表扫描和索引查找仍显示为高，但查询在大约8秒内完成。那么，我们回到了功能......？

Answer 1

我认为你最好的选择是在SSMS中运行它并检查你的执行计划。由于这是一个内联的表值UDF，因此优化器将把它并入到执行计划中，并且应该能够看到事物正在脱轨。

我没有太多的经验在CROSS APPLY情况下使用PIVOT子查询 - 这让我觉得这可能是一个问题。但执行计划肯定会告诉你。

Answer 2

从MSDN文章应用（MSDN - Apply）：

“APPLY运算符可以让您调用表值函数对于查询的外部表表达式返回的每一行”。

你的例子显示了一个group by。在行被分组之后而不是在特定的查询中调用你的函数是否可能？这将减少函数必须被调用的行数。

如果做不到这一点，我的另一个建议是通过优化查询，在函数本身中获得尽可能多的性能增益。你可以加快每一毫秒的速度。

Answer 3

正如已经指出的那样，为外部查询中的每一行调用CROSS APPLY。所以，这里的关键问题是返回多少行：

DECLARE @table (a, b, c) 
INSERT @table (a, b, c) 
SELECT (values) 

SELECT t1.c1, t1.c2, t1.c3 
FROM 
    (
    SELECT a.c1 
    FROM a 
    JOIN b ON (join terms) 
    WHERE a.c1 IN (SELECT a FROM @table) 
    ) t1 

这是将要对您的TVF进行的调用次数。如果（并且它很大）TVF对于a.c2的任何值具有类似的执行时间，那么相关的性能比较就是你的函数的单独执行时间*从上面的查询返回的行。

由于原始查询的混淆/泛化，难以确定，但我怀疑您的TVF可能被排除，并且逻辑内联到父查询中。如果可行，这可能会让你获得最佳表现。

Answer 4

您可能想要更改您的UDF以在任何地方正确使用两部分表名，以便您可以向其添加SCHEMABINDING子句。见Improving query plans with the SCHEMABINDING option on T-SQL UDFs。

Answer 5

到目前为止，我已经能够将性能从〜2：30提高到〜0：17。这更好，但仍不理想。我做了以下内容：

• 新增架构绑定到我的电视UDF（谢谢你，莱姆斯！）。这有所帮助，但似乎对性能的影响比以下更少。

• 重新构建的主查询在@table上加入，而不是在子查询中引用它：这看起来最有帮助，并且是大部分性能提升似乎来自的地方。

我觉得我剩下的滞后是由于一些大表我打严重缺失指数，但没有增加他们的能力，我不知道我能做到的ATM。根据查询分析器的报告，我已经将并行性的成本降低到了0％，所以我认为我已经尽全力去完成函数调用。

谢谢大家！