Snowflake视图性能优化咨询：原查询与视图执行耗时差异问题

问题描述 #

在Snowflake中直接执行查询时，若在所有阶段对Drug字段添加过滤条件，查询可在30秒内完成。但将该查询创建为普通视图后，通过WHERE条件过滤Drug时，性能大幅下降，耗时近5分钟。

涉及的关联表行数：

• RECORDS：827,055,369行
• AUDIT：1,051,055,768行
• NOTES：61,637,025行

原查询SQL如下：

with SRC as (
    SELECT DISTINCT 
         A2.DRUG
        ,A2.HOSPITAL_NUMBER
        ,A2.HOSPITAL_NAME
        ,A2.COUNTRY
        ,A2.INSTANCE
        ,A2.FORM
        ,A2.PATIENT
        ,A1.ID
        ,A1.FORM_FIELD
        ,A2.POSITION
        ,A1.AUDIT_NAME
        ,A1.VALUE
        ,A1.TIMESTAMP
    FROM AUDIT A1
    JOIN RECORDS A2
      ON A1.ID = A2.ID 
     AND A1.FORM_FIELD = A2.FORM_FIELD
    WHERE A1.DRUG = 'ABCXYZ' AND EXISTS (
        SELECT 1
        FROM NOTES VSN
        WHERE (1=1)
          AND VSN.DRUG = A2.DRUG
          AND VSN.ITEM_ID = A2.ITEM_ID
          AND VSN.DRUG = 'ABCXYZ'
          AND VSN.TEXT = 'Required'
    )
), MaxSourceTimestamp as (
    SELECT   WITHOUT_VERIFY.DRUG
        ,WITHOUT_VERIFY.PATIENT
        ,WITHOUT_VERIFY.ID
        ,WITHOUT_VERIFY.FORM_FIELD
        ,WITH_VERIFY.TIMESTAMP
        ,MAX(WITHOUT_VERIFY.TIMESTAMP) AS MaxSourceTimestamp
    FROM SRC WITH_VERIFY
    JOIN SRC WITHOUT_VERIFY
       ON WITHOUT_VERIFY.DRUG                   = WITH_VERIFY.DRUG
      AND WITHOUT_VERIFY.PATIENT                 = WITH_VERIFY.PATIENT
      AND WITHOUT_VERIFY.ID               = WITH_VERIFY.ID
      AND WITHOUT_VERIFY.FORM_FIELD              = WITH_VERIFY.FORM_FIELD
      AND WITHOUT_VERIFY.AUDIT_NAME != 'Verify'
    WHERE WITHOUT_VERIFY.TIMESTAMP   <= WITH_VERIFY.TIMESTAMP
      AND WITH_VERIFY.AUDIT_NAME = 'Verify'
    GROUP BY 
         WITHOUT_VERIFY.DRUG
        ,WITHOUT_VERIFY.PATIENT
        ,WITHOUT_VERIFY.ID
        ,WITHOUT_VERIFY.FORM_FIELD
        ,WITH_VERIFY.TIMESTAMP
), FIELD_NAMES AS (
    SELECT DISTINCT DRUG, FORM_FIELD, LABEL 
    FROM FIELDS 
    WHERE DRUG = 'ABCXYZ' AND TYPE = 'CRF' 
      AND (DRUG, VERSION_ID) IN (
        SELECT DRUG, MAX(VERSION_ID) 
        FROM METADATA 
        WHERE (1=1)
          AND TYPE = 'CRF' AND DRUG = 'ABCXYZ'
        GROUP BY DRUG
      )
)
select 
     WITHOUT_VERIFY.DRUG
    ,WITHOUT_VERIFY.HOSPITAL_NUMBER
    ,WITHOUT_VERIFY.HOSPITAL_NAME
    ,WITHOUT_VERIFY.COUNTRY
    ,WITHOUT_VERIFY.INSTANCE
    ,WITHOUT_VERIFY.FORM
    ,WITHOUT_VERIFY.PATIENT
    ,WITHOUT_VERIFY.ID
    ,WITHOUT_VERIFY.FORM_FIELD
    ,WITHOUT_VERIFY.POSITION
    ,WITHOUT_VERIFY.AUDIT_NAME
    ,WITHOUT_VERIFY.VALUE
    ,WITHOUT_VERIFY.TIMESTAMP
    ,L2.VERIFY_TIMESTAMP
    ,L3.TIMESTAMP PREVIOUS_DATE 
    ,L4.LABEL FIELD_NAME 
from SRC WITHOUT_VERIFY 
LEFT JOIN (
    SELECT 
         WITHOUT_VERIFY.*
        ,s.TIMESTAMP as VERIFY_TIMESTAMP
    FROM MaxSourceTimestamp s
    LEFT JOIN SRC WITHOUT_VERIFY
     ON WITHOUT_VERIFY.DRUG                   = s.DRUG
    AND WITHOUT_VERIFY.PATIENT                 = s.PATIENT
    AND WITHOUT_VERIFY.ID               = s.ID
    AND WITHOUT_VERIFY.FORM_FIELD              = s.FORM_FIELD
    AND WITHOUT_VERIFY.TIMESTAMP        = s.MaxSourceTimestamp
    AND WITHOUT_VERIFY.AUDIT_NAME != 'Verify'
) L2  
 on WITHOUT_VERIFY.DRUG            = L2.DRUG
AND WITHOUT_VERIFY.PATIENT          = L2.PATIENT 
AND WITHOUT_VERIFY.ID        = L2.ID 
AND WITHOUT_VERIFY.FORM_FIELD       = L2.FORM_FIELD 
AND WITHOUT_VERIFY.TIMESTAMP = L2.TIMESTAMP
LEFT JOIN SRC L3 
 ON WITHOUT_VERIFY.DRUG    = L3.DRUG 
AND WITHOUT_VERIFY.PATIENT    = L3.PATIENT 
AND WITHOUT_VERIFY.ID  = L3.ID 
AND WITHOUT_VERIFY.FORM_FIELD = L3.FORM_FIELD
AND L3.AUDIT_NAME = 'Verify'
LEFT JOIN FIELD_NAMES L4 
 ON WITHOUT_VERIFY.DRUG      = L4.DRUG 
AND WITHOUT_VERIFY.FORM_FIELD = L4.FORM_FIELD
WHERE WITHOUT_VERIFY.AUDIT_NAME != 'Verify';

性能优化方法 #

1. 使用参数化视图 #

将Drug作为参数传入视图，让Snowflake优化器能提前将过滤条件下推到底层表，避免全量扫描中间结果。示例：

CREATE OR REPLACE VIEW MY_VIEW (p_drug) AS
WITH SRC AS (
    SELECT DISTINCT 
         A2.DRUG
        ,A2.HOSPITAL_NUMBER
        -- 其他字段保留原结构
    FROM AUDIT A1
    JOIN RECORDS A2
      ON A1.ID = A2.ID 
     AND A1.FORM_FIELD = A2.FORM_FIELD
    WHERE A1.DRUG = p_drug AND EXISTS (
        SELECT 1
        FROM NOTES VSN
        WHERE VSN.DRUG = A2.DRUG
          AND VSN.ITEM_ID = A2.ITEM_ID
          AND VSN.DRUG = p_drug
          AND VSN.TEXT = 'Required'
    )
), MaxSourceTimestamp as (
    SELECT   WITHOUT_VERIFY.DRUG
        ,WITHOUT_VERIFY.PATIENT
        -- 其他字段保留原结构
    FROM SRC WITH_VERIFY
    JOIN SRC WITHOUT_VERIFY
       ON WITHOUT_VERIFY.DRUG = WITH_VERIFY.DRUG
      -- 其他JOIN条件保留
    WHERE WITHOUT_VERIFY.TIMESTAMP <= WITH_VERIFY.TIMESTAMP
      AND WITH_VERIFY.AUDIT_NAME = 'Verify'
    GROUP BY 
         WITHOUT_VERIFY.DRUG
        -- 其他GROUP BY字段保留
), FIELD_NAMES AS (
    SELECT DISTINCT DRUG, FORM_FIELD, LABEL 
    FROM FIELDS 
    WHERE DRUG = p_drug AND TYPE = 'CRF' 
      AND (DRUG, VERSION_ID) IN (
        SELECT DRUG, MAX(VERSION_ID) 
        FROM METADATA 
        WHERE TYPE = 'CRF' AND DRUG = p_drug
        GROUP BY DRUG
      )
)
-- 最终SELECT语句保留原结构，所有硬编码的'ABCXYZ'替换为p_drug
select 
     WITHOUT_VERIFY.DRUG
    -- 其他字段保留
from SRC WITHOUT_VERIFY 
-- 后续JOIN逻辑保留
WHERE WITHOUT_VERIFY.AUDIT_NAME != 'Verify';

调用方式：SELECT * FROM MY_VIEW('ABCXYZ');

2. 启用过滤条件下推 #

检查视图执行计划（使用EXPLAIN命令），确认Drug过滤是否被推到底层表。若未下推，可尝试：

• 移除不必要的DISTINCT：原SCTE中的DISTINCT若不是必须的，可直接删除，减少计算开销。
• 重写EXISTS子查询：先过滤NOTES表再进行JOIN，替代EXISTS逻辑，帮助优化器识别过滤条件。

3. 使用物化视图预计算 #

如果Drug的过滤值相对固定，可创建物化视图预计算对应结果，直接读取预存数据：

CREATE OR REPLACE MATERIALIZED VIEW MV_MY_VIEW AS
-- 原查询，保留硬编码的'Drug'='ABCXYZ'
...

若需支持多个Drug值，可按Drug字段分区创建物化视图，提升特定值的查询效率。

4. 优化底层表结构 #

• 对AUDIT、RECORDS表按DRUG字段聚类：ALTER TABLE AUDIT CLUSTER BY (DRUG);，让Snowflake快速定位目标Drug的数据分区。
• 针对高基数字段（如DRUG、ID）启用搜索优化服务，加速过滤和JOIN操作。

创建视图的注意事项 #

• 避免硬编码过滤条件：原查询中多处硬编码'ABCXYZ'，创建视图时需替换为参数或允许外部传入条件，确保优化器能下推过滤逻辑。
• 简化CTE结构：过多嵌套CTE可能阻碍优化器的条件下推，尝试合并部分CTE，减少中间结果集的大小。
• 移除冗余操作：确认DISTINCT、聚合等操作是否必要，避免无意义的计算开销。
• 检查执行计划：创建视图后，用EXPLAIN查看执行计划，确认过滤条件是否下推到底层表，是否存在全表扫描。
• 优先选择参数化视图：当需要动态过滤条件时，参数化视图比普通视图更易配合优化器生成高效执行计划。

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