page180
266
Confirm search optimization is disabled, as shown in Figure 7-13.
Figure 7-13.
Search optimization disabled
Timeliness
As previously stated, the Snowflake SOS background service runs asynchronously;
therefore, I cannot be sure of when the search access paths for an enabled table will
complete. Furthermore, when the table data changes, the search access paths will
update during which time queries might run more slowly. This behavior is explained
within the Snowflake documentation at
https://docs.snowflake.com/en/user-guide/
search-optimization-service#how-the-search-optimization-service-works
.
I suggest search optimization should not be implemented on empty tables nor on
tables where high velocity DML operations occur.
Best Practices
Implementing SOS can significantly improve the performance of some queries. I offer
the following guidelines when considering SOS:
•
The candidate table should have a high number of micro-partitions.
•
High cardinality attributes are ideally suited to SOS.
•
Apply SOS on a selective attribute basis where known access
paths exist.
•
Use SOS for tables with low-velocity DML operations.
•
Match existing point lookup query predicates to attribute values.
Conversely, these scenarios are to be avoided when implementing SOS:
•
Low number of micro-partitions
•
Low cardinality attributes
•
Unsupported data types
Chapter 7
SearCh OptimizatiOn ServiCe