Snowflake is a modern data warehousing platform that is designed to meet the challenges of changing data landscape. One of the key features of the Snowflake is its Query Acceleration Service, which is designed to help users improve the performance of queries. In this blog, we will look at how the Query Acceleration Service works and how it can help you boost the performance of your queries in Snowflake.

Snowflake Query Acceleration Service is a feature that enables users to accelerate their queries by providing a high-performance computing environment. The service uses a combination of hardware and software to optimize query execution and reduce the time it takes to run queries and improve overall performance.

The Query Acceleration Service also optimizes query execution by using multiple processors and cores to run queries simultaneously. This parallel processing of queries significantly reduces the time it takes to run queries, making the entire process faster and more efficient.

There are several benefits to using the Query Acceleration Service in Snowflake, including:

The Query Acceleration Service helps you save time and resources by reducing the time it takes to run queries. This means that you can get more done in less time, freeing up resources to focus on other important tasks.

By reducing the time it takes to run queries, the Query Acceleration Service can help you save money on computing costs. The faster your queries run, the less time you will need to spend on computing, which results in significant cost savings.

• With automatic scaling, QAS can dynamically adjust resources allotted to the virtual warehouse. It will Auto-scale up/down resources based on query requirements, this means that users don’t have to manually adjust the resources to optimize the performance of queries.

QAS offers greater control to users by allowing them to set a scaling factor. The scaling factor controls the number of resources allotted to a virtual warehouse, giving users cost control benefits, as users can limit the number of resources allocated to the warehouse to avoid incurring unnecessary costs.

With fast query performance users experience responsive and efficient analysis. It improves user satisfaction and productivity.

monitoring users can better understand how much credit is being consumed by each query, including those that are accelerated by QAS. It enables users to track and manage their credit usage, ensuring that they don’t exceed their credit limits and incur expected limits.

To use the Query Acceleration Service in Snowflake, you simply need to enable it for your account. This can be done through the Snowflake web Interface or through the Snowflake API. Once you have enabled the Query Acceleration Service, you can start using it to optimize your queries and boost your performance.

It is not enabled by default, but it can be quickly deployed against a warehouse using:

alter warehouse TEST set
enable_query_acceleration = true
query_acceleration_max_scale_factor = 16;

The above query Activates the Query Acceleration Service which expands the capabilities of the virtual warehouse to allocate up to 16 times the size of the original warehouse. For example, an XSMALL warehouse with just one database server will automatically expand to 16 servers, while a large warehouse with 8 database servers would grow to 128 servers. This allows you to run massive queries on servers. You also have the option to set the scaling factor to zero, which gives Snowflake flexibility to scale QAS as large as necessary for the query.

· Max_scale_factor is an upper bound to the amount of compute resources a warehouse can lease for query acceleration.
· If the scale factor is not set, then the default value is 8.
· Setting the scale factor to 0, eliminates the upper bound limit and allows queries to lease as many resources as necessary.

Large and Complex queries: Queries that require a large amount of data processing and manipulation, such as those that involve complex aggregations, join operations, and data transformations, are good candidates for acceleration with QAS. By offloading some of the processing to dedicated compute resources, QAS can help speed up the execution of these types of queries and improve overall performance.
Data exploration queries: Queries that are used to explore and analyze large data sets, such as those that generate reports or perform data analysis. These types of queries, typically involve scanning large amounts of data and performing complex calculations, so QAS can help improve their performance and reduce query execution time.
Time-sensitive queries: Queries that are required to run in real-time, such as those that support business-critical processes, can also benefit from QAS, by accelerating these queries, QAS can help ensure that results are delivered quickly and accurately, which is essential for time-sensitive applications.
Repeatable queries: Queries that run repeatedly, such as those used for scheduled reporting or data analysis, can also benefit from QAS. By accelerating these queries, QAS can help reduce the amount of time required to run them, which can be especially useful for organizations that need to perform these types of queries frequently.

Identifying Warehouses and Queries that benefit from query acceleration

To identify queries that benefit from query acceleration you can use:

SYSTEM$ESTIMATE_QUERY_ACCELERATION

select parse_json(SYSTEM$ESTIMATE_QUERY_ACCELERATION(‘Query_ID’));

select parse_json(SYSTEM$ESTIMATE_QUERY_ACCELERATION(‘Query_ID’));

{
“estimatedQueryTimes”: {},   
“originalQueryTime”: 816.106,   
“queryUUID”: “01aa579d-3200-a7b3-0003-51900011402”,   
“status”: “ineligible”,   
“upperLimitScaleFactor”: 0
}

Overall, this response means that the QAS cannot provide any estimated query times or scaling factors for it. The original query time is 0, indicating that it may not have run successfully. It also indicates that the query with a specific Id is not found.

{
“estimatedQueryTimes”: {},
“originalQueryTime”: 53.404,
“queryUUID”: “01aa7382-3200-a91b-0003-519200040046”,
“status”: “accelerated”,
“upperLimitScaleFactor”: 0
}

The response indicates that the query is ineligible for QAS and the original time cannot be reduced by applying QAS.

• If the output query is accelerated, then the status of QAS is accelerated

It is a system-defined view in the Snowflake that provides information about the queries that are eligible for query acceleration. This view is used to identify the queries for acceleration and to determine the acceleration eligibility of a particular query.

By querying this view, users can get insights into the queries that are eligible for acceleration and optimize their queries accordingly. The view provides a useful way to understand which queries are the best for acceleration in Snowflake.

This SQL query retrieves data from four tables -store sales, store returns, item, and date dim and retrieves aggregated data on sales and returns of a specific brand of products.

Overall, the query retrieves aggregated data on sales and returns of a specific brand of products, grouped by year, brand ID, and brand name, and sorted by certain columns. The query results are limited to 200 rows.

select d_year AS ss_sold_year, i.i_brand_id ,i.i_brand ,
sum(ss_quantity) ss_qty,sum(ss_wholesale_cost) ss_wc,sum(ss_sales_price) ss_sp
from store_sales s,store_returns sr,item i,date_dim d
where
s.ss_ticket_number = sr.sr_ticket_number and s.ss_sold_date_sk = d.d_date_sk and
s.ss_item_sk = i.i_item_sk and i.i_manufact_id = 939 and d_qoy = 4
group by d_year, i_brand_id, i_brand
order by 1,4,5,2
limit 200;

• After enabling the Query Acceleration Service, you can check the Query Profile’s overview panel to observe the impact of query acceleration on the query performance.
• This panel displays various statistics of query, including the results of multiple advanced operations.
• If there are multiple accelerated operations in a query, the results are combined in this panel to show the total amount of work done by the Query Acceleration Service.
• The provided screenshot is an example of the type of statistics that are displayed in the Profile Overview panel.
  Partitions scanned by service- This shows a number of files sent for scanning to the QAS.
• Scans selected for acceleration- This indicates the number of table scans that have been chosen for acceleration.

• When Query Acceleration is enabled for an account, the billing page on the web interface provides information about the usage of the services across all warehouses in the account.
• This includes dedicated warehouses in the account. This includes a dedicated warehouse called QUERY_ACCELERATION that displays all the credits consumed by the service.
• The provided screenshot shows an example of the billing information that appears on the page for the QUERY_ACCELERATION warehouse.

Conclusion:

Snowflake’s Query Acceleration Service is a powerful tool for improving the performance of your queries. By caching frequently executed queries and optimizing query execution, the Query Acceleration Service can help you get the results you need more quickly and efficiently. If you are looking to boost the performance of your queries in Snowflake, the Query Acceleration service is a must-have tool.