page102
84
Long Execution Time Costs
Long execution times may be caused by many factors; I list some here:
•
Micro-partition fragmentation due to a high number of low-volume
inserts and updates
•
Degree of pruning required to resolve data sets
•
Data skewing resulting from changing data content over time
•
Query predicates not matching cluster key definition
•
High cardinality queries with many selective criteria leading to
inefficient pruning
Remediating Long Execution Time Queries
Automatic clustering can remediate long execution times by re-ordering micro-partition
content into optimally efficient form. Note that automatic clustering can invalidate
cached results. I will discuss automatic clustering in detail in the next chapter. You can
find further details at
https://docs.snowflake.com/en/user-guide/tables-auto-
reclustering
.
For existing tables where the cluster key does not match the query predicates,
consider adding a materialized view. Consider using dynamic tables to offload work onto
serverless compute. Note that the refresh time may be a factor in your decision-making.
Over time data within a table may become skewed. This occurs as data ranges
change over time and is often a side effect of high
INSERT
and
UPDATE
activity where
micro-partition content changes frequently, a phenomena referred to as churn. I will
discuss this phenomena in detail within the next chapter, but for now, it is sufficient to
know skewed data can impact query execution time as the number of micro-partitions
scanned may be higher than expected.
A search optimization service can improve the performance of highly selective
queries but is not recommended for high-churn environments. I discuss search
optimization services in detail later within this book. You can find further details at
https://docs.snowflake.com/en/user-guide/search-optimization-service
.
Incorrect join order can also contribute to long execution time; put the lowest
cardinality table first after the FROM clause, and put the highest cardinality table last.
ChapTer 3
The Query profiler