page292
380
•
Making sure filter criteria are sufficiently selective and match any
defined clustering keys
•
Minimizing aggregations and aggregate filters
•
Reducing analytic operations
•
Removing ordering and record limits
Optimize INSERTs
Occasionally you will encounter individual
INSERT
statements that may be consolidated
into multirow
INSERT
statements.
A multirow
INSERT
statement is considerably more efficient than many individual
INSERT
statements because of the immutable nature of micro-partitions. Every
individual
INSERT
statement causes a new micro-partition write for each row, whereas a
multirow
INSERT
causes fewer micro-partition writes.
UNION or UNION ALL
UNION
forces a
SORT
, whereas
UNION ALL
does not enforce a
SORT
. Replace
UNION
with
UNION ALL
where appropriate.
Joins
Joins can be improved by rewriting code for efficiency; I explain some of the more
common issues encountered with joins next.
Remove Disjunctive Joins
The Snowflake optimizer prefers conjunctive (additive) joins; these are predicates with
AND
operators. Predicates with
OR
operators are disjunctive (subtractive) joins that are
known to affect performance. Disjunctive joins should be rewritten using
UNION
/
UNION
ALL
to improve performance.
ChapTer 10
OpTimizing perfOrmanCe