page110
92
Ultimately, consider refactoring the query to eliminate bottlenecks, which may
include using temporary tables as the intermediary storage for large result sets.
Join Order
Table join order has the capability to derail the best of queries. As with everything, a little
knowledge can be dangerous, and applying expert knowledge from legacy RDBMSs is
particularly dangerous.
i suggest an open mind when investigating query performance issues.
In Snowflake, the table placement order is opposite to what you would expect
in Oracle.
Why Is Join Order Important?
Referring to our earlier explanation of what a “good” query profile looks like, when the
build side is larger than the probe side, performance is usually slower.
You also know Snowflake builds hash tables in preparation for joining data sets,
and the order in which you join your tables has significance. For all query profiles, and
assuming a right deep join tree, the optimal pattern is for the table returning the largest
data set at the bottom right and the table returning the smallest data set at the top left.
You saw an example of a right deep join tree earlier within this chapter.
Within your SQL statement, you should place the table with the lowest cardinality
first within the FROM clause as this has the potential to prune the most micro-partitions
earliest within the query. The next lowest cardinality table should be second, and so on,
up to the highest cardinality table last.
Identifying Join Order Issues
When investigating join order issues, you should first examine the query profile. As you
can see from Figure 3-17, the optimal pattern for result set determination is the largest
data volume at the bottom right, with reduction to the smallest data volume shown at the
top left.
ChapTer 3
The Query profiler