page83
65
Build Side
Build-side operations complete first, where small tables or dimensions are preferred.
Snowflake creates hash tables on the probe side in preparation for joining data
sets. As illustrated in Figure 3-12, the order in which you join tables has significance.
The optimizer prefers the table returning the largest data set (partsupp_baseline 800M
records) at the bottom right and the table returning the smallest data set at the top
left (supplier_baseline 10M records). I will further discuss join ordering later when
considering performance tuning.
When the build side is larger than the probe side, performance is usually slower.
Probe Side
Probe-side operations are where large tables, result sets, or facts are preferred. Using
small build-side data sets facilitates parallelization and optimal use of cluster memory
where our hybrid columnar compressed data is held. As you can see in Figure 3-12, all
build and probe operations are optimally positioned within the query profile.
Right Deep Join Tree
Seventy percent of people prefer visual representations, whereas 30 percent prefer
textual representations of information. As advertising executive Frederick R. Barnard
stated, “One picture is worth a thousand words.” I agree and further propose the visual
representation of a query profile readily allows the viewer to determine patterns that
indicate “good” or “bad.”
Figure 3-12 shows a right deep join tree, which consumes less warehouse memory
and increases parallel processing options.
In my estimation, I consider the query profile tree and profile overview provide
invaluable tools for performance tuning queries and strongly recommend a firm grasp of
these fundamentals will serve you well.
ChapTer 3
The Query profiler