优化SQL证书过期查询：每5天返回已过期记录的写法改进

我需要编写一条SQL查询，返回即将在30、15、10、5、0天后过期的证书记录，同时要求证书过期后每5天返回一次该已过期记录。当前查询结果正确，执行耗时也不足1秒，但希望优化实现方式，尤其是筛选已过期每5天记录的逻辑。

现有SQL代码 #

declare @DateVar datetime
set @DateVar = Convert(date, GetDate())

; with Exp as
(
    select a.UID, a.UName, a.Expiration_Date,
    DATEDIFF(DAY, GETDATE(), Expiration_Date) as 'NumberOfDays'
    from Table1 a
    inner join Table2 b on a.ID = b.ID
    inner join Table3 c on a.UID = c.UID 
)
, DateToSendCalc as
(
select *, DATEADD(DAY, -NumberOfDays, Expiration_Date) as DateToSendNotification 
from Exp
)
Select distinct a.UID, a.UName, a.Expiration_Date, DateToSendNotification,
NumberOfDays
from DateToSendCalc a 
where NumberOfDays in (30, 15, 10, 5, 0)
or NumberOfDays like '-%5'

查询结果示例 #

2024年1月15日执行查询 #

2024年1月20日执行查询 #

优化方案 #

1. 核心优化：替换字符串匹配为数值运算 #

原查询用NumberOfDays like '-%5'筛选已过期每5天的记录，属于字符串模式匹配，效率低于数值计算，且逻辑不够直观。建议替换为：

NumberOfDays < 0 AND ABS(NumberOfDays) % 5 = 0

直接通过数值取模判断过期天数绝对值是5的倍数，完全符合需求，执行效率更高，逻辑也更清晰。

2. 简化CTE结构，减少冗余计算 #

原查询中DateToSendCalc里的DateToSendNotification等价于预先定义的@DateVar，因为：

DATEADD(DAY, -NumberOfDays, Expiration_Date) = DATEADD(DAY, -DATEDIFF(DAY, GETDATE(), Expiration_Date), Expiration_Date) = CAST(GETDATE() AS DATE)

因此可以直接在最终查询中使用@DateVar，省去一个CTE，简化查询结构：

优化后的完整SQL：

DECLARE @DateVar DATE = CAST(GETDATE() AS DATE);

WITH Exp AS
(
    SELECT 
        a.UID, 
        a.UName, 
        a.Expiration_Date,
        DATEDIFF(DAY, @DateVar, a.Expiration_Date) AS NumberOfDays
    FROM Table1 a
    INNER JOIN Table2 b ON a.ID = b.ID
    INNER JOIN Table3 c ON a.UID = c.UID 
)
SELECT DISTINCT
    UID, 
    UName, 
    Expiration_Date,
    @DateVar AS DateToSendNotification,
    NumberOfDays
FROM Exp
WHERE 
    NumberOfDays IN (30, 15, 10, 5, 0)
    OR (NumberOfDays < 0 AND ABS(NumberOfDays) % 5 = 0);

3. 潜在性能升级（按需选择） #

如果Expiration_Date字段有索引，可将条件转换为基于该字段的范围/匹配查询，避免对计算字段NumberOfDays的筛选（数据量较大时更明显）：

• 即将过期条件：Expiration_Date IN (DATEADD(DAY, 0, @DateVar), DATEADD(DAY, 5, @DateVar), DATEADD(DAY, 10, @DateVar), DATEADD(DAY, 15, @DateVar), DATEADD(DAY, 30, @DateVar))
• 已过期每5天条件：Expiration_Date < @DateVar AND DATEDIFF(DAY, Expiration_Date, @DateVar) % 5 = 0
  该优化需结合实际索引情况测试，避免因函数计算无法命中索引。

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