我正在使用SQL Server 2008R2。我编写了以下表值函数UDF,它接受标量值this或that或两者作为参数,并返回带有ID、this和that列的表。当我从复杂查询中调用该函数时,性能非常差,但在简单查询中调用时则没有问题。我想知道是否有人对我做的事情有任何想法,这会减慢速度。函数定义如下:
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)
...但是当在一个更复杂的查询中调用时(伪代码如下:如果不够信息,请告诉我),处理时间从约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秒。所以,我们又回到了函数的问题...?