Two minutes to under a second
A short note on one slow query in a CRM at DCS Card Center, and the rewrite that fixed it.
Several processes in our CRM depended on one API call that fetched data from the database. That call took one to two minutes. The query joined two tables in full and only narrowed the result down afterwards, so the database built a large intermediate result and then discarded most of it.
I rewrote the query so a CTE first selects only the rows the process actually needs. The join then runs against that small subset instead of the whole table.
before
table A (all rows) ─┐
├─ JOIN ─▶ large result ─▶ narrow ─▶ few rows
table B (all rows) ─┘
after
table A ─▶ CTE: narrow ─▶ small subset ─┐
├─ JOIN ─▶ few rows
table B ────────────────────────────────┘ The work a join does grows with the number of rows going into it. Shrinking one side before the join cut that work, and the API went from one to two minutes to under a second.
The win wasn't the WITH keyword itself. It was changing
the order of the work. If the database had been narrowing the data
before the join on its own, the original query would already have
been fast. Restructuring the query made that order explicit. When a
query is slow now, the first thing I check is how many rows reach
each join.