Mastering ODPS SQL Performance: From Logview to Advanced Optimizations

1. Overview of SQL Execution in ODPS

When an SQL statement is submitted to ODPS (Alibaba's MaxCompute), it goes through a queue, is parsed, compiled into a series of Fuxi Jobs, Tasks, and Instances, and finally runs to produce results. Understanding this pipeline helps identify where time and resources are spent before attempting any optimization.

2. First Step: Using Logview

Logview displays the Fuxi Job DAG, each Task, and its Instances. The hierarchy is:

SQL → multiple Fuxi Jobs

Each Job → multiple Fuxi Tasks (identified by prefixes M, R, J, C)

Each Task → multiple Fuxi Instances

Key panels in Logview:

Latency : shows instance runtime.

Summary : resource cost (CPU minutes) and monetary cost (resource cost × cpu / 1440 × 1.52 CNY).

Typical troubleshooting steps:

Identify long‑running Instances.

Check Input/Output sizes for data explosion.

Inspect Long‑Tails and Data‑Skews for skewed partitions.

Locate the exact SQL fragment via StdOut (click the longest‑running Task, view its operator graph, then match the operator in StdOut).

Useful visual tricks:

Switch the default Progress Chart to the Task‑Time Heat Chart to spot the orange‑colored longest‑running Task.

Use the Latency Chart – wider blue lines mean longer runtime, higher vertical position indicates later start, and steep slope signals resource acquisition delay.

3. Common Reasons for Slow SQL

Resource contention / low priority : Check Queue Length and Waiting Queue in Logview; increase priority with set odps.instance.priority=0;.

Unreasonable parameter settings : Adjust mapper split size, join/reducer instance counts, enable skew join ( set odps.sql.skewjoin=true;), etc.

Poor SQL formulation : Avoid SELECT *, push partition filters to the WHERE clause, and consider sub‑queries for large tables.

Data skew : Hot keys in joins or group‑by, many nulls, or excessive distinct counts cause imbalance; mitigate with map‑join hints, skew‑join settings, or manual key redistribution.

Large data volume : Increase instance count or use higher‑spec instances; prune data early with selective filters.

4. Practical Optimization Techniques

4.1 Use MapJoin Hint for Small‑Table Joins

Explicitly hint a small table to be broadcast in the map phase:

select ds, count(distinct user_id) as city_user_cnt
from (
  SELECT /*+ MAPJOIN(c) */ b.ds, b.user_id
  FROM table_b b
  INNER JOIN table_c c ON b.district_id = c.district_id
) 
group by ds;

MapJoin works for inner, left, and right joins when the small table fits in memory; memory limit can be raised with set odps.sql.mapjoin.memory.max=2048;.

4.2 Replace COUNT(DISTINCT) with Double GROUP BY

When the distinct key distribution is uniform, rewrite as two‑stage aggregation:

-- original
select ds, count(distinct item_id) as item_cnt
from item_table
group by ds;

-- rewritten
select ds, count(item_id) as item_cnt
from (
  select ds, item_id
  from item_table
  group by ds, item_id
) t
group by ds;

4.3 TRANS_COLS for Multi‑Distinct Aggregations

TRANS_COLS expands multiple distinct columns into separate rows, allowing a single aggregation pass:

select TRANS_COLS(3, city_id, client_code, is_new_user, user_id, imei) as (idx, city_id, client_code, is_new_user, visitor_id);

This reduces the number of distinct aggregations and can cut runtime by up to 50% in the demonstrated case.

4.4 Split Large Temporary Tables

Break down massive intermediate tables into smaller chunks to avoid long waiting times for resources.

4.5 Apply Appropriate UDFs

Custom functions like rb_build_or_agg can accelerate specific metrics, but avoid UDFs that perform heavy loops; replace them with built‑in functions when possible.

4.6 Hash Bucketing

Pre‑bucket large join tables to co‑locate rows with the same join key, reducing scan range:

alter table table_xxx clustered by (column_name) sorted by (column_name) into 2108 buckets;

Select the bucket count based on query patterns and data size.

5. Conclusion

Effective SQL performance tuning on MaxCompute combines a clear view of the execution pipeline (via Logview) with targeted adjustments—resource priority, parameter tuning, query rewriting, and physical data layout changes. By systematically applying the techniques above, practitioners can dramatically reduce runtime and cost for large‑scale analytical workloads.