Query Optimization Framework
Bell Canada
A rework of how a reporting job reads a 23-million-row device inventory table on Teradata. The old query joined everything and filtered later; the new one filters the lookups first, pushes predicates down into them, and accounts for key skew.
A 12-minute join was too slow to ship; it had to be fast before go-live.
- 01The whale join
- Broke
- In pre-go-live testing, a reporting query against the 23M-row device table ran 12 minutes.
- Why
- The job joined the full inventory table to the full classification table before filtering anything (join everything, filter later).
- Fix
- Flipped the order: filter the lookups first, push the predicates down into them, and analyze the key skew. Runtime fell from 12 minutes to 2 before go-live.
83%faster join
Before
- Pre-tuning baseline
After
- Filtered lookups
- Predicate pushdown
- Skew analysis
23M-row join. 12 min → 2 min, before go-live.