page294
382
Table Join Order
Table join order on SQL statements can be significant. Start with the smallest cardinality
tables first to eliminate the greatest number of micro-partitions early in the query
optimization stage. Also check that the query filter criteria are sufficiently selective to
improve micro-partition pruning.
Simplify Logic
Wherever possible, remove aggregations and summaries from join, group by, and order
by operations as cardinality estimates suffer. Instead, create materialized views to pre-
aggregate and summarize attributes.
Reduce the number of levels navigated to resolve query result sets.
Missing Referential Integrity
Referential integrity may be retrospectively applied by using an
ALTER TABLE
statement;
see
https://docs.snowflake.com/en/sql-reference/sql/create-table-constraint
.
Missing Aliases
At all times you should remove metadata lookups by adding aliases to all referenced
objects. Adding meaningful aliases aids readability and code maintenance too.
Temporary Tables
Temporary tables are restricted to the local session and are removed when the session
closes. Temporary should be regarded as an interim, nonpersistent step in a process,
recognizing the contents cannot be inspected after the session closes.
Temporary tables have these characteristics:
•
Avoid name conflicts for temporary tables with base tables. If
common, then the temporary table will be referenced in the
statement.
•
Separate micro-partition metadata and statistics maintained
throughout their life cycle.
ChapTer 10
OpTimizing perfOrmanCe