page181
267
•
Low query execution time (less than a few seconds)
•
The query predicates are:
•
Not
EQUALITY
or
SUBSTRING
•
Contained within an
IN
list
•
The result of a subquery
•
A relatively large result set in comparison to the full data set
•
A SQL function in the query on the target table attribute
Search optimization does not support the leading attribute of a cluster key as this
already provides for micro-partition pruning.
Snowflake is continually evolving, and SOS is no exception. At the time of writing,
query join acceleration, promising a dramatic speedup for star schema joins, is being
worked on, as disclosed during Snowflake Summit in June 2023.
Summary
This chapter introduced SOS and then suggested where SOS may best be deployed
within a typical application footprint before indicating optimal usage and limitations.
SOS is not a silver bullet and must be selectively applied. SOS implemented on tables
with high-velocity, low-volume DML operations will prove costly to maintain. I also
showed that implementing SOS at the table level in most scenarios will also prove costly,
and you should prefer to implement SOS for individual attributes instead. However, also
note that SOS provides a capability to reference individual values within a JSON record,
which is a very useful feature.
Your focus should be on enabling SOS for individual attributes only.
The walk-through of search optimization provided insight into how SOS works,
noting the asynchronous nature of the service. Both compute costs and storage costs are
incurred, noting the interaction with the Time Travel and Fail Safe settings. I explained
how to investigate both storage and compute costs then working through reference test
cases for standard tables, dynamic tables, transient tables, and temporary tables.
Chapter 7
SearCh OptimizatiOn ServiCe