如何优化查询7月未登录用户的SQL？消除While循环提升性能

原SQL通过WHILE循环逐天查询2022年7月1日至31日期间未登录系统的用户，执行耗时超2分钟，应用端还触发超时错误。以下是原实现代码：

DECLARE @TABLE_TEMP TABLE
(
    Row int IDENTITY(1,1),
    [UserId] int,
    [UserName] nvarchar(100),       
    [StartDate] nvarchar(20),
    [FirstLogin] nvarchar(20),
    [LastLogout] nvarchar(20)       
)

DECLARE @START_DATE datetime = '2022-07-01';
DECLARE @END_DATE   datetime = '2022-07-31';
DECLARE @USER_ID nvarchar(max) = '1,2,3,4,5,6,7,8,9';
DECLARE @QUERY nvarchar(max) = '';

WHILE(@START_DATE < @END_DATE OR @START_DATE = @END_DATE)
BEGIN               
    SET @QUERY = 'SELECT 
                      s.userid AS [UserId], 
                      s.username AS [UserName],
                 ''' + CAST(@START_DATE as nvarchar)  + ''' AS [StartDate],
                      MAX(h.START_TIME) as [FirstLogin],
                      MAX(ISNULL(h.END_TIME, s.LAST_SEEN_TIME)) as [LastLogout]                  
                  FROM USER s 
                  LEFT JOIN USER_LOGIN_HISTORY h ON h.userid = s.userid                                                         
                  LEFT JOIN TEMP_USER_INACTIVATION TUI ON TUI.userid = s.userid AND ('''+ CAST(@START_DATE as nvarchar)  +''' BETWEEN ACTIVATED_DATE AND DEACTIVATD_DATE)
                  WHERE s.userid IN (' + @USER_ID + ') 
                    AND h.userid  NOT IN (SELECT userid FROM USER_LOGIN_HISTORY WHERE CAST(START_TIME AS DATE)  = '''+ CONVERT(nvarchar,(CAST(@START_DATE AS DATE))) +''')                                                                                      AND ACTIVATED_DATE IS NOT NULL 
                  GROUP BY s.userid, h.userid, s.username, s.last_seen_time
                  HAVING CAST(MAX(ISNULL(h.END_TIME, s.LAST_SEEN_TIME)) AS DATE) <>  '''+ CONVERT(nvarchar,(CAST(@START_DATE AS DATE)))  + '''
                  ORDER BY [User Name]'

    INSERT INTO @TABLE_TEMP
        EXEC(@QUERY)   

    SET @START_DATE = DATEADD(DD, 1, @START_DATE)           
END

优化方案：用CTE生成日期序列，消除循环 #

直接生成目标日期范围内的所有日期，一次性关联查询所有用户的未登录记录，避免重复执行31次查询：

DECLARE @START_DATE date = '2022-07-01';
DECLARE @END_DATE   date = '2022-07-31';
DECLARE @USER_IDS TABLE (UserId int);
-- 拆分用户ID为表变量，避免动态SQL拼接
INSERT INTO @USER_IDS VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9);

-- CTE生成日期序列
WITH DateRange AS (
    SELECT @START_DATE AS CheckDate
    UNION ALL
    SELECT DATEADD(day, 1, CheckDate)
    FROM DateRange
    WHERE CheckDate < @END_DATE
)
SELECT
    s.userid AS UserId,
    s.username AS UserName,
    dr.CheckDate AS StartDate,
    MAX(h.START_TIME) AS FirstLogin,
    MAX(ISNULL(h.END_TIME, s.LAST_SEEN_TIME)) AS LastLogout
INTO #TABLE_TEMP -- 用临时表替代表变量，大数据量下性能更优
FROM DateRange dr
CROSS JOIN [USER] s
LEFT JOIN USER_LOGIN_HISTORY h 
    ON h.userid = s.userid
    AND CAST(h.START_TIME AS date) <= dr.CheckDate
LEFT JOIN TEMP_USER_INACTIVATION tui 
    ON tui.userid = s.userid
    AND dr.CheckDate BETWEEN tui.ACTIVATED_DATE AND tui.DEACTIVATD_DATE
WHERE s.userid IN (SELECT UserId FROM @USER_IDS)
    AND tui.ACTIVATED_DATE IS NOT NULL -- 排除停用状态的用户
    -- 检查当前日期该用户无登录记录
    AND NOT EXISTS (
        SELECT 1 
        FROM USER_LOGIN_HISTORY h_check
        WHERE h_check.userid = s.userid
        AND CAST(h_check.START_TIME AS date) = dr.CheckDate
    )
    -- 最后活跃日期不等于当前检查日期
    AND CAST(MAX(ISNULL(h.END_TIME, s.LAST_SEEN_TIME)) AS date) <> dr.CheckDate
GROUP BY s.userid, s.username, dr.CheckDate
ORDER BY UserName, dr.CheckDate;

-- 输出结果
SELECT * FROM #TABLE_TEMP;
DROP TABLE #TABLE_TEMP;

优化说明 #

1. 消除循环与动态SQL：用CTE生成31天的日期序列，通过CROSS JOIN一次性匹配所有用户和日期，避免重复执行31次查询及动态SQL的编译开销。
2. 用户ID处理优化：将ID字符串转为表变量，避免动态拼接带来的注入风险，同时提升查询匹配效率。
3. 数据类型统一：全程使用date类型处理日期，避免datetime转nvarchar的性能损耗和格式错误。
4. 子查询逻辑优化：用NOT EXISTS替代NOT IN，逻辑更清晰且性能更稳定，避免NULL值干扰结果。
5. 临时表替代表变量：大数据量场景下，临时表的查询性能优于表变量，后续还可根据需求添加索引进一步优化。

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