Azure Synapse存储过程性能优化建议请求：实时与备份数据对比

这个存储过程用于对比实时表DWH.DIM_LEASE_ACTIVITY和备份表SCRATCH.DIM_LEASE_ACTIVITY_DLY_BKP_@{LATEST_DATE_VALUE}的数据差异，差异逻辑为(A-B)+(B-A)，最终将差异统计结果写入ALT.SUPPL_DLY_BACKUP_COMPARE表。以下是针对现有实现的具体优化建议：

一、替换动态SQL写法，规避风险并提升可读性 #

现有实现通过字符串拼接生成动态SQL，存在SQL注入风险，且超长字段列表重复编写导致代码维护困难。改用参数化传递+字段列表复用的方式优化：

优化方案 #

1. 将重复的超长字段列表提取为变量，避免冗余编写；
2. 使用sp_executesql的参数化传递方式传入@BKP_QUARTERS，替代字符串拼接。

示例代码片段 #

DECLARE @COL_LIST NVARCHAR(MAX) = N'LEASE_ACTIVITY_KEY, LEASE_ACTIVITY_SRC_KEY, CONTRACT_NUMBER, LEASE_REPORTING_TYPE, TRANSACTION_TYPE, LEASE_TYPE, ACTIVITY_STATUS, LEASE_DESCRIPTION, LEASE_COMMENTS, REPORT_DATE, START_DATE, END_DATE, SIGN_DATE, START_DATE_KEY, END_DATE_KEY, SIGN_DATE_KEY, LEASE_COMMENCEMENT_DATE, ORIGINAL_TERM_EXPIRATION_DATE, EXPIRATION_DATE, CONTRACTUAL_START_DATE, SUITES, RENEWAL_TYPE, CONTRACTUAL_RENT_START_DATE, RENT_START_DATE, ORIGINAL_TERM_END_DATE, REPORTED_FLAG, RENT_TREND_EXCLUDED_FLAG, NON_LEASE_DEAL, COMMENCEMENT_DATE_STATUS, RETENTION_TYPE, GENERATION, CASH__RENT__STABILIZES, RELEASE_FLAG, PUBLISHED_FLAG, PRODUCT_TYPE, CROSS_CONNECT_TYPE, BUSINESS_UNIT, TELX_CASE_NUMBER, LEASE_CURRENCY_CODE, LEASE_EXCHANGE_RATE, SIGN_PERIOD, REPORT_PERIOD, COMMENCEMENT_PERIOD, SIGN_YEAR, REPORT_YEAR, COMMENCEMENT_YEAR, REDUNDANCY, DATASOURCE, SOURCE, ORDER_LINE_NUMBER, PUBLISHED_LEASE_IDENTIFIER, CUSTOMER_TYPE, YARDI_TCODE, UNIT_TYPE, LEGACY_SRC_KEY, TRANSACTION_TYPE_GROUP, UNIT_TYPE_SRC_KEY, EXCLUDE_FROM_LEASESTATS, LOCK_FLAG, ORDER_START_DATE, FNT_ASSET_ID, MTM_FLAG, PRIOR_LEASE_MRC, OPERATION_CODE, RECORDONLY_FLAG, LEASE_CREATED_BY, LEASE_OWNER, POWER_TYPE, TRANSACTION_SUBTYPE, ANNUAL_ESCALATION_TYPE__C, CPI_CAP__C, ESCALATION_TYPE_COMMENTS__C';

DECLARE @SQL_a NVARCHAR(MAX) = N'
SELECT ' + @COL_LIST + N'
INTO #TempLeaseActivity_a
FROM DWH.DIM_LEASE_ACTIVITY DIM
INNER JOIN DWH.DIM_DATE D ON (CASE WHEN DIM.LEASE_REPORTING_TYPE = ''Signed Leases'' THEN DIM.SIGN_DATE_KEY ELSE DIM.START_DATE_KEY END) = D.DATE_KEY
WHERE D.QUARTER = @BKP_QUARTERS
  AND (DIM.DATASOURCE NOT IN (''Teraco Upload'', ''INXN FTP'') OR (DIM.DATASOURCE IN (''Teraco Upload'', ''INXN FTP'') AND DIM.COMMENCEMENT_DATE_STATUS <> ''Target''))
';

EXEC sp_executesql @SQL_a, N'@BKP_QUARTERS NVARCHAR(7)', @BKP_QUARTERS = @BKP_QUARTERS;

二、移除临时表，直接通过关联计算差异 #

现有实现将数据写入临时表后再执行EXCEPT，会额外占用存储资源并增加IO开销。改用FULL JOIN直接关联两张表，一次查询完成双向差异统计：

优化方案 #

用FULL JOIN关联实时表和备份表，通过主键是否为NULL区分差异类型，同时用CHECKSUM校验所有字段一致性，减少中间数据存储环节。

示例代码片段 #

INSERT INTO [ALT].[SUPPL_DLY_BACKUP_COMPARE] (
    TABLE_NAME, TYPE, COUNT, QUARTER, COMPARED_WITH_BKP_DATE, ETL_CREATED_DT
)
SELECT 
    'Lease Activity Dimension' AS TABLE_NAME,
    CASE 
        WHEN A.LEASE_ACTIVITY_KEY IS NULL THEN 'Backup minus Live'
        WHEN B.LEASE_ACTIVITY_KEY IS NULL THEN 'Live minus Backup'
    END AS TYPE,
    COUNT(*) AS COUNT,
    @BKP_QUARTERS AS QUARTER,
    @LATEST_DATE_VALUE AS COMPARED_WITH_BKP_DATE, -- 替换GETDATE()，确保与备份表日期一致
    GETDATE() AS ETL_CREATED_DT
FROM (
    SELECT ' + @COL_LIST + N'
    FROM DWH.DIM_LEASE_ACTIVITY DIM
    INNER JOIN DWH.DIM_DATE D ON (CASE WHEN DIM.LEASE_REPORTING_TYPE = ''Signed Leases'' THEN DIM.SIGN_DATE_KEY ELSE DIM.START_DATE_KEY END) = D.DATE_KEY
    WHERE D.QUARTER = @BKP_QUARTERS
      AND (DIM.DATASOURCE NOT IN (''Teraco Upload'', ''INXN FTP'') OR (DIM.DATASOURCE IN (''Teraco Upload'', ''INXN FTP'') AND DIM.COMMENCEMENT_DATE_STATUS <> ''Target''))
) A
FULL JOIN (
    SELECT ' + @COL_LIST + N'
    FROM SCRATCH.DIM_LEASE_ACTIVITY_DLY_BKP_' + @LATEST_DATE_VALUE + N' DIM
    INNER JOIN DWH.DIM_DATE D ON (CASE WHEN DIM.LEASE_REPORTING_TYPE = ''Signed Leases'' THEN DIM.SIGN_DATE_KEY ELSE DIM.START_DATE_KEY END) = D.DATE_KEY
    WHERE D.QUARTER = @BKP_QUARTERS
      AND (DIM.DATASOURCE NOT IN (''Teraco Upload'', ''INXN FTP'') OR (DIM.DATASOURCE IN (''Teraco Upload'', ''INXN FTP'') AND DIM.COMMENCEMENT_DATE_STATUS <> ''Target''))
) B ON A.LEASE_ACTIVITY_KEY = B.LEASE_ACTIVITY_KEY
    AND CHECKSUM(A.*) = CHECKSUM(B.*) -- 快速校验所有字段一致性
WHERE A.LEASE_ACTIVITY_KEY IS NULL OR B.LEASE_ACTIVITY_KEY IS NULL
GROUP BY 
    CASE 
        WHEN A.LEASE_ACTIVITY_KEY IS NULL THEN 'Backup minus Live'
        WHEN B.LEASE_ACTIVITY_KEY IS NULL THEN 'Live minus Backup'
    END;

注意：若表中包含TEXT/NTEXT/IMAGE等无法被CHECKSUM处理的字段，需替换为BINARY_CHECKSUM或逐字段对比。

三、优化日期关联逻辑，提升查询性能 #

现有CASE WHEN的日期关联逻辑会导致无法利用索引，建议通过计算列+索引优化：

优化方案 #

1. 在实时表和备份表上创建计算列，提前生成关联用的日期键：

-- 实时表创建计算列及索引
ALTER TABLE DWH.DIM_LEASE_ACTIVITY ADD DATE_KEY_FOR_QUARTER AS (CASE WHEN LEASE_REPORTING_TYPE = 'Signed Leases' THEN SIGN_DATE_KEY ELSE START_DATE_KEY END);
CREATE NONCLUSTERED INDEX IX_DIM_LEASE_ACTIVITY_DATE_KEY_FOR_QUARTER ON DWH.DIM_LEASE_ACTIVITY (DATE_KEY_FOR_QUARTER);

-- 备份表同理（可在备份脚本中自动创建）
ALTER TABLE SCRATCH.DIM_LEASE_ACTIVITY_DLY_BKP_@{LATEST_DATE_VALUE} ADD DATE_KEY_FOR_QUARTER AS (CASE WHEN LEASE_REPORTING_TYPE = 'Signed Leases' THEN SIGN_DATE_KEY ELSE START_DATE_KEY END);
CREATE NONCLUSTERED INDEX IX_BKP_LEASE_ACTIVITY_DATE_KEY_FOR_QUARTER ON SCRATCH.DIM_LEASE_ACTIVITY_DLY_BKP_@{LATEST_DATE_VALUE} (DATE_KEY_FOR_QUARTER);

2. 修改查询中的关联条件为：

INNER JOIN DWH.DIM_DATE D ON DIM.DATE_KEY_FOR_QUARTER = D.DATE_KEY

四、其他细节优化 #

• 参数合法性校验：在存储过程开头增加参数格式校验，比如检查@BKP_QUARTERS是否符合YYYY-QN格式，@LATEST_DATE_VALUE是否为YYMMDD格式；
• 临时表资源清理：若保留临时表写法，需在存储过程末尾显式删除临时表，避免占用会话资源：

DROP TABLE IF EXISTS #TempLeaseActivity_a, #TempLeaseActivity_b;

• **避免SELECT ***：始终明确指定字段列表，避免因表结构变更导致的错误，同时减少不必要的字段传输；
• 日期字段修正：原代码中COMPARED_WITH_BKP_DATE使用FORMAT(GETDATE(), 'yyMMdd')，应替换为传入的@LATEST_DATE_VALUE，确保与备份表日期完全匹配。

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