page290
378
Eliminate DISTINCT
I occasionally observe the use of the
DISTINCT
clause to enforce uniqueness. Closer
examination often reveals a missing join condition from the query predicates or poor
data model implementation.
Identify and remove all
DISTINCT
clauses wherever possible, and use the previously
covered query performance tools to aid investigation.
Examine Common Table Expressions (CTEs)
Where a CTE is referenced more than once in the same SQL statement, you may find
attribute pruning is disabled.
I recommend the use of CTEs to abstract complex logic and simplify code, but not in
all situations.
Please refer to the corresponding section in Chapter 3 for further details.
Window Functions
QUALIFY
provides the same functionality for window functions as
HAVING
does for
aggregate
GROUP BY
functions.
QUALIFY
may reduce memory usage by limiting results;
see
https://docs.snowflake.com/en/sql-reference/constructs/qualify
.
Use the same keys for
PARTITION BY
and
ORDER BY
clauses. Using different keys will
result in a performance penalty.
Implement a single consistent windowing pattern for multiple analytic function calls
in the same SQL statement.
Always implement a
PARTITION BY
clause in a window function regardless of
whether the query is successfully processed. Where no obvious partitioning pattern
matches the requirement, use either
PARTITION BY NULL
or
PARTITION BY 1
.
Returned Query Attributes
Snowflake prefers fewer attributes to be returned from individual queries and suggests
using 100 or fewer attributes (according to Jiaqi Yan, principal software engineer, and
Minzhen Yang, principal engineer and tech lead, at Snowflake Inc).
ChapTer 10
OpTimizing perfOrmanCe