How can performance be optimized for a query that returns a small amount of data from a very large base table?

The correct answer is C. Use the search optimization service. The Search Optimization Service (SOS) is specifically designed to accelerate selective point-lookup queries - those that return a small number of rows from a very large table using equality or IN predicates on specific columns. It works by building a persistent search access…

Performance Management

Question

Options

AUse clustering keys
BCreate materialized views
CUse the search optimization service
DUse the query acceleration service

How the community answered

(28 responses)

A
4% (1)
B
4% (1)
C
86% (24)
D
7% (2)

Explanation

The Search Optimization Service (SOS) is specifically designed to accelerate selective point-lookup queries - those that return a small number of rows from a very large table using equality or IN predicates on specific columns. It works by building a persistent search access path structure that allows Snowflake to skip scanning the vast majority of micro-partitions. Clustering keys (A) help with range scans and filter pruning but are less effective for arbitrary point lookups. Materialized views (B) precompute aggregation results and are better for repeated complex aggregations. The Query Acceleration Service (D) helps with large, resource-intensive queries that benefit from additional compute, not selective small-result lookups.

Topics

#Query optimization#Performance tuning#Search optimization service#Selective queries

Community Discussion

No community discussion yet for this question.

Full COF-C02 Practice