sql - 为什么使用子查询调用UDF可以提高查询性能？

Question

我最近意识到，通过在子查询中调用 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

Answer 1

我会选择:

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 子句中，您将拥有更多控制权。