page97
79
Long Compilation Time Costs
Long compilation times may be caused by many factors; I list some here:
•
High data volumes in tables
•
Micro-partition fragmentation due to a high number of low-volume
INSERT
s and
UPDATE
s
•
Highly denormalized tables with lots of attributes
•
Multiple levels of nested view decomposition required to
resolve objects
•
Multiple levels of role hierarchy navigation required to resolve objects
•
Multiple data masking policies to resolve attribute content
•
Multiple row-level access policies to resolve entitled data sets
•
Number of and complexity of expressions applied to attributes
•
Degree of pruning required to resolve data sets
•
Lack of bind variable use negating query reuse
Remediating Long Compilation Time Queries
Now that you understand what a long compilation time is and how to identify one, you
can turn your attention to remediating identified queries.
Snowflake optimally prefers 10 or fewer attributes to be returned in a result set,
avoiding attribute lists of more than 100.
Wherever possible, simplify queries by reducing the number of views navigated.
And for existing tables where the cluster key does not match the query predicates,
consider adding a materialized view. In highly volatile environments with unpredictable
workloads, I will demonstrate how to externally parallelize queries in a later chapter to
reduce runtimes.
Consider using dynamic tables to offload work onto serverless compute. Note that
this feature at the time of writing is in public preview, and the refresh time may be a
factor in your decision-making.
Complex role hierarchies take time to navigate and reduce the number of database
roles required to resolve object dependencies.
ChapTer 3
The Query profiler