Snowflake — Search Optimization vs Query Acceleration
Both Search Optimization and Query Acceleration services can be used to improve performance in a snowflake environment. While search optimization improves performance of certain types of queries in a table, query acceleration can improve performance of the warehouse.
Search optimization improves query performance by skipping unwanted micro-partitions during scanning and query optimization service improves performance of warehouse by offloading portions of the query processing work to shared compute resources. Let us try to understand the differences between these two services in this article.
Search Optimization Service
Let us say we have a query that tries to find all rows that contain the name “Sundar” in a table. We know data in snowflake is stored in micro-partitions. So, the table scan operation of this query has to scan all micro-partitions to find out all rows that contain the name “Sundar”. Now, if the table scan operation knows the micro-partitions in which the name “Sundar” is present, it can scan only those micro-partitions and skip the ones in which the name “Sundar” is not present. This will reduce the number of micro-partitions scanned and improve query performance. That is what search optimization service does for us.
How does Search Optimization work ?
Search optimization service maintains a persistent data structure called search access path. This data structure keeps track of which values of the table’s columns are stored in which micro-partitions. Using this, search optimization service finds out the micro-partitions that need to be scanned for a particular value and skips the rest.
The search access path is created and maintained by a maintenance service.
- When search optimization is configured for a table, the maintenance service creates and populates the search access path.
- Whenever there are changes to the table, the maintenance service updates the search access path keeping it up to date.
Query Acceleration Service
Let us say we have a warehouse of size “S”. Assume, we have a query that takes 15 minutes to complete and we want it to execute faster. Easiest way is to alter our warehouse to size “M”, which means we have to spend more credits per hour. Remember warehouse of size “S” has 2 computes and “M” has 4 computes. The cost could be almost twice. Wouldn’t it be good if we can achieve better performance for such queries without increasing the warehouse size ?
That is exactly what Query Acceleration Service does for us.
How does Query Acceleration Service work ?
Query Acceleration Service provides shared computes that can be utilized by the warehouse for queries that require more workload. These shared computes are over and beyond the number of computes assigned to us as part of the warehouse configuration. So they are utilized only when needed and only for specific types of queries.
Not all queries all eligible for this acceleration. Queries with large scans and aggregations can qualify for query acceleration service. We can find what queries are eligible for this acceleration from the “QUERY_ACCELERATION_ELIGIBLE” view.
Unlike search optimization service that is configured for a table, query acceleration service is enabled at a warehouse level. This can be enabled while creating a warehouse or can be updated for an existing warehouse.
We can use search optimization and query acceleration together to improve query performance significantly.
- We can use search optimization service to prune the micro-partitions not needed for a query.
- On top of it, query acceleration can offload the work (for eligible queries) to shared computes to increase parallelism thereby improving performance overall.
Note : Both of these features are available only in the enterprise edition of Snowflake and both have costs associated with them. We need to do a performance vs cost analysis before enabling any of these two.
