page36
16
Query Parsing Order
I have discussed DDL and now will move on to discussing Data Manipulation Language
(DML). The most common DML statement you will encounter is
SELECT
, where you
retrieve data from Snowflake. I often refer to a
SELECT
statement as a query.
To effectively tune queries, you must understand how
SELECT
statements are
executed. The following code sample demonstrates a simple single table query syntax:
SELECT DISTINCT column
FROM mytable
WHERE constraint_expression
GROUP BY column
HAVING constraint_expression
ORDER BY column ASC/DESC
LIMIT count OFFSET start_point;
SQL is passed through several subsequent stages that I describe later, but for now, I’ll
focus on identifying the order in which each part of the SQL statement is parsed.
to reduce complexity, you will not consider inline functions, common table
expressions (CtEs), tree walks, set operators, and other advanced constructs.
Figure 1-3 illustrates the order in which SQL operations are actioned.
FROM
WHERE
GROUP
BY
HAVING
SELECT
DISTINCT
ORDER
BY
LIMIT /
OFFSET
Figure 1-3.
SQL order of operations
The next section provides some detail on “how” SQL is parsed and offers some broad
usage advice based upon real-world experience.
FROM Clause
The first step is to identify the object(s) where data is stored. The
FROM
clause identifies
the objects, in your example, a single table. But you often join to additional tables,
and the order in which you join the tables has significance to the Snowflake optimizer.
ChaptEr 1
tuning thE SnowFlakE Data ClouD