How Materialized Views Cut Log Query Times from Seconds to Milliseconds

Background

Developers handling backend monitoring often face slow log queries when data volume grows, leading to timeouts, alerts, and degraded user experience.

Solution Overview

We introduced Alibaba Cloud Log Service (SLS) materialized views in a large‑scale business scenario and measured performance before and after the change.

Case 1: High‑concurrency SDK “bombardment”

Pain point : each request had dynamic conditions; average query took 4100 ms, causing thread‑pool backlog and eventual timeouts.

SQL before materialization:

select column1, column2, column3,
       (timestamp - timestamp % 3600) as time_slot,
       count(*) as cnt,
       avg(metric_val) as avg_lat
  from log
 group by column1, column2, column3, time_slot;

After creating a materialized view, query time dropped to 46 ms – an 89× improvement, and latency remained stable under any SDK concurrency.

Case 2: De‑duplicated statistics

Pain point : count(distinct hash_val) on large tables took 16.8 s, making the feature unreliable.

SQL before:

select project_id,
       count(1) as event_cnt,
       count(distinct hash_val) as issue_cnt
  from log
 group by project_id;

Materialized view reduced execution to 2.2 s, an 8× speed‑up, making the feature reliably available.

Case 3: Year‑over‑year latency comparison

Pain point : a complex query with ts_compare and multiple sub‑queries took 54.3 s, often timing out.

SQL before:

type:read|
  select time,
         diff[1] as day1,
         diff[2] as day2,
         diff[3] as day3,
         diff[4] as day7
    from (
      select time,
             ts_compare(avg_latency,86400,172800,604800) as diff
        from (
          select avg(latency) as avg_latency,
                 date_trunc('hour', __time__) as time
            from log
         group by time
        )
    )
 order by time;

After materialization, the same analysis completed in 958 ms – a 56× improvement, turning an unusable query into a responsive dashboard.

ROI and Practical Guidance

High utilization: the three views served over 10,000 queries per day.

Negligible storage cost: extra storage is less than 0.1 % of raw log storage.

Three scenarios where materialized views are most effective:

Extremely slow queries with heavy deduplication or percentile calculations.

Interactive dashboards requiring sub‑second response.

High‑concurrency environments where pre‑computed results prevent service overload.

Conclusion

Materialized views in SLS can transform multi‑second or minute‑long log queries into millisecond‑level responses, dramatically improving reliability and user experience while incurring minimal cost.