迁移至AWS Lambda后，如何快速从SQL Server检索带9个子查询的5000条记录？

我正在把旧的.NET Core应用迁移为AWS Lambda函数，当前逻辑是先从SQL Server获取一批记录列表，再通过foreach循环对每条记录执行8个额外SQL查询来补充数据。现在要处理约5000条记录，涉及9个包含多表连接的子查询，要求10分钟内完成，但用游标+循环的方式速度太慢，以下是带1个子查询的示例代码：

-- declare variables used in cursor
DECLARE @CUS_ID_SEQ VARCHAR(128);

-- declare cursor
DECLARE cursor_all_Agents CURSOR FOR
SELECT DISTINCT [CUS_ID_SEQ] 
FROM T_Customer C 

INNER JOIN T_State S_CUS ON S_CUS.STA_ID_SEQ=C.CUS_STA_ID_SEQ_FK 
INNER JOIN T_Location L ON L.LOC_CUS_ID_SEQ_FK=C.CUS_ID_SEQ 
INNER JOIN T_State S_Loc ON S_Loc.STA_ID_SEQ=L.LOC_STA_ID_SEQ_FK 
INNER JOIN T_Customer_Address CA on CA.CUAD_CUS_ID_SEQ_FK=C.CUS_ID_SEQ 
INNER JOIN T_Address A ON A.ADD_ID_SEQ = CA.CUAD_ADD_ID_SEQ_FK 
INNER JOIN T_State S ON S.STA_ID_SEQ = A.ADD_STA_ID_SEQ_FK 
INNER JOIN T_Contract CT ON CT.CTR_ID_SEQ = C.CUS_CTR_ID_SEQ_FK 
INNER JOIN T_Customer_Status CS ON CS.CSTA_ID_SEQ = C.CUS_CSTA_ID_SEQ_FK 
LEFT JOIN T_Customer_Affiliation CAFF ON CAFF.CAFF_CUS_ID_SEQ_FK=C.CUS_ID_SEQ 
LEFT JOIN T_Affiliation AF ON AF.AFF_ID_SEQ=CAFF.CAFF_AFF_ID_SEQ_FK 
LEFT JOIN T_Customer_Cancellation_Detail CCD ON CCD.CCAN_CUS_ID_SEQ_FK=C.CUS_ID_SEQ 
LEFT JOIN T_Cancellation_Reason CR ON CR.CNR_ID_SEQ=CCD.CCAN_CNR_ID_SEQ_FK 
LEFT JOIN T_Cancellation_Type CANT ON CANT.CNT_ID_SEQ=CCD.CCAN_CNT_ID_SEQ_FK 

WHERE (C.CUS_Number IS NOT NULL AND LEN(LTRIM(RTRIM(C.CUS_Number))) > 0) 
            AND (L.LOC_Number IS NOT NULL AND LEN(LTRIM(RTRIM(L.LOC_Number))) > 0) 
            AND (C.CUS_Name NOT LIKE '%TEST%') 
            AND ((CCD.CCAN_New_Date > '2010-01-01') OR (CCD.CCAN_New_Date IS NULL)) 
        AND CTR_Code NOT IN ('01', '05', '06', '07', '08', '10', '17', '13', '14')  
-- open cursor
OPEN cursor_all_Agents;

-- loop through a cursor
FETCH NEXT FROM cursor_all_Agents INTO @CUS_ID_SEQ;
WHILE @@FETCH_STATUS = 0
    BEGIN

    PRINT CONCAT('Cus_SEQ_Id id: ', @CUS_ID_SEQ);
    SELECT DISTINCT [CUS_ID_SEQ], [CUS_Name], [CUS_Number], [CUS_Email], [CUS_Website], [CUS_Primaryphone], [CUS_Secondaryphone], [CUS_TaxId], [CUS_Appointment_Date], [CUS_TL_Volume], [CUS_PL_Volume], [CUS_CL_Volume], [CUS_Vision_Statement], [CUS_Vision_Update_Dt], [CUS_Log], [CUS_SEG_ID_SEQ_FK], [CUS_CSTA_ID_SEQ_FK], [CUS_CTR_ID_SEQ_FK], [CUS_REG_ID_SEQ_FK], [CUS_TER_ID_SEQ_FK], [CUS_STA_ID_SEQ_FK], [CUS_Tax_Or_SSN], [CUS_Sort_Name], [CUS_Single_Location_Indicator], [CUS_Comp_PL_Volume], [CUS_Comp_CL_Volume], [CUS_Comp_TL_Volume], [CUS_Comp_Bonds_Volume], [CUS_Affiliation_Number], [CTR_ID_SEQ], [CTR_Code], [CTR_Name], [CSTA_ID_SEQ], [CSTA_Status], [CSTA_Desc], AF.AFF_Code, AF.AFF_Name, AF.AFF_Desc 
    FROM T_Customer C 

INNER JOIN T_State S_CUS ON S_CUS.STA_ID_SEQ=C.CUS_STA_ID_SEQ_FK 
INNER JOIN T_Location L ON L.LOC_CUS_ID_SEQ_FK=C.CUS_ID_SEQ 
INNER JOIN T_State S_Loc ON S_Loc.STA_ID_SEQ=L.LOC_STA_ID_SEQ_FK 
INNER JOIN T_Customer_Address CA on CA.CUAD_CUS_ID_SEQ_FK=C.CUS_ID_SEQ 
INNER JOIN T_Address A ON A.ADD_ID_SEQ = CA.CUAD_ADD_ID_SEQ_FK 
INNER JOIN T_State S ON S.STA_ID_SEQ = A.ADD_STA_ID_SEQ_FK 
INNER JOIN T_Contract CT ON CT.CTR_ID_SEQ = C.CUS_CTR_ID_SEQ_FK 
INNER JOIN T_Customer_Status CS ON CS.CSTA_ID_SEQ = C.CUS_CSTA_ID_SEQ_FK 
LEFT JOIN T_Customer_Affiliation CAFF ON CAFF.CAFF_CUS_ID_SEQ_FK=C.CUS_ID_SEQ
LEFT JOIN T_Affiliation AF ON AF.AFF_ID_SEQ=CAFF.CAFF_AFF_ID_SEQ_FK 
LEFT JOIN T_Customer_Cancellation_Detail CCD ON CCD.CCAN_CUS_ID_SEQ_FK=C.CUS_ID_SEQ 
LEFT JOIN T_Cancellation_Reason CR ON CR.CNR_ID_SEQ=CCD.CCAN_CNR_ID_SEQ_FK 
LEFT JOIN T_Cancellation_Type CANT ON CANT.CNT_ID_SEQ=CCD.CCAN_CNT_ID_SEQ_FK 

WHERE C.CUS_ID_SEQ=@CUS_ID_SEQ

FETCH NEXT FROM cursor_all_Agents INTO @CUS_ID_SEQ;
END;

-- close and deallocate cursor
CLOSE cursor_all_Agents;
DEALLOCATE cursor_all_Agents;

优化方案 #

1. 合并所有查询为单次SQL语句（最核心） #

把原来循环里的9个子查询直接整合到主查询中，用JOIN或CTE一次性拉取所有数据，彻底避免5000+次的数据库请求往返。

• 示例：去掉游标循环，直接执行整合后的完整查询：

SELECT DISTINCT 
    [CUS_ID_SEQ], [CUS_Name], [CUS_Number], [CUS_Email], [CUS_Website], 
    [CUS_Primaryphone], [CUS_Secondaryphone], [CUS_TaxId], [CUS_Appointment_Date], 
    [CUS_TL_Volume], [CUS_PL_Volume], [CUS_CL_Volume], [CUS_Vision_Statement], 
    [CUS_Vision_Update_Dt], [CUS_Log], [CUS_SEG_ID_SEQ_FK], [CUS_CSTA_ID_SEQ_FK], 
    [CUS_CTR_ID_SEQ_FK], [CUS_REG_ID_SEQ_FK], [CUS_TER_ID_SEQ_FK], [CUS_STA_ID_SEQ_FK], 
    [CUS_Tax_Or_SSN], [CUS_Sort_Name], [CUS_Single_Location_Indicator], 
    [CUS_Comp_PL_Volume], [CUS_Comp_CL_Volume], [CUS_Comp_TL_Volume], 
    [CUS_Comp_Bonds_Volume], [CUS_Affiliation_Number], [CTR_ID_SEQ], [CTR_Code], 
    [CTR_Name], [CSTA_ID_SEQ], [CSTA_Status], [CSTA_Desc], AF.AFF_Code, AF.AFF_Name, AF.AFF_Desc 
FROM T_Customer C 
INNER JOIN T_State S_CUS ON S_CUS.STA_ID_SEQ=C.CUS_STA_ID_SEQ_FK 
INNER JOIN T_Location L ON L.LOC_CUS_ID_SEQ_FK=C.CUS_ID_SEQ 
INNER JOIN T_State S_Loc ON S_Loc.STA_ID_SEQ=L.LOC_STA_ID_SEQ_FK 
INNER JOIN T_Customer_Address CA on CA.CUAD_CUS_ID_SEQ_FK=C.CUS_ID_SEQ 
INNER JOIN T_Address A ON A.ADD_ID_SEQ = CA.CUAD_ADD_ID_SEQ_FK 
INNER JOIN T_State S ON S.STA_ID_SEQ = A.ADD_STA_ID_SEQ_FK 
INNER JOIN T_Contract CT ON CT.CTR_ID_SEQ = C.CUS_CTR_ID_SEQ_FK 
INNER JOIN T_Customer_Status CS ON CS.CSTA_ID_SEQ = C.CUS_CSTA_ID_SEQ_FK 
LEFT JOIN T_Customer_Affiliation CAFF ON CAFF.CAFF_CUS_ID_SEQ_FK=C.CUS_ID_SEQ
LEFT JOIN T_Affiliation AF ON AF.AFF_ID_SEQ=CAFF.CAFF_AFF_ID_SEQ_FK 
LEFT JOIN T_Customer_Cancellation_Detail CCD ON CCD.CCAN_CUS_ID_SEQ_FK=C.CUS_ID_SEQ 
LEFT JOIN T_Cancellation_Reason CR ON CR.CNR_ID_SEQ=CCD.CCAN_CNR_ID_SEQ_FK 
LEFT JOIN T_Cancellation_Type CANT ON CANT.CNT_ID_SEQ=CCD.CCAN_CNT_ID_SEQ_FK 
WHERE 
    (C.CUS_Number IS NOT NULL AND LEN(LTRIM(RTRIM(C.CUS_Number))) > 0) 
    AND (L.LOC_Number IS NOT NULL AND LEN(LTRIM(RTRIM(L.LOC_Number))) > 0) 
    AND (C.CUS_Name NOT LIKE '%TEST%') 
    AND ((CCD.CCAN_New_Date > '2010-01-01') OR (CCD.CCAN_New_Date IS NULL)) 
    AND CTR_Code NOT IN ('01', '05', '06', '07', '08', '10', '17', '13', '14')

• 剩余8个子查询，根据数据关联性用LEFT JOIN（允许空值）或INNER JOIN（必须有数据）整合到主查询中，彻底消除循环逻辑。

2. 优化查询性能：索引与执行计划 #

• 检查所有JOIN条件列（如CUS_STA_ID_SEQ_FK、LOC_CUS_ID_SEQ_FK）和WHERE过滤列（如CUS_Number、CTR_Code）是否存在非聚集索引，缺失则创建对应索引，大幅提升JOIN和过滤速度。
• 执行SET SHOWPLAN_XML ON查看查询执行计划，排查表扫描、哈希匹配等高成本操作，针对性优化（比如调整JOIN顺序、添加覆盖索引）。
• 移除不必要的DISTINCT：如果CUS_ID_SEQ是唯一值且JOIN不会产生重复行，直接删除DISTINCT，减少数据库去重开销。

3. 批量处理（单次查询结果过大时） #

如果整合后的查询返回数据量过大，可分批次拉取：

• 用OFFSET ... FETCH NEXT分页查询，每次拉取1000条：

SELECT ... -- 完整字段列表
FROM ... -- 完整表连接
WHERE ... -- 筛选条件
ORDER BY CUS_ID_SEQ
OFFSET 0 ROWS FETCH NEXT 1000 ROWS ONLY;

• 在Lambda中循环处理分页，将数据库请求次数控制在个位数，同时避免一次性加载5000条数据导致内存溢出。

4. Lambda侧优化 #

• 配置合适的内存规格：Lambda内存越高CPU性能越强，根据数据处理量调整到2048MB或更高，提升数据解析和处理速度。
• 复用数据库连接：使用.NET的SqlConnection连接池，避免每次查询新建连接，减少连接建立开销。

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