page94
76
Remediating Cartesian Joins and Join Explosions
Now that you understand what a Cartesian join is, how to identify one, and the costs
associated with them, you can turn your attention to remediating identified queries.
The logical answer is to identify missing join criteria as this is the most likely root
cause. Note that missing composite key attributes are far harder to identify than single
attribute primary key/foreign key relationships. A general rule of thumb is that the
number of WHERE/AND join conditions should always equal the number of tables
minus 1. This works for many scenarios.
Nonunique key joins may also produce partial Cartesian joins. To resolve this issue,
deconstruct your query into its constituent parts and check that each part returns the
expected cardinality. Equality joins most often result in faster processing speed than
nonequality joins, which should be avoided.
Numeric data type joins are the fastest of all. I prefer sequence generated surrogate
primary keys over natural or composite keys for all tables along with declared referential
integrity.
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.
The table join order can also be significant. Start with the smallest tables first as
this may eliminate the greatest number of micro-partitions early within the query
optimization stage. Also check that the filter criteria are sufficiently selective to improve
micro-partition pruning.
Ultimately, consider refactoring the query to eliminate bottlenecks, which may
include using temporary tables as intermediary storage for the large result sets.
Long Compilation Time
Within this section I define long compilation time, provide the means to identify queries
suffering from long compilation time, and then finally offer remediation steps to resolve
this issue.
ChapTer 3
The Query profiler