带CASE WHEN的游标发送邮件存储过程执行过慢，求优化

问题分析 #

你当前用游标逐行处理并发送邮件，性能低下的核心原因有两点：

1. 游标本身是逐行遍历，处理大量数据时开销极大；
2. 原查询存在冗余的CASE WHEN逻辑，且未对用户去重，导致同一用户可能被多次处理，重复调用sp_send_dbmail（每次调用存储过程都有额外性能开销）。

优化方案 #

1. 简化查询逻辑+用户去重 #

直接过滤出符合条件的过期项目对应的用户，用DISTINCT去重，避免同一用户收到重复邮件，减少邮件发送次数。

2. 临时表替代游标 #

用临时表存储待处理用户，配合WHILE循环批量处理，比游标效率更高。

3. 优化邮件内容 #

修正原HTML标签语法问题，可选添加用户的过期项目列表，提升提醒实用性。

优化后的存储过程代码 #

CREATE PROCEDURE SendExpiredAnalysisReminders
AS
BEGIN
    SET NOCOUNT ON;

    -- 创建临时表存储需提醒的用户（自动去重）
    CREATE TABLE #ReminderUsers (
        Email VARCHAR(50),
        FullName VARCHAR(50),
        IsProcessed BIT DEFAULT 0
    );

    -- 插入符合条件的用户：过期且状态为3的项目对应的PE用户
    INSERT INTO #ReminderUsers (Email, FullName)
    SELECT DISTINCT 
           TU.email, 
           TU.full_name
    FROM tbl_project TP
    INNER JOIN tbl_user_pe TU 
        ON TU.full_name = TP.pic_pe
    WHERE TP.analisis_deadline <= GETDATE() 
      AND TP.status = 3;

    -- 声明变量
    DECLARE @email VARCHAR(50), 
            @full_name VARCHAR(50),
            @subject VARCHAR(100),
            @body NVARCHAR(MAX);

    -- 获取第一个待处理用户
    SELECT TOP 1 
           @email = Email, 
           @full_name = FullName
    FROM #ReminderUsers
    WHERE IsProcessed = 0;

    WHILE @@ROWCOUNT > 0
    BEGIN
        -- 组装邮件主题和内容
        SET @subject = 'Notification: You are late in sending analysis';
        
        -- 可选：获取该用户的所有过期项目列表
        DECLARE @overdueProjects NVARCHAR(MAX);
        SELECT @overdueProjects = COALESCE(@overdueProjects + '<br>- ', '') + TP.project_name
        FROM tbl_project TP
        WHERE TP.pic_pe = @full_name
          AND TP.analisis_deadline <= GETDATE() 
          AND TP.status = 3;

        -- 拼接带项目列表的邮件内容
        SET @body = N'<table style="border-collapse: collapse; width: 600px;">
                        <tr>
                            <td style="padding: 15px; font-family: Arial, sans-serif;">
                                Hi ' + @full_name + ',<br><br>
                                You have the following overdue analysis tasks:<br>' 
                                + ISNULL(@overdueProjects, 'No specific project listed') 
                                + '<br><br>
                                Please update your analysis data immediately to stop receiving these reminders.
                            </td>
                        </tr>
                      </table>';

        -- 发送邮件
        EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'YHA SMTP',
            @recipients = @email,
            @subject = @subject,
            @body = @body,
            @body_format = 'HTML';

        -- 标记该用户为已处理
        UPDATE #ReminderUsers
        SET IsProcessed = 1
        WHERE Email = @email AND FullName = @full_name;

        -- 获取下一个待处理用户
        SELECT TOP 1 
               @email = Email, 
               @full_name = FullName
        FROM #ReminderUsers
        WHERE IsProcessed = 0;
    END;

    -- 清理临时表
    DROP TABLE #ReminderUsers;
END;

额外性能建议 #

• 给tbl_project的analisis_deadline、status、pic_pe字段创建联合索引，加速过滤查询：

  CREATE NONCLUSTERED INDEX IX_tbl_project_DeadlineStatusPic 
  ON tbl_project (analisis_deadline, status, pic_pe);
  

• 避免在业务高峰时段执行该存储过程，或者添加分页逻辑分批次处理大量用户。

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