从源视图更新目标表哈希字段执行超时，寻求性能优化方案

背景说明 #

我有一个用于填充目标表的源视图，近期为该视图新增2列，并将其纳入现有HASHBYTES函数生成新的content_hash，具体实现如下：

CONVERT(NVARCHAR(100), HASHBYTES('SHA2_512',
                CONCAT(ISNULL(CONVERT(VARCHAR(30), FORMAT(tl.[TRANSACTION_ID],'#')), 'NULL TRANSACTIONUID'), '|'
                ,ISNULL(CONVERT(VARCHAR(30), FORMAT(tl.[COMPANY_ID],'#')), 'NULL PROJECTUID'), '|'
                ,ISNULL(CONVERT(VARCHAR(30), FORMAT(tl.[DEPARTMENT_ID],'#')), 'NULL OPERATINGGROUPUID'), '|'
                ,ISNULL(CONVERT(VARCHAR(30), FORMAT(d.[DEPARTMENT_ID],'#')), 'NULL SO_OPERATINGGROUPUID'), '|' --added per User Story #4626
                ,ISNULL(CONVERT(VARCHAR(50),cx.modifiedon), 'NULL IV_MODIFIEDON'), '|' --added per User Story #4652
                ,ISNULL(CONVERT(VARCHAR(30), FORMAT(tl.[OPENAIR_IS_PERSON_ID],'#')), 'NULL RESOURCEUID'), '|'
                ,ISNULL(CONVERT(VARCHAR(30), FORMAT(tl.[SUBSIDIARY_ID],'#')), 'NULL LEGALENTITYUID'), '|'
                ,ISNULL(CONVERT(VARCHAR(30), FORMAT(tl.[ACCOUNT_ID],'#')), 'NULL GLACCOUNTUID'), '|'
                ,ISNULL(CONVERT(VARCHAR(30), FORMAT(tl.[CLASS_ID],'#')), 'NULL GLCLASSUID'), '|'
                ,ISNULL(CONVERT(VARCHAR(30), FORMAT(c.[PARENT_ID],'#')), 'NULL CLIENTUID'), '|'
                ,ISNULL(CONVERT(VARCHAR(30), a.[TYPE_NAME]), 'NULL GLACCOUNTTYPEUID'), '|'
                ,ISNULL(CONVERT(VARCHAR(30), FORMAT(t.[TRANDATE],'YYYYMMDD')), 'NULL TRANDATE'), '|'
                ,ISNULL(CONVERT(VARCHAR(30), FORMAT(tl.[AMOUNT],'#.############')), 'NULL AMOUNT'), '|'
                ,ISNULL(CONVERT(VARCHAR(30), FORMAT(tl.[AMOUNT_LINKED],'#.############')), 'NULL AMOUNT'), '|'
                ,ISNULL(CONVERT(VARCHAR(30), FORMAT(tl.[AMOUNT_PENDING],'#.############')), 'NULL AMOUNT'), '|'
                ,ISNULL(CONVERT(VARCHAR(30), [NON_POSTING_LINE]), 'NULL NONPOSTINGLINE'), '|'
                ,ISNULL(CONVERT(VARCHAR(30), tl.[OPENAIR_ITEM_DESCRIPTION]), 'NULL Desc'), '|'
                ,ISNULL(CONVERT(VARCHAR(30), FORMAT(tl.[DATE_CREATED],'yyyyMMdd HH:MM:ss')), 'NULL TRANDATE'), '|'
                ,ISNULL(CONVERT(VARCHAR(30), FORMAT(tl.[DATE_LAST_MODIFIED_GMT],'yyyyMMdd HH:MM:ss')), 'NULL LAST_MODIFIED')
                ))) 
                as content_hash

核心问题 #

执行以下UPDATE语句将目标表的content_hash同步为源视图对应值时，语句长时间运行（最长2小时仍未完成）：

UPDATE tgt
    SET
        tgt.content_hash = src.content_hash
    FROM dbo.[gl_transaction_line] tgt
    INNER JOIN dbo.src_gl_transaction_line src 
        ON tgt.gl_transaction_line_uid = src.gl_transaction_line_uid

源视图结果集约646万条记录，单独执行视图仅需11-12分钟（新增列前后耗时相近）。

本次为一次性操作，目的是避免夜间ETL存储过程因content_hash全量差异将所有记录写入历史表；已临时将目标时态表改为非时态表，避免历史表数据量翻倍。

已尝试的优化措施 #

• 移除HASHBYTES内的嵌套FORMAT函数及冗余CONVERT操作，视图耗时缩短了几分钟，但UPDATE仍无进展；
• 查看源视图执行计划，发现存在占64%成本的聚集索引扫描，已计划尝试创建非聚集索引。

补充ETL逻辑 #

夜间刷新存储过程通过对比源视图与目标时态表的content_hash，判断执行插入、更新、删除或无操作，核心逻辑如下：

IF OBJECT_ID('tempdb.dbo.#processing_path') IS NOT NULL BEGIN DROP TABLE #processing_path END

SELECT          ISNULL(src.gl_transaction_line_uid, tgt.gl_transaction_line_uid) as gl_transaction_line_uid
                , CASE WHEN tgt.gl_transaction_line_uid IS NULL 
                        THEN 'Insert'
                    WHEN src.gl_transaction_line_uid IS NULL 
                        THEN 'Delete'
                    WHEN src.content_hash != tgt.content_hash
                        THEN 'Update'
                    ELSE 'Unchanged' END AS processing_path
                , tgt.version_begin_timestamp
INTO            #processing_path
FROM            dbo.src_gl_transaction_line src
 FULL JOIN      dbo.[gl_transaction_line] tgt ON src.gl_transaction_line_uid = tgt.gl_transaction_line_uid

CREATE UNIQUE CLUSTERED INDEX idx_uc ON #processing_path (gl_transaction_line_uid)

寻求解决方案 #

现需要可提升UPDATE性能的方法，或同步content_hash的替代方案。

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