Which action would provide optimal performance for the second SQL query?

A Snowflake user runs a complex SQL query on a dedicated virtual warehouse that reads a large amount of data from micro-partitions. The same user wants to run another query that uses the same data set.

Which action would provide optimal performance for the second SQL query?
A . Assign additional clusters to the virtual warehouse.
B . Increase the STATEMENT_TIMEOUT_IN_SECONDS parameter in the session.
C . Prevent the virtual warehouse from suspending between the running of the first and second queries.
D . Use the RESULT_SCAN function to post-process the output of the first query.

View Answer

Answer: D

Explanation:

According to the Using Persisted Query Results documentation, the RESULT_SCAN function allows you to query the result set of a previous command as if it were a table. This can improve the performance of the second query by avoiding reading the same data from micro-partitions again.

The other actions do not provide optimal performance for the second query because:

• Assigning additional clusters to the virtual warehouse does not affect the data access speed, but only the query execution speed. It also increases the cost of the warehouse.

• Increasing the STATEMENT_TIMEOUT_IN_SECONDS parameter in the session does not improve the performance of the query, but only allows it to run longer before timing out. It also increases the risk of resource contention and deadlock.

• Preventing the virtual warehouse from suspending between the running of the first and second queries does not guarantee that the data will be cached in memory, as Snowflake uses a least recently used (LRU) cache eviction policy. It also increases the cost of the warehouse. https://docs.snowflake.com/en/user-guide/querying-persisted-results