snowflake-cloud-data-platform - 雪花查询性能调优

Question

我有一个包含多个 ctes 的雪花查询，并使用 Talend 作业插入表中。执行查询需要超过 90 分钟。它是多个级联的ctes，一个调用另一个，另一个调用另一个。

我想提高查询的性能。大概有1000行代码，我无法将其粘贴到这里。当我检查配置文件时，它显示了所有窗口函数和聚合函数，这会减慢查询速度。

例如，顶部较慢的是，

ROW_NUMBER​(​​)​ OVER ​(​PARTITION BY LOWER​(​S​.​SUBSCRIPTIONID​)​ 
                   ORDER BY S​.​ISROWCURRENT DESC NULLS FIRST, 
                      TO_NUMBER​(​S​.​STARTDATE​)​ DESC NULLS FIRST, 
                      IFF​(​S​.​ENDDATE IS NULL, '29991231', S​.​ENDDATE​)​ DESC NULLS FIRST​)​ 

占用 7.3% 的时间。您能建议一种替代方法来提高查询性能吗？

Answer 1

问题是 1000 行对于任何查询分析器来说都很难优化。对于您和继承该代码的 future 团队成员来说，这也会使故障排除变得更加困难。

我建议分解查询并进行这些优化:

1. 使用CREATE TEMPORARY TABLE AS代替CTE。在您要连接或筛选的列上创建表时添加 ORDER BY。临时表对于优化器来说更容易构建和以后使用。 ORDER BY 帮助 Snowflake 了解后续与其他表的联接要优化哪些内容。它们也更容易排除故障。
2. 在您的示例中，看看是否可以将此数据保留为永久列，以便 Snowflake 可以跳过转换部分并对其进行更好的统计:TO_NUMBER​(​S​.​STARTDATE​)​ 和 IFF​(​S​.​ENDDATE IS NULL，'29991231'，S​.​ENDDATE​)​。
3. 除了第 2 步之外，也可以不按开始日期和结束日期排序，看看是否可以添加 IDENTITY、SEQUENCE 或填充 INTEGER > 可以用作排序键的列。您还可以按字面意思命名这个新列 sortKey。对整数进行排序比在 DATETIME 上运行函数然后按它排序要快得多。
4. 如果任何 CTE 可以更改为物化 View ，它们将是预先构建的并且速度会显着加快。
5. 最后，在插入临时表之前，将所有数据暂存在临时表中(按创建目标表的相同列排序)。这将使插入步骤本身更快，并且 Snowflake 将更轻松地处理对该表的并发更改。

注释:

1. 创建 temporary table :创建或替换临时表 table1 作为 select * from Dual; 之后，您将引用 table1 而不是代码而不是 CTE。
2. 记录了具体化 View here 。它们是企业版功能。它们的语法是:create Materialized View mymv as select col1, col2 from mytable;