Try to ensure that queries hit the cache

Whether for real-time or offline data, as long as queries do not always require the latest data, there is an opportunity to hit the cache.
Reason: The vast majority of queries that hit the cache can return results within 1 second.

• Offline data is generally updated on a daily basis. If there are repeated queries within a day, all queries after the first one can hit the cache.
• Real-time data is updated more frequently. However, if it is not necessary to see the latest data with every query, you can still set a cache validity period, such as 15 or 30 minutes. This helps improve query efficiency and reduce cluster load.

Before July 1, check whether random sampling is enabled for the chart. Random sampling does not attempt to use the cache and has poor performance. If sampling is required, you can choose the default sampling method.
After July 1, you can manually set whether to enable caching in the chart settings on the visualization page.

• Caching can be enabled for all offline data. DataWind will intelligently determine whether the data has changed and decide whether to update the cache, ensuring that expired data is not cached.
• Real-time data has a default cache duration of 5 minutes. Since this policy was launched in May, no users have reported experiencing data latency. Under normal circumstances, there is no need to disable it.
  If higher latency is acceptable, it is recommended to increase the cache duration setting to optimize query performance.
  In addition, try to avoid using functions such as now() and today(). If these functions are included in the query, caching cannot be enabled on the backend in order to ensure data accuracy. The today() function can later be replaced by a parameter. For now, consider whether it is equivalent to p_date and try to use it as a replacement.

Reduce the number of days in query partitions.

Query as few days as possible, and reduce the amount of data queried by minimizing the number of partition days.

In visual queries, reduce the number of days in the p_date filter condition, and reduce the number of days in dashboard filters.

Minimize or replace time-consuming operators.

Time-consuming operators such as countd, uniqExact, quantileExact, arrayhas, like, match, jsonExtract, and long case when statements can all lead to slow queries, and the query efficiency of some operators cannot be improved by upgrading cluster configurations.

1. If it is not necessary to ensure completely accurate results, functions such as countd, uniqExact, and quantileExact can be replaced with approximate calculation functions such as uniq and quantile, sacrificing some precision to improve query efficiency.
2. For functions such as json, case when, and like, consider materializing columns at the data warehouse layer instead of calculating them during queries.
3. For functions such as arrayhas and arrayset, consider creating indexes in advance.
   In general, minimize the use of the above functions, or place metrics that use these functions in a separate chart to avoid slowing down the query speed of the entire chart.

Use time-consuming analysis features less frequently

Some analysis features provided by DataWind may have high latency in large data volume scenarios, such as LOD, period-over-period analysis, comparison, detail totals, pivot table subtotals, and totals.
Reason: The implementation of these features may involve complex joins, require computing on all data, or need multiple calculations to assemble results.

1. For comparison and period-over-period analysis, try to limit the number of groups, and consider setting commonly used dimension items as sharding keys to make the join process local.
2. For Fixed, if filter conditions can be added, set dimension filters. There are currently no particularly effective optimization methods for other LOD queries.
3. For functions such as total and percentage, it is best to base calculations on the currently displayed data rather than on all data.
   Overall, use the listed analysis features with caution when dealing with large data volumes (single partition > 100 million rows).

Set sampling

For scenarios such as analyzing proportions, trends, avg, and quantiles, consider using the sampling feature.
Reason: Sampling can reduce the amount of data queried, improve query speed, and lower resource consumption. In scenarios where high accuracy is not required, such as trends or proportions, sampled data does not affect the analysis of results.

Set the sampling ratio on the visualization page (prefer stable sampling before 6/25). Sampling reduces the amount of data read and affects sum and count results, but does not impact pie chart proportions, line chart trend analysis, or operators such as avg and quantile.
Sampled data loses precision and is only suitable for approximate analysis in specific scenarios.

Reduce dimensions

Most queries involve no more than five dimensions. Having too many dimension items will result in reading too many columns and increase computation costs.

Remove unnecessary dimensions