Query Boost: Query Acceleration Service in Snowflake

Query too slow? Snowflake can help. Snowflake comes with a set of in-built tools to improve query performance. One of these is the Query Acceleration Service, which can “auto-magically” detect when a difficult query is holding up a warehouse and call in extra compute to help save the day! The Query Acceleration Service is supported in Snowflake Enterprise Edition or higher. Let’s dive in.

How does the Query Acceleration Service work?

I’m glad you asked! The short answer is… it’s a secret. Snowflake is a proprietary piece of software and they don’t want competitors using their ideas and technology. The long answer is… it’s a secret but we can still try to understand it and its limitations.

When you run a query in Snowflake, you depend on a unit of compute called a Virtual Warehouse. Each warehouse can only process one query at a time, meaning that if one particularly chunky query needs to be executed, it could hold up all other queries in the queue, no matter how small they are.

A virtual warehouse is processing queries. Query A is completed. A large query, Query B, is being processed and is holding up the queue. Queries C and D are in the queue behind Query B. — *Without Query Acceleration, a virtual warehouse could be held up by a single difficult query.*

If the QAS is enabled, Snowflake will detect these “outlier” queries and assign compute resources to speed up the process.

As before, a virtual warehouse is processing queries. Query A is completed. A large query, Query B, is being processed and is holding up the queue. Queries C and D are in the queue behind Query B. This time, however, the Query Acceleration Service detects the hold-up and assigns serverless compute to speed up the processing on the outlier query. — *The QAS Allocates serverless compute to outlier queries.*

Only some queries can be accelerated

Only some queries are eligible for acceleration. In its docs, Snowflake states that supported query types for acceleration include those using highly selective filters (WHERE) or aggregation (GROUP BY). Also, if a query contains a LIMIT clause, then it must also contain an ORDER BY.

Getting Started with Query Acceleration

Snowflake provides various tutorials and documentation pages to help you begin experimenting with QAS

Auteur

Edoardo Draetta

Dedicated Analytics Engineer with a strong background in physics, bringing a unique blend of analytical rigor and scientific acumen to the world of data-driven decision-making. With a passion for unraveling complex phenomena and a keen eye for patterns, my solid foundation in physics equips me with a deep understanding of quantitative analysis, statistical modeling, and computational techniques, enabling me to develop innovative solutions to intricate problems.

View all posts