Optimizing Chart Query Performance in YouShu BI: Data Query Principles, Intelligent Caching, Query Merging, and Diagnostics

Introduction – The article presents an overview of YouShu BI chart query performance optimization, outlining the data query principles and the subsequent improvements.

1. Data Query Principles – Visual analysis consists of three stages: data ingestion, data modeling, and report creation. YouShu BI supports over 30 data sources, drag‑and‑drop Join/Union modeling, custom SQL, calculated fields, and metadata definitions.

2. Intelligent Caching Design – To handle high concurrency, large data volumes, and offline (T+1) data, a two‑level cache is introduced: the first‑level caches front‑end query results per chart, while the second‑level caches the generated SQL results for reuse across similar visualizations. The cache architecture includes a scheduler, an executor, and a monitor, with support for user‑specific caches and ROI‑based priority calculation.

3. Core Query Capabilities – Basic functions include sorting, filtering, aggregation, dictionary lookup, calculated fields, and grouping. Advanced features cover chart linking, drill‑down, map calculations, row‑level permissions, and cross‑view granularity.

4. Query Merging and Optimization – By constructing query‑view objects at the report level, multiple similar chart queries are merged, reducing redundant SQL execution. The process is transparent to the front‑end and includes cache reuse and a query interceptor to ensure a single dispatch per view.

5. Additional Optimizations – Includes value‑acceleration (dynamic/static values), partition‑filter optimization using metadata, query tiering, front‑end rendering prioritization, SQL generation enhancements, and MPP acceleration via data extraction and materialized views.

6. Performance Diagnosis (Data Doctor) – Describes a diagnostic workflow that identifies slow charts, performs full‑link timeline analysis, applies rule‑based inference, and suggests concrete solutions. It also features a unified SQL profile analysis layer for Impala and ClickHouse, highlighting slow scans and joins.

Conclusion – Performance is a core competitive advantage for BI products; solving issues requires scenario‑driven analysis, empowering users with self‑service tools, and leveraging techniques such as materialized views, value acceleration, and partition filtering.