Query Optimization Framework
Bell Canada
A rework of how a production reporting job reads a 23-million-row device inventory table on Teradata. The old query recomputed a giant join on every run; the new one computes it once, stores the result, and reads that instead.
The nightly job was timing out and breaking its SLA — and it was dragging down the shared production environment for everyone else.
- 01The whale join
- Broke
- A reporting query against the 23M-row device table ran 12 minutes, ate shared production resources, and started timing out overnight.
- Why
- The job joined the full inventory table to the full classification table before filtering anything — join everything, filter later — and recomputed that from scratch on every single run.
- Fix
- Flipped the order. Drive from the small, filtered fact table, push the filters down into the lookups, and materialize the pre-aggregated result once. Runtime fell from 12 minutes to 2.
83%faster runtime
23Mrow join removed
Evidence before confidence.