page112
94
•
Join explosion resulting in spillage and OOMs due to large
intermediate data set generation.
•
Left deep tree join refers to query profiles where the predominating
query profile branches to the left, the opposite of a right deep tree
join. Left deep tree joins consume more warehouse memory and
reduce parallel processing options.
Remediating Poor Join Order Issues
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 except for composite
natural keys.
You should also be mindful that the Snowflake query optimizer prefers numeric data
type joins. These join criteria can also cause performance issues:
•
Data type conversion: Joining a NUMBER to a VARCHAR.
•
Evaluate expressions: MIN/MAX, etc.
•
User-defined functions (UDFs): Complex logic embedded
into a UDF.
•
Common table expression (CTE): You will investigate CTEs later in
this chapter.
We have all encountered complex SQL statements that are hard to read. On closer
examination you may find the following:
•
SELECT *
•
Redundant table joins
•
Missing composite key join attributes
•
DISTINCT forcing uniqueness
•
Unnecessary join keys
Remediating poor join order issues involves a lot of time and hard work along with
constant retesting of changes with the eventual objective of improving performance. You
must at all times simplify your code wherever possible by removing complexity.
ChapTer 3
The Query profiler