I am a huge fan of testing performance on either the actual production data or a full-sized copy of the data. With every database platform I’ve worked with in a 20+ year career, performance is often excellent at a smaller scale, but as the data grows, it elucidates problems in the SQL or data model that cause performance to suddenly fall off of a cliff. As data grows, sometimes an invisible threshold is crossed that causes an exponential slow down.
Snowflake makes testing on a full copy of the data easy through the separation of compute and storage. You really can test read-only workloads on the real production data by using a different compute warehouse against the production data. If full read-write is required, cloning tables works wonders, though that may eventually incur the cost of storing a fully separate copy of the data. Some customers are also concerned about the cost of the compute for testing against the full data.
No matter how much I want to be able to draw performance conclusions on small data sets that scale infinitely, it is hard to draw conclusions on a smaller data set that will scale linearly as the data volume increases.
Snowflake Sample Data
Snowflake provides some sample data that you can experiment with. I frequently use this data not just for this blog, but also for my day job to prove a point to myself or others. One nice component of this data is that it is offered at several different “scale factors”. This means that I can get my syntax and test cases working against a small data set and only then move up to a larger data set. I have played with a number of tables in this regard, but the TPCH ORDERS table is probably my favorite, just because I have spent a chunk of my career working with e-commerce databases, so thinking about it comes fairly naturally to me.
The smallest data set for this table in the TPCH_SF1 schema is only about 10 micro-partitions, so most queries return nearly immediately. I actually usually start at the TPCH_SF10 level, as it is 15 million rows and only about 32 micro-partitions. This is enough to sometimes be able to start seeing timing differences between the levels. As I want to scale my experiments, there are also SF100 and SF1000. At the SF1000 level, the table has over 3200 micro-partitions. This is still much smaller than many production data sets, but is enough data for problems and differences to become more obvious.
Example of Query Performance as Data Increases
I wanted to take a set of queries that I played with in a previous blog entry – How Slow Can You Go? Writing inefficient SQL in Snowflake – and show the differences that pop up as the data gets larger.
Scale Factor 10
There are three queries we’ll be looking at here. A slightly messy starting point, and two possible rewrites. The messy starting point looks like this:
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
;Running that on the _SF10 schema looks like this:
It runs in about 6.2 seconds on a warm XS warehouse, returning a million rows.
A re-write of that code that returns the same result looks like this:
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
;Running that on the _SF10 schema looks like this:
It runs in the neighborhood of 2 seconds on a warm XS warehouse, returning the same million rows.
A second re-write could look 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 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;Running that on the _SF10 schema looks like this:
It also runs in the neighborhood of 2 seconds on a warm XS warehouse, returning that same million rows.
If we look at the query profiles for the two rewrites, we see some differences. The query profile for the first rewrite looks like this:
One thing that stands out to me looking at this is that a chunk of time is taken up in the window function. I will be interested to see how this scales, as this node may need to use a fair amount of memory to function as the data size increases.
The query profile for the second rewrite looks like this:
The thing that stands out to me here is that we’re scanning the same table three times here. This may not do well as the data size increases, because so much more scanning will be done.
It is because of these differences in the plans that I am interested to try the same query at different data sizes to see what happens.
Scale Factor 100
I want to try each query against the _SF100 schema. This is ten times the data as the last tries, while keeping the warehouse size at XS.
Here’s the original query:
It runs in about 1 minute and 14 seconds on a warm XS warehouse. As expected, it returns 10X the result set of the smaller schema.
Now let’s try the first rewrite:
This rewrite ran in just 14 seconds. We can see that on the larger data set, this re-write makes an even larger difference than it did on the smaller data set.
The query profile for this one looks like this:
Our second rewrite looks like this:
It ran in just 10 seconds.
The query profile for this one looks like this:
We see a greater divide opening up between our two rewrites, with the second one starting to be noticeably faster than either the original or the first rewrite.
One thing I notice here is that at this data scale and with this warehouse size, we see data starting to spill from the warehouse memory on to local disk. Both queries are spilling fairly equal amounts. Local spilling is relatively normal.
Scale Factor 1000
At the largest data scale available in the snowflake sample data, our original messy query runs for about 18 minutes and 46 seconds:
We see significant spilling from memory to local storage – about 177 GB.
The first rewrite looks like this:
Just over 3 minutes. I’m not surprised by the jump in execution time because we’re at a point where an XS is a bit small to scan this many micro-partitions.
We also see spilling to local storage, though not nearly as much as with the original query at this size.
The second rewrite looks like this:
6 minutes and 44seconds – that means that the query that was the fastest at _SF100 is still better than the original at _SF1000, but significantly slower than our best rewrite.
The query profile looks like this:
This isn’t terribly surprising to me, as if the number of micro-partitions we’re having to scan is slowing us down when we scan the table once, when we scan the table three times, it is much worse. Instead of the ~10 GB of spilling to local storage, we’re seeing 84 GB of spilling to local storage.
In the real world, I’d want to use a larger warehouse at this data size.
Scaling In Different Ways
One thing to note here is that with the way this data set increases in size, I get 10X the results. It is entirely possible that the real data scaling direction might be to have 10X the data values for each order number, but to still have the same number of groups in a query like this. It matters which is more representative of data as you scale up.
Conclusions
We were trying two rewrites that were roughly equal at the smaller data size. As we moved up to the next scale factor (SF100), we saw that the second rewrite out performed the first rewrite. But as we moved up in data size yet again (to SF1000), the performance flipped, and the faster rewrite was the first one.
Should this frustrate us and cause us to just give up? Not at all. With real-world data and usage patterns, performance is something we should be frequently evaluating to make sure that what worked yesterday works today. Many times we can’t accurately predict the direction or distribution of data increasing. Choosing a reasonable data model, writing sound SQL, and trying different configurations of SQL to solve the same problem are excellent places to start. Learning to read the query profile and knowing some of the red and yellow flags to look out for are important skills to maintain performance and therefore moderate cost as data size increases.
Very good article