page291
379
Micro-partitions underpin every SQL statement. You must consider how data is
stored and maintained in storage when making query decisions. You should optimize
your physical base table storage for query performance. Performance may be improved
by separating
VARIANT
attributes into a separate table where most queries do not
reference the
VARIANT
attributes.
Reduce Nested Views
Wherever possible, nested views should be removed from queries. In general, query
optimizers must resolve each nested view before resolving the main query. You can see
how nesting views may lead to performance issues both in terms of increased query
compilation time and execution time.
Nested views are often difficult to debug as they act as a translation, filter,
summary, or aggregate layer between source objects and consuming objects or queries.
Temporarily replacing an intermediary view with a table of the same name and contents
helps resolve issues as the query profile will be far simpler. Better still, replace nested
views with more elegant SQL encompassing view functionality.
I discuss how to identify object types later in this chapter as the naming convention
alone cannot be relied upon.
Replace Subqueries
Snowflake may not always optimize subqueries to dynamically prune micro-partitions,
and rewriting a query may not always convert a subquery to a join. Instead, convert
subqueries to direct joins or CTEs where appropriate. There is usually no benefit to
ordering data in a subquery or CTE except to obtain an intermediate top ‘n’ rowset.
As always, test and then retest to prove that the changes are effective.
Optimization Focus
When optimizing SQL statements, the most impact will be realized by optimizing the
number of rows returned. In order of preference, your approach should focus on the
following:
•
Reducing the number of objects accessed
•
Ensuring join conditions are complete and correct
ChapTer 10
OpTimizing perfOrmanCe