Using Results Explicitly with RESULT_SCAN()

I am a fan of the result set cache in Snowflake. It is one of three layers of caching, and can be a reliable way to mitigate the impact of repeated queries when the underlying tables are fairly slow to change. It is also possible to explicitly use the result set cache using the RESULT_SCAN() table function. This article explores the plusses, minuses, and some caveats.

Result Set Caching

Result set caching is one of three major levels of caching that I covered in Types of Caching in Snowflake. With result set caching, Snowflake stores the results of a query for well-defined periods of time. When a duplicate query runs, Snowflake checks to see if the query and table are eligible for result set reuse. Instead of fully re-executing the query, the former result set is returned, usually very quickly.

It is useful primarily for tables that don’t change very frequently, but are queried in the same exact syntax over and over again. This makes it very useful for query workloads that generate dashboards.

RESULT_SCAN()

The results of a query can be explicitly accessed for as long as they are retained using the RESULT_SCAN() table function. Normal SQL syntax can be applied to add conditions or transformations to the results that were previously generated and saved.

Let’s work through a couple of examples using the SNOWFLAKE_SAMPLE_DATA database. In one of these examples, using the RESULT_SCAN() table function helps us, and in another example, it hurts us.

Positive Example

Let’s first run a query to build a result set to work with.

SELECT L_SHIPDATE
       , SUM(L_EXTENDEDPRICE-L_DISCOUNT+L_TAX) AS total_price
  FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000.LINEITEM
 GROUP BY L_SHIPDATE
  ;

For me, this query ran in 52 seconds on an XS warehouse.
If we simply run the exact same query, but explicitly against the result set:

SELECT L_SHIPDATE
       ,  total_price
  FROM TABLE(RESULT_SCAN('01be4f5f-0206-36aa-000e-4a6b00056086'))
  ;

This runs in just 460 milliseconds for me on an XS warehouse. It’s just returning the results that were already calculated.
If I instead need to do some basic filtering, I can try that first against the base table:

SELECT L_SHIPDATE
       , SUM(L_EXTENDEDPRICE-L_DISCOUNT+L_TAX) AS total_price
  FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000.LINEITEM
  WHERE L_SHIPDATE <='1994-09-12'
 GROUP BY L_SHIPDATE
  ;

This query runs about 15 seconds on an XS warehouse for me.
How about if I use RESULT_SCAN() instead?

SELECT l_shipdate
       , total_price
  FROM TABLE(RESULT_SCAN('01be4f5f-0206-36aa-000e-4a6b00056086'))
 WHERE L_SHIPDATE='1994-09-12';

This runs in 387 milliseconds for me.

This shows the power of RESULT_SCAN() - response time that is that much better is generally something to be celebrated.

Negative Example

RESULT_SCAN() isn't always faster, though. Why? Well, the result set itself is something that has to be scanned. Snowflake does not keep statistics on the file or files that make up a result set, so if additional logic is highly filtering on a large result set, it means that the entire result set is scanned to apply that filtering. Filtering predicates against data in micro-partitions can benefit from micro-partition pruning, scan less data, and return faster.

Let's explore this with an example. Let's start with a query to base our RESULT_SCAN() on:

SELECT L_ORDERKEY
       , MIN(L_SHIPDATE) as start_date
       , SUM(L_EXTENDEDPRICE-L_DISCOUNT+L_TAX) AS total_price
  FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000.LINEITEM
 GROUP BY L_ORDERKEY
  ;

This ran in about 11 minutes and 23 seconds for me, on an XS warehouse.

Next, let's look at a query to filter this down futher, against the same base table:

SELECT  L_ORDERKEY
        , SUM(L_EXTENDEDPRICE-L_DISCOUNT+L_TAX) AS total_price
  FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000.LINEITEM
 WHERE L_ORDERKEY=2884600160
 GROUP BY L_ORDERKEY
 ;

This ran in 43 seconds for me, on an XS.

Finally, if we rewrite the query to run against the RESULT_SCAN():

SELECT  L_ORDERKEY
        , total_price
  FROM TABLE(RESULT_SCAN('01be4f44-0206-36ed-000e-4a6b0005502e'))
 WHERE L_ORDERKEY=2884600160
 ;

This took a whopping 4 minutes and 14 seconds for me when I ran it on an XS warehouse.

In this case, accessing the result cache was slower than actually running the query against the base table. Being able to push the predicate down to the table scan in this case was faster than having to scan the entire result set looking for the right rows.

Query Profile

The query profiles for result set reuse make it very clear what happened. When Snowflake chose to use the result set cache for you, the query profile looks something like this:

That's it, that's the entire query profile.
If we explicitly use RESULT_SCAN() to access the results, it looks more like this:

Note that there are two steps here, and this one is the more complicated one, showing an "InternalObject" and the columns returned, not even taking note of our filter.

Summary

Targeted use of RESULT_SCAN() can improve performance, but must be carefully tested to ensure it has the desired affect. Simply doing a SELECT * on a large table and applying filters using RESULT_SCAN() on that result may not improve performance, and may, in fact, make performance worse. Like many areas of Snowflake performance, testing is the only way to really understand the performance impact.