Performance and Query Optimization
Reading a query profile
Optimization starts with the profile: look for partitions scanned versus total, bytes spilled to local or remote storage, join explosion, and queueing. Pruning is the highest-leverage fix. Clustering keys help large tables filtered on high-cardinality columns; check clustering depth (average overlapping partitions) and cluster only when depth is high and queries filter on the key. The Search Optimization Service helps point lookups and selective equality or substring predicates. Query Acceleration Service offloads scan-heavy portions of queries.
Caches
- Result cache: 24 hours, account-wide, invalidated by DML on underlying tables; not used for non-deterministic queries or certain functions.
- Local warehouse cache: survives only while the warehouse runs; requires the same warehouse; suspending drops it.
- Metadata cache in cloud services.
Traps
- Assuming a query re-runs instantly after data changes (result cache invalidated).
- Adding a clustering key to a small table or a low-selectivity column - cost without benefit.
- Resizing up for a concurrency problem, or adding clusters for a single slow query.
- Ignoring spillage to remote storage, which is the signal to resize up.
Worked example: a query spills 80% to remote disk and scans 100% of partitions. First add a clustering key on the date filter; if spillage persists, double the warehouse. Rule of thumb: prune first, then cluster, then resize.