尝试查询加速

This topic provides an overview of how a warehouse owner or administrator can use the query acceleration service to improve the performance of queries running on a warehouse. For complete details about query acceleration, refer to Using the Query Acceleration Service (QAS).

The query acceleration service offloads portions of query processing to serverless compute resources, which speeds up the processing of a query while reducing its demand on the warehouse’s compute resources.

当仓库具有异常值查询（即使用比典型查询更多资源的查询）时，Query Acceleration Service 还可以提高仓库其他查询的性能，因为异常值查询的额外计算需求已卸载到无服务器计算资源。

可能从 Query Acceleration Service 中受益的工作负载示例包括临时分析、每次查询的数据量不可预测的工作负载，以及具有大量扫描和选择性筛选器的查询。

查找查询加速的候选项

您可以使用函数或查询来确定启用 Query Acceleration Service 是否可以提高查询或查询集的性能。

函数：确定特定查询是否可能受益

The SYSTEM$ESTIMATE_QUERY_ACCELERATION function allows you to check whether a specific query is a good candidate for query acceleration service.

该函数接受查询 ID 作为其唯一实参。将函数封装在函数中 PARSE_JSON 更便于解读结果。例如：

SELECT PARSE_JSON(system$estimate_query_acceleration('8cd54bf0-1651-5b1c-ac9c-6a9582ebd20f'));

If a query is a candidate for query acceleration service and has not yet been accelerated, the status of the response is eligible. A status of ineligible indicates the query will not benefit if you enable query acceleration service for a warehouse.

For additional information about evaluating the query acceleration service for a particular query, including estimated execution times for different scale factors, refer to the reference documentation.

查询：跨仓库的最佳查询候选项

此查询通过计算符合加速条件的查询执行时间，来确定过去一周内可从 Query Acceleration Service 中受益最多的查询。

SELECT query_id, eligible_query_acceleration_time
  FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ACCELERATION_ELIGIBLE
  WHERE start_time > DATEADD('day', -7, CURRENT_TIMESTAMP())
  ORDER BY eligible_query_acceleration_time DESC;

查询：按执行时间划分的最佳仓库候选项

此查询可确定过去一周内可从 Query Acceleration Service 中受益最多的仓库。对于每个仓库，它会计算出符合加速条件的总查询执行时间。

SELECT warehouse_name, SUM(eligible_query_acceleration_time) AS total_eligible_time
  FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ACCELERATION_ELIGIBLE
  WHERE start_time > DATEADD('day', -7, CURRENT_TIMESTAMP())
  GROUP BY warehouse_name
  ORDER BY total_eligible_time DESC;

查询：按查询次数排序的最佳仓库候选项

此查询可确定过去一周内符合 Query Acceleration Service 条件的查询次数最多的仓库。

SELECT warehouse_name, COUNT(query_id) AS num_eligible_queries
  FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ACCELERATION_ELIGIBLE
  WHERE start_time > DATEADD('day', -7, CURRENT_TIMESTAMP())
  GROUP BY warehouse_name
  ORDER BY num_eligible_queries DESC;

成本注意事项

查询加速仓库租用的无服务器计算资源使用的 credit 与仓库使用的 credit 无关，需要单独计费。

Query Acceleration Service 是为整个仓库启用的，但与扩大仓库规模不同，它仅用于受益于计算能力提高的查询。对于运行混合工作负载的仓库来说，这可能具有成本效益，因为不需要额外计算资源的查询不会产生使用较大仓库的额外成本。

You can use the warehouse’s scale factor to help control the cost of the query acceleration service. This scale factor, which is a multiplier of the warehouse’s credit consumption, sets a limit on how much serverless compute can be used by a warehouse. For example, if a warehouse has a scale factor of 5, the credit consumption rate of serverless compute resources cannot exceed the consumption rate of the warehouse by more than 5 times.

You can use the SYSTEM$ESTIMATE_QUERY_ACCELERATION function to gauge how the scale factor affects the performance of a query.

要在不考虑成本的情况下最大限度地提高性能，请将比例因子设置为 0。

如何启用 Query Acceleration Service¶

To enable the query acceleration service with a maximized performance boost, use the ALTER WAREHOUSE command as follows:

ALTER WAREHOUSE my_wh SET
  ENABLE_QUERY_ACCELERATION = true
  QUERY_ACCELERATION_MAX_SCALE_FACTOR = 0;