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