向Snowflake导入数千行数据时MERGE是否比INSERT查询速度更快

数千行量级下，你更新后的MERGE语句会比原来的INSERT写法性能更好，且优势会比小数据测试时更稳定。

注意：你最开始改写的第一版MERGE存在逻辑错误，ON子句的与条件要求一行数据同时匹配BLOCK和FACILITY两类hkey，几乎无法触发正确的匹配逻辑，你后续更新的MERGE才是和原INSERT逻辑一致的正确写法，以下性能对比均基于更新后的正确MERGE。

性能差异的核心原因 #

原有INSERT写法的性能缺陷 #

• 需要两次独立扫描临时表temp_table_name，分别处理BLOCK和FACILITY_ID两类数据，还要执行两次独立的NOT IN子查询重复拉取hub_location全量location_hkey做校验，冗余计算成本更高
• 写法中多余的EXISTS子查询会额外触发两次临时表扫描，无实际业务价值还增加了开销
• NOT IN本身存在NULL值匹配异常的隐患，同时性能远低于JOIN匹配

更新后MERGE写法的优势 #

• 仅需一次关联匹配操作就可以完成两类数据的hkey去重校验，只需拉取一次hub_location的location_hkey做哈希匹配，Snowflake对这类等值JOIN的优化效率极高
• 去掉了冗余的NOT IN和EXISTS子查询，所有逻辑在单次DML语句中完成，元数据操作、事务提交的开销只有一次，远低于多段INSERT的开销
• 数千行属于极小数据量级，小数据测试时的耗时占比里查询初始化、元数据操作的开销占比更高，当数据量上升到数千行后，实际计算逻辑的占比提升，MERGE的性能优势会更稳定，不会出现大幅波动

可进一步优化的点 #

你可以把MD5计算提前到USING子句中，避免重复计算hkey，进一步提升效率：

MERGE INTO HUB_LOCATION HL 
USING 
(
    SELECT DISTINCT 
        'BLOCK' AS LOCATION_TYPE, 
        OBJECT_CONSTRUCT(*):BLOCK AS LOCATION_VALUE,
        MD5(CONCAT_WS('', 'BLOCK', OBJECT_CONSTRUCT(*):BLOCK)) AS LOCATION_HKEY
    FROM TEMP_TABLE_NAME 
    UNION ALL 
    SELECT DISTINCT 
        'FACILITY' AS LOCATION_TYPE, 
        object_construct(*):FACILITY_ID AS LOCATION_VALUE,
        MD5(CONCAT_WS('', 'FACILITY', object_construct(*):FACILITY_ID)) AS LOCATION_HKEY
    FROM TEMP_TABLE_NAME
) ST 
ON ST.LOCATION_HKEY = HL.LOCATION_HKEY 
WHEN NOT MATCHED AND ST.LOCATION_VALUE IS NOT NULL THEN 
    INSERT (LOCATION_HKEY, LOAD_DT, RECORD_SRC, LOCATION_TYPE, LOCATION_VALUE) 
    VALUES (ST.LOCATION_HKEY, CURRENT_TIMESTAMP(), 'ONA', ST.LOCATION_TYPE, ST.LOCATION_VALUE);

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