Query performance is the efficiency of query execution: how quickly a query completes and how much compute, storage, and other resources it consumes. Snowflake executes many queries efficiently without manual tuning, but large, complex, or resource-intensive workloads may need investigation and targeted optimization. section 1.2.3 section 8.0
Start with evidence rather than changing table or warehouse settings blindly. Query history can identify the longest-running statements by total elapsed time, while the query profile exposes execution details such as partitions scanned, partitions total, and spilling. These measures reveal whether pruning occurred and where a query is likely spending resources. section 8.6.2 section 9.2.3
Improve query performance through the option that matches the observed workload:
- Write selective SQL. Name needed columns instead of using SELECT *, and filter early when possible so Snowflake processes less data and can exclude unneeded columns or micro-partitions. section 8.3.1 section 9.2.3
- Use clustering for large tables when typical queries retrieve a small portion of rows and repeatedly filter or sort by a suitable key. A useful key supports micro-partition pruning and has an appropriate number of distinct values. section 8.4.2
- Use search optimization for selective equality or IN filters on large, highly partitioned tables, particularly when a clustered table is filtered on a column other than its clustering key. section 8.5.2 section 8.5
- Size virtual warehouses against representative complex queries. Larger warehouses can improve execution when small warehouses cannot process the workload efficiently, but quick queries may show little difference. section 9.2
- Reduce spilling by rewriting large grouping and sorting queries to process fewer rows, such as by adding a relevant filter. Spilling writes data from memory to local disk, or potentially remote storage, and slows execution. section 9.2.3
- Manage concurrent workload when many users share a warehouse. Lowering MAX_CONCURRENCY_LEVEL gives each running query more resources, but can increase queueing and requires monitoring. section 9.4.2
Pruning a product filter with search optimization
A query groups sold quantity by store while filtering RETAILER_SALES on one product ID. When the table is clustered by store ID, the product filter initially scans nearly all micro-partitions. Search optimization creates a search access path that records which column values occur in each micro-partition. Re-running the query scans 955 of 1,271 partitions, showing improved pruning. This approach is appropriate only when the equality filter is selective and the table is large enough to benefit. section 8.5 section 8.5.2
select query_id, query_text, partitions_scanned, partitions_total, total_elapsed_time from SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY where TO_DATE(start_time) > DATEADD(day,-1,TO_DATE(CURRENT_TIMESTAMP())) order by total_elapsed_time desc limit 50;
Use this QUERY_HISTORY query to rank recent statements by elapsed time and compare scanned with total partitions before opening a query profile. section 8.6.2
Reducing spills by filtering earlier
A complex aggregation over more than 700 million retail-sales records groups and sorts store and product results. On an extra-small warehouse, adding a distance filter limits the data processed; execution time drops from 22 seconds to 16 seconds. The example illustrates a query rewrite before relying solely on more warehouse capacity. section 9.2 section 9.2.3
Listing 9.2 Amended query from listing 9.1 with filtered data
use warehouse BAKERY_WH_XSMALL;
select
store_id,
distance_km,
product_id,
sum(sales_quantity) as total_quantity
from RETAILER_SALES
where store_id in (
select store_id
from (
select store_id,
count(distinct product_id) as product_cnt
from RETAILER_SALES
where distance_km < 1000 #1
group by store_id
having product_cnt > 100
)
)
group by store_id, distance_km, product_id
order by distance_km;
Compare the filtered aggregation with its broader version to see how a predicate reduces the rows processed by grouping and sorting. section 9.2.3
