A T-SQL report against the Warehouse computes, for every branch, the number of loan requests that occurred after that branch's most recent inspection date, using a correlated subquery inside the SELECT list that re-evaluates the inspection lookup for every branch row. The query is one of the slowest in the nightly report suite. Which rewrite is most likely to improve its performance while returning the same results?
Select an answer to reveal the explanation.
Short Explanation
A correlated subquery is like re-checking a reference book from scratch for every single row instead of looking it up once and reusing the answer. When the same lookup — each branch's latest inspection date — gets recomputed row by row, that repeated work adds up fast. Turning it into a join against a one-time-computed lookup set means the engine does the work once and reuses it, which is usually a much bigger win than any cosmetic tweak.
Full Explanation
A correlated subquery that depends on the outer row typically forces the engine to re-evaluate the inner logic once per outer row, and when that inner logic does its own aggregation — finding a branch's most recent inspection date — that cost is paid repeatedly rather than once; rewriting it as a join against a derived table or CTE that pre-computes each branch's latest inspection date lets the optimizer compute that lookup a single time and then match it efficiently against the outer rows, which is the standard fix for this exact pattern and preserves identical results. Simply wrapping the existing query in a CTE without restructuring the correlated logic doesn't change how many times the inner lookup executes, so it wouldn't meaningfully help — a CTE is a naming and readability construct, not an automatic performance guarantee. Adding an ORDER BY changes the order of returned rows, not how the join or subquery itself is executed, and doesn't reduce repeated work. Dynamic data masking changes what values authorized or unauthorized users see; it doesn't reduce the computational cost of evaluating a subquery per row. A caveat: this rewrite pattern assumes the pre-aggregated lookup can be computed once and reused safely, which holds here since the most-recent-inspection-date logic doesn't depend on anything else in the outer row. A concrete check: compare the query's execution plan before and after the rewrite, looking specifically for whether the inspection-date lookup appears once versus once per outer row.