SQL Server 2022中DATEDIFF关联Func1致查询缓慢的替代方案咨询

问题背景 #

原SQL语句在SQL Server 2019中运行正常，但在SQL Server 2022中，即便结果集仅约10万条记录，查询耗时却超5分钟。排查后确认自定义函数Func1是性能瓶颈——注释该函数相关逻辑后，查询几秒即可完成。

原查询语句 #

DECLARE @period int

SELECT @period  = [dbo].[DateFromYearMonth](2025, 04) 

SELECT 
    ap.Id, Plan, Amt, Rate 
FROM 
    (SELECT 
         Id, DateOfBirth, Gender, Days, EffDate, Group    
     FROM 
         (SELECT      
              ap.Id, m.DateOfBirth, m.Gender,      
              ap.EffDate, ap.Days, ap.Group,
              ROW_NUMBER() OVER (PARTITION BY ap.Id, ap.PlanId ORDER BY ap.EffDate DESC) AS rn  
          FROM 
              [dbo].[ActiveP_View] ap, [dbo].[Member] m 
          WHERE 
              @Period BETWEEN @Period AND ap.EndDate) AS T1   
     WHERE  
         rn = 1) ap
LEFT OUTER JOIN 
    [dbo].[State] st WITH (NOLOCK) ON ap.Id = st.Id   
LEFT OUTER JOIN 
    [dbo].[Rte_View] rfl WITH (NOLOCK) ON ap.Group = rfl.Group  
                                       AND (ap.Gender = rfl.Gender OR rfl.Gender IS NULL)  
                                       AND ([dbo].[Func1](ap.DateOfBirth, @Period) = rfl.AUnit OR rfl.AUnit IS NULL) 

原Func1函数定义 #

CREATE FUNCTION [dbo].[Func1](@dob DATE, @date DATE)
RETURNS VARCHAR(5)
AS
BEGIN
    RETURN 
        (CASE 
             WHEN DATEDIFF(MONTH, @dob, COALESCE(@date, GETDATE())) BETWEEN -1 AND 11 
                 THEN 'Month'
                 ELSE 'Year' 
         END);
END

替代实现方案 #

方案1：改用内联表值函数 #

标量函数容易触发逐行计算（RBAR），导致性能恶化。内联表值函数能让SQL Server优化器更好地生成高效执行计划：

CREATE FUNCTION [dbo].[Func1_Inline](@dob DATE, @date DATE)
RETURNS TABLE
AS
RETURN
SELECT 
    CASE 
        WHEN DATEDIFF(MONTH, @dob, COALESCE(@date, GETDATE())) BETWEEN -1 AND 11 
            THEN 'Month'
            ELSE 'Year' 
    END AS Result

查询中调用方式修改为：

AND (
    (SELECT Result FROM [dbo].[Func1_Inline](ap.DateOfBirth, @Period)) = rfl.AUnit 
    OR rfl.AUnit IS NULL
)

方案2：直接将函数逻辑嵌入查询 #

完全移除函数，把判断逻辑提前计算到子查询中，避免函数调用的额外开销：

DECLARE @period int

SELECT @period  = [dbo].[DateFromYearMonth](2025, 04) 

SELECT 
    ap.Id, Plan, Amt, Rate 
FROM 
    (SELECT 
         Id, DateOfBirth, Gender, Days, EffDate, Group,
         -- 提前计算年龄单位，避免重复计算
         CASE 
             WHEN DATEDIFF(MONTH, DateOfBirth, COALESCE(@period, GETDATE())) BETWEEN -1 AND 11 
                 THEN 'Month'
                 ELSE 'Year' 
         END AS AgeUnit
     FROM 
         (SELECT      
              ap.Id, m.DateOfBirth, m.Gender,      
              ap.EffDate, ap.Days, ap.Group,
              ROW_NUMBER() OVER (PARTITION BY ap.Id, ap.PlanId ORDER BY ap.EffDate DESC) AS rn  
          FROM 
              [dbo].[ActiveP_View] ap, [dbo].[Member] m 
          WHERE 
              @Period BETWEEN @Period AND ap.EndDate) AS T1   
     WHERE  
         rn = 1) ap
LEFT OUTER JOIN 
    [dbo].[State] st WITH (NOLOCK) ON ap.Id = st.Id   
LEFT OUTER JOIN 
    [dbo].[Rte_View] rfl WITH (NOLOCK) ON ap.Group = rfl.Group  
                                       AND (ap.Gender = rfl.Gender OR rfl.Gender IS NULL)  
                                       AND (ap.AgeUnit = rfl.AUnit OR rfl.AUnit IS NULL) 

方案3：用日期范围比较替代DATEDIFF #

可以通过直接计算日期区间来实现相同逻辑，完全避免DATEDIFF函数调用：

-- 嵌入查询中的逻辑写法
CASE
    -- 判断出生日期是否在目标日期的前1个月到后11个月范围内
    WHEN @dob >= DATEADD(MONTH, -1, COALESCE(@date, GETDATE()))
         AND @dob <= DATEADD(MONTH, 11, COALESCE(@date, GETDATE()))
    THEN 'Month'
    ELSE 'Year'
END AS AgeUnit

这个逻辑和原函数的DATEDIFF判断结果完全一致，但优化器能更高效地处理日期比较，减少计算开销。

内容的提问来源于stack exchange，提问作者AMDI