sql - 为什么通过使用子查询调用UDF来提高查询性能？

我最近意识到，通过在子查询中调用UDF比直接调用它们要好得多，为什么会发生这种情况？

举个例子：

CREATE FUNCTION [CurrentYearStart]()
RETURNS DATETIME
AS
BEGIN
    DECLARE @Date DATETIME;
    SELECT top 1 @Date = StartPeriod
    FROM SystemPeriods WITH (NOLOCK);
    RETURN @Date;
END

ProviderServiceAreas有412585行。

以下查询在大约50秒钟内返回

SELECT   PayGroup, SystemType, MAX(EffDt) AS MaxEffDt
FROM     CAT.ProviderServiceAreas
WHERE    EffDt > CurrentYearStart()
GROUP BY Paygroup, SystemType

执行计划：https://www.brentozar.com/pastetheplan/?id=BJCom7vgm

以下查询在大约1s内返回

SELECT   PayGroup, SystemType, MAX(EffDt) AS MaxEffDt
FROM     CAT.ProviderServiceAreas
WHERE    EffDt > (SELECT CurrentYearStart())
GROUP BY Paygroup, SystemType

执行计划：https://www.brentozar.com/pastetheplan/?id=HkolVQDx7

最佳答案

我会去：

SELECT PayGroup, SystemType, MAX(EffDt) AS MaxEffDt
FROM ServiceAreas CROSS JOIN
     (SELECT StartPeriod() as sp) sp
WHERE EffDt > sp.sp
GROUP BY Paygroup, SystemType ;

显然，性能差异是函数被调用的次数。如果将函数调用放在FROM子句中，则可以得到更多控制。