D4.2 - OUTLINE BEST PRACTICES FOR SNOWFLAKE PERFORMANCE MANAGEMENT ON VIRTUAL WAREHOUSES Flashcards
What are the related query performance techniques associated with the search optimization service?
- Clustering a table
- Creating one or more materialized views (clustered or unclustered)
Benefits of clustering a table?
- faster range searches
- faster equality searches
- However, a table can be clustered only a single key, which can contain one or more columns or expressions)
Search Optimization Service speeds up?
- Equality searches
- Applies only to all the columns of supported types in a table that has search optimization enabled
Materialized view speeds up?
- Both equality and range searches, as well as some sort operations, but only for the subset of rows and columns included in the materialized view
- Materialized views can also be used to to set a different clustering key on the same source table (or a subset of that table), or in conjunction with flattening JSON/ variant data
What is the ‘Query Profile’ ?
- Query Profile provides a graphical representation of the main components of the processing plan for a selected query, with statistics for each component, along with details and statistics for the overall query
- It is designed to help you spot typical mistakes in SQL query expressions and/or to identify potential performance bottlenecks and improvement opportunities
Caching
Metadata cache: managed by Snowflake automatically, so it doesnt use compute to provide range values like min, max, row counts, etc
Query Cache: results of a query, good for 24 hours, unless underlying data changes, 24 hour clock restarts if the query is re-executed within the 24-hour period up to a max of 31 days
Warehouse cache: resets when warehouse is suspended. Size of cache is determined by size of warehouse. Provides improved performance for subsequent queries if they can read from cache instead of tables
