How Slow Can You Go? Writing inefficient SQL in Snowflake

In my day job, I spend a lot of time looking at other peoples’ SQL and helping them make it run faster through a vast array of different techniques. I have been toying lately with seeing if I could take reasonable SQL and make it run really slow. If I know how to make a query faster, it stands to reason that I also know how to make it run slower.

Function of This SQL

I’ve been working with a lot of SQL lately where the goal is to find most or all of the columns for the row that is the latest for some given dimension (maybe customer id or category). I sometimes play around with a couple of different patterns that can be more efficient depending on the other work being done in a query. It is very rare to be working with queries as simple as the ones in this article, and therefore there can be reasons why one pattern is better or worse.

I’ll be using the SNOWFLAKE_SAMPLE_DATA database, which should be available in every free trial Snowflake account and most customer accounts. This means that you can run the SQL here, though be aware of the cost. I’m only working with a trial account, and near the end of a month, so I don’t mind if I run out of my free credits in playing with this. Pretty sure I ran through $200 in credits for this article alone.

Any timings that I share here can also be subject to the normal variations of any cloud-based MPP system. Your results are likely to be similar, but the further you get in time from the time of this post, the more likely Snowflake has improved the performance of Snowflake, so they’re likely to be different.

I’m also using an X-Small dedicated warehouse here.

Initial Starting Point SQL

Here’s the starting point for this experiment:

use snowflake_sample_data.tpch_sf10;
alter session set use_cached_result=FALSE;
SELECT o_custkey 
    , MAX(o_orderdate) OVER (PARTITION BY o_custkey ORDER BY o_totalprice) AS latest_this_cust
    , LAST_VALUE(o_totalprice) OVER (PARTITION BY o_custkey ORDER BY o_orderdate, o_totalprice) AS order_amount
FROM ORDERS 
QUALIFY RANK() OVER (PARTITION BY o_custkey ORDER BY o_orderdate DESC NULLS LAST, o_totalprice DESC NULLS LAST) = 1
ORDER BY o_custkey
    , latest_this_cust
;

The only reason I am setting USE_CACHED_RESULT to FALSE here is because I am testing this query alone in isolation, and in this case I want to eliminate super-fast results from the result set cache. If I were doing load testing, throughput testing, or trying to simulate a real workload, I would want to leverage the result set cache as a part of that testing.

I also am not talented enough to eyeball a million records to see if they are the same in subsequent queries, so I’m going to leverage the HASH_AGG function to do that for me. This hashes my result set to let me answer with a reasonable degree of certainty the question “are these result sets the same?

Running this query through hash_agg, I get this:

Note that when using functions like RANK and LAST_VALUE you have to be very specific to get the same exact results back. Different results can happen when rerunning the exact same query if removing the NULLS LAST or not specifying conditions to the point where exactly one row is ranked #1. If, in this example, I only specified LAST_VALUE(o_totalprice) OVER (PARTITION BY o_custkey ORDER BY o_orderdate) instead of LAST_VALUE(o_totalprice) OVER (PARTITION BY o_custkey ORDER BY o_orderdate, o_totalprice), I got differing results from run to run, even on this small result set. This is expected with SQL and common in MPP systems like Snowflake. Many other databases will also not guarantee order in functions like RANK unless explicitly specified.

Note that we got this answer in 5.8 seconds on an XS warehouse. If I were really testing here, I’d want to run this multiple times on multiple different days and likely consider the average (or if my requirements called for it, the p90 or some other statistical calculation based on the results).

Reasonable Alternatives

There’s one rewrite that stands out to me, here:

SELECT o_custkey
    , o_orderdate
    , o_totalprice
from orders
QUALIFY RANK() OVER (PARTITION BY O_CUSTKEY ORDER BY O_ORDERDATE DESC NULLS LAST, O_TOTALPRICE 
DESC NULLS LAST) = 1
order by o_custkey, o_orderdate;

We can see from the HASH_AGG that this gets us the same result:

Note that this took only 1.1 seconds on an XS warehouse! I call that kind of improvement a good day, most of the time. But let’s try one more variation. I love CTEs, but often see people using them in odd ways. Here’s a rewrite with reasonable CTEs:

WITH latest_order_dates AS (
SELECT o_custkey
    , MAX(o_orderdate) AS latest_orderdate
    FROM orders
    GROUP BY o_custkey
)
, latest_orders AS (
SELECT o.o_custkey
    , MAX_BY(o_orderkey, o_totalprice) AS latest_order
    FROM orders AS o 
        JOIN latest_order_dates lod
            ON o.o_custkey = lod.o_custkey
                AND o.o_orderdate=lod.latest_orderdate
    GROUP BY o.o_custkey
)
SELECT ord.o_custkey
    , ord.o_orderdate
    , ord.o_totalprice 
FROM latest_orders AS lo
    JOIN orders AS ord 
        ON lo.latest_order=ord.o_orderkey
ORDER BY o_custkey
    , o_orderdate
;

We can see from the HASH_AGG that this again gets us the same result:

This also ran in the same amount of time as our last rewrite – 1.1s.

Performance Sidebar

Interestingly, the query profiles of these two solutions look very different, even if they get the same result in the same amount of time. Here’s the query profile of the first query in this section:

And here is the query profile of the second query in this section:

Basically the main reason they’re so similar in performance is that we’re dealing with just tiny amounts of data here. Based on these query profiles, I would be interested to see how each version scales with larger data sets. The second one reads more micro-partitions, making me wonder if that would be a problem as the data sets grow. The first one has most of the logic in a window function, which may need more memory as the data sets grow. Using the SNOWFLAKE_SAMPLE_DATA database makes that easy to test out.

Queen of Slowness

I’ve taken a starting query and rewritten it in two faster ways. Now let’s see what I can do about a really slow one. I happen to not be fond of some of the ARRAY_* functions in Snowflake from a performance perspective. I think they’re working on making them faster, but I could absolutely use one here to get the same result set in a slower way. That looks something like this:

WITH latest_order_dates AS (
    SELECT o_custkey
        , MAX(o_orderdate) AS latest_orderdate
    FROM orders
    GROUP BY o_custkey
)
, latest_orders AS (
    SELECT o.o_custkey
        , MAX_BY(o_orderkey, o_totalprice) AS latest_order
    FROM orders AS o 
        JOIN latest_order_dates AS lod
            ON o.o_custkey = lod.o_custkey
                AND o.o_orderdate=lod.latest_orderdate
    GROUP BY o.o_custkey
)
SELECT o.o_custkey
    , o.o_orderdate
    , o.o_totalprice
FROM orders AS o 
WHERE ARRAY_CONTAINS(o.o_orderkey,(SELECT ARRAY_AGG(latest_order) 
                                    FROM latest_orders) )
ORDER BY o_custkey, o_orderdate
;

Using HASH_AGG we can see this gets the same result:

But look at the time – over 27 HOURS!

Summary

There are a lot of acceptable ways to get the same result set with SQL in Snowflake. Sometimes there is a faster way. Sometimes the fastest way will change as data scales. There are also some really bad or slow ways to get the same result set. Think about the cost difference between running for 1.1s on an XS and running for 30 hours!

Anyone have another way to write this to make it even slower? Would love to hear any experimentation in the comments!