Почему производительность запросов повышается за счет вызова 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

Следующий запрос возвращается примерно через 1 с

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 пункт.