page48
29
I will not dive into the root causes of each potential failure. The following
information enables you to identify where some query failures occur along with
sufficient context to explain “why” such failures may occur.
Query Compilation
In this section I explain how a noncached query is processed.
If the statement in step 2 of Figure 2-1 (“If the query exactly matches a previously
run query AND the data has not changed, then the result set is returned from the Result
Cache”) is TRUE, then this section is not executed.
Figure 2-2 illustrates the processing path followed when step 2 of Figure 2-1 is FALSE,
corresponding to step 3, which is “Plan/Optimize”:
Parser
1
SQL
Query
Ingeson
Semanc
Analysis
Logical
Rewriter
Micro-
Paron
Pruning
Inial Plan
Plan
Rewriter
Cost-based
Join
Ordering
Physical
Plan
Generaon
2
3
4
5
6
7
8
9
Pruner
Tokenizer
FoundaonDB
Figure 2-2.
Query compilation steps
Before we deep dive into the Snowflake query optimizer, it is worth saying that all
RDBMSs have a query optimizer. We therefore rely upon common, in-use terminology,
and for those new to understanding query optimizers, we briefly explain terminology
shortly.
Before we dive in, our aim is to impart sufficient information to cause you to think
about how your code will execute once submitted to the query optimizer. The easiest,
cheapest, and most effective performance tuning occurs before a single line of code is
written, that is, during the design phase. All the information presented within this book
is intended to provide you with the tools to tune your code before submission to the
Snowflake query optimizer.
Also note not every stage or step within a stage is mandatory. The query optimizer
may choose to skip stages or individual steps where appropriate. As an example, if no
CTE is detected within the submitted SQL statement, there is no need to expand CTEs.
Snowflake refers to this process as automatic skipping of redundant stages. I indicate
where stages and steps can be skipped in the following sections.
Chapter 2
the Query Optimizer