ARA-C01 · Question #97
A Developer is having a performance issue with a Snowflake query. The query receives up to 10 different values for one parameter and then performs an aggregation over the majority of a fact table. It
The correct answer is C. Enable the search optimization service on the table. When the users execute the query, the search. Enabling the search optimization service on the table can improve the performance of queries that have selective filtering criteria, which seems to be the case here. This service optimizes the execution of queries by creating a persistent data structure called a search access pat
Question
A Developer is having a performance issue with a Snowflake query. The query receives up to 10 different values for one parameter and then performs an aggregation over the majority of a fact table. It then joins against a smaller dimension table. This parameter value is selected by the different query users when they execute it during business hours. Both the fact and dimension tables are loaded with new data in an overnight import process. On a Small or Medium-sized virtual warehouse, the query performs slowly. Performance is acceptable on a size Large or bigger warehouse. However, there is no budget to increase costs. The Developer needs a recommendation that does not increase compute costs to run this query. What should the Architect recommend?
Options
- ACreate a task that will run the 10 different variations of the query corresponding to the 10 different
- BCreate a task that will run the 10 different variations of the query corresponding to the 10 different
- CEnable the search optimization service on the table. When the users execute the query, the search
- DCreate a dedicated size Large warehouse for this particular set of queries. Create a new role that
How the community answered
(29 responses)- A17% (5)
- B3% (1)
- C72% (21)
- D7% (2)
Explanation
Enabling the search optimization service on the table can improve the performance of queries that have selective filtering criteria, which seems to be the case here. This service optimizes the execution of queries by creating a persistent data structure called a search access path, which allows some micro-partitions to be skipped during the scanning process. This can significantly speed up query performance without increasing compute costs.
Topics
Community Discussion
No community discussion yet for this question.