Azure定时触发函数分批处理100条记录仍遇Execution Timeout Expired错误

核心问题根源 #

1. 全表无差别UPDATE：存储过程开头的UPDATE Students SET IsActive = 0, IsDeleted = 1会更新表中所有记录，即便后续MERGE会把部分记录改回活跃状态。这会触发全表锁，且产生大量日志，直接拖慢执行速度。
2. MERGE条件无法利用索引：ON子句中对email字段嵌套使用REPLACE函数，导致Students表上的email索引完全失效，MERGE操作只能走全表扫描，2万条记录下性能急剧下降。
3. 分批调用无效：每次分批调用都会执行一次全表UPDATE，多次叠加反而加剧锁竞争和日志写入压力。

具体优化方案 #

1. 替换全表UPDATE为精准标记

不要一次性把所有记录设为失效，而是仅标记那些不在新导入数据中的记录，避免无意义的全表更新。

2. 预清洗Email并利用索引

• 在Students表上创建持久化计算列存储清洗后的Email，为该列创建索引，让匹配操作能直接命中索引：

ALTER TABLE Students 
ADD CleanedEmail AS REPLACE(REPLACE(REPLACE(email, CHAR(9), ''), CHAR(10), ''), CHAR(13), '') PERSISTED;

CREATE NONCLUSTERED INDEX IX_Students_CleanedEmail ON Students (CleanedEmail);

• 在存储过程中先清洗UDT中的Email数据，存入临时表并创建索引，避免在匹配时重复计算。

3. 拆分MERGE为独立UPDATE/INSERT

拆分MERGE为单独的更新和插入操作，比MERGE更易优化，且能减少锁的持有时间。

优化后的存储过程代码 #

CREATE PROCEDURE [dbo].[UploadStudents]
    @userUDT [dbo].[UserUDT] READONLY
AS  
BEGIN  
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    -- 1. 清洗UDT数据到临时表，创建索引加速匹配
    CREATE TABLE #CleanedUsers (
        Email NVARCHAR(255),
        CleanedEmail NVARCHAR(255),
        [Name] NVARCHAR(255),
        PrimaryIdentifier NVARCHAR(255),
        SchoolDepartmentCode NVARCHAR(50),
        UserGroup NVARCHAR(50),
        [Role] NVARCHAR(50),
        SpiceId NVARCHAR(50)
    );

    INSERT INTO #CleanedUsers
    SELECT 
        Email,
        REPLACE(REPLACE(REPLACE(Email, CHAR(9), ''), CHAR(10), ''), CHAR(13), ''),
        [Name],
        PrimaryIdentifier,
        SchoolDepartmentCode,
        UserGroup,
        [Role],
        SpiceId
    FROM @userUDT;

    CREATE NONCLUSTERED INDEX IX_Temp_CleanedEmail ON #CleanedUsers (CleanedEmail);

    -- 2. 仅标记不在新数据中的记录为失效
    UPDATE s
    SET IsActive = 0, IsDeleted = 1, ModifiedOn = GETDATE()
    FROM Students s
    LEFT JOIN #CleanedUsers cu ON s.CleanedEmail = cu.CleanedEmail
    WHERE cu.CleanedEmail IS NULL;

    -- 3. 更新匹配到的现有记录
    UPDATE s
    SET 
        UserName = cu.[Name],
        PrimaryIdentifier = cu.PrimaryIdentifier,
        SchoolDepartmentCode = cu.SchoolDepartmentCode,
        UserGroup = cu.UserGroup,
        UserRole = cu.[Role],
        IsActive = 1,
        IsDeleted = 0,
        ModifiedOn = GETDATE()
    FROM Students s
    INNER JOIN #CleanedUsers cu ON s.CleanedEmail = cu.CleanedEmail;

    -- 4. 插入未匹配的新记录
    INSERT INTO Students (Id, CreatedOn, ModifiedOn, CreatedBy, IsActive, IsDeleted, Email, UserName, PrimaryIdentifier, SchoolDepartmentCode, UserGroup, UserRole, SpiceId)
    SELECT 
        NEWID(), GETDATE(), GETDATE(), NULL, 1, 0, cu.Email, cu.[Name], cu.PrimaryIdentifier, cu.SchoolDepartmentCode, cu.UserGroup, cu.[Role], cu.SpiceId
    FROM #CleanedUsers cu
    LEFT JOIN Students s ON s.CleanedEmail = cu.CleanedEmail
    WHERE s.CleanedEmail IS NULL;

    DROP TABLE #CleanedUsers;
END

额外优化建议 #

• 检查Azure SQL数据库的DTU/CPU使用率：如果资源长期处于高负载，考虑升级服务层级。
• 分批处理时确保每一批事务独立：避免大事务持有锁时间过长，可在Azure Function中按500-1000条为一批调用存储过程。
• 清理重复数据：提前检查UDT中是否存在重复的CleanedEmail，避免更新/插入时出现歧义。

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