SSIS遍历15万条数据性能优化求助：单条处理耗时超3.5小时

业务场景与当前实现 #

• 需求：遍历目标表中15万条4列数据，逐行执行包含更新操作的转换逻辑
• 当前实现步骤：
  1. 通过Execute SQL Task将目标表（xxxx）的15万条4列数据以完整结果集存入对象类型变量
  2. 使用Foreach Loop容器遍历该变量中的每一行
  3. 容器内通过Execute SQL Task调用存储过程[Omg].[validateError]，传入当前行的4列作为参数

性能问题 #

处理15万条数据耗时近3.5小时，无法满足业务要求的「至少每2小时执行一次」的频率。尝试改用Script Task通过ADO.NET遍历对象变量，耗时仍超过3小时。另外，待处理数据所在的xxxx表无主键标识列。

关键代码详情 #

获取数据的SQL语句 #

select aa, bb, cc, dd 
from xxxx; 

（xxxx表共150000条待处理数据）

调用的存储过程代码 #

CREATE PROCEDURE [Omg].[validateError]
    @DID varchar(50),
    @MGN varchar(100),
    @MN varchar(50),
    @SN varchar(20)
AS
    DECLARE @IsValidxx bit = 0,
            @IsValidyy bit = 0,
            @IsValidzz bit = 0,
            @IsValidww bit = 0,
            @HoldErrorCodes varchar(20) = 'Z',
            @InValidxxrErrorCode varchar(20) = 'D',
            @InValidyyErrorCode varchar(20) = 'A',
            @InValidzzErrorCode varchar(20) = 'M',
            @InValidwwErrorCode varchar(20) = 'G';
BEGIN
    BEGIN TRY
        SET @IsValidxx = CASE 
                             WHEN EXISTS(SELECT 1 FROM  aaaaaaaa WHERE DID = @DID AND TerritoryType = 'Level 1') 
                                 THEN 1 
                                 ELSE 0 
                         END;
        SET @IsValidyy = CASE 
                             WHEN EXISTS (SELECT 1 
                                          FROM bbbbbbb OFM 
                                          INNER JOIN ccccccc MG ON MG.Ordf = OFM.Ordf
                                          INNER JOIN ddddddddd M  ON M.Mod = MG.Mod
                                                 AND M.Available = 1 
                                                 AND MG.IsActive = 1
                                                 AND M.MN = @MN 
                                                 AND MG.MGN = @MGN) 
                                 THEN 1 
                                 ELSE 0 
                         END;

        IF @IsValidyy = 1
        BEGIN
            SET @IsValidzz = 1;
            SET @IsValidww = 1;
        END
        ELSE
        BEGIN
            SET @IsValidzz = CASE WHEN EXISTS(SELECT 1 FROM  dddddd  WHERE MN = @MN AND SN=@SN AND Available=1 ) THEN 1 ELSE 0 END;
                SET @IsValidww= CASE WHEN EXISTS(SELECT 1 FROM  ccccc WHERE MGN = @MGN AND IsActive=1) THEN 1 ELSE 0 END;
        END

        IF @IsValidxx = 1 AND @IsValidyy = 1 AND 
           @IsValidzz = 1 AND @IsValidww = 1
        BEGIN
            SET @IsValidxx = 1;
            SET @IsValidyy = 1;
            SET @IsValidzz = 1;
            SET @IsValidww = 1;
        END
        ELSE 
            IF @IsValidxx = 0
            BEGIN
                SET @HoldErrorCodes = @HoldErrorCodes + @InValidxxrErrorCode;
            END

            IF @IsValidyy = 0
            BEGIN
                SET @HoldErrorCodes = @HoldErrorCodes + @InValidyyErrorCode;
            END

            IF @IsValidzz = 0
            BEGIN
                SET @HoldErrorCodes = @HoldErrorCodes + @InValidzzErrorCode;
            END

            IF @IsValidww = 0
            BEGIN
                SET @HoldErrorCodes = @HoldErrorCodes + @InValidwwErrorCode;
            END

            UPDATE [Import].[Recommendation] 
            SET ErrorCodes = @HoldErrorCodes 
            WHERE DID = @DID 
              AND MN = @MN 
              AND MGN = @MGN 
              AND SN = @SN;
    END TRY
    BEGIN CATCH
        RETURN ERROR_MESSAGE() ;
    END CATCH
END

求助内容 #

寻求针对该SSIS处理流程的性能优化方案，以将处理时间压缩至2小时以内。

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