A KPI dashboard query joins eight normalized tables and takes too long to refresh. The underlying data is loaded once per night and the dashboard is read hundreds of times a day. Which approach best improves read performance for this workload?
Select an answer to reveal the explanation.
Short Explanation
When you read something a hundred times and change it once, it pays to prepare it ahead of time, like pre-cooking meals for the week. A denormalized reporting table does the joining once at night so the dashboard just reads. Normalizing further sounds tidy, but you'd be adding joins, not removing them.
Full Explanation
Denormalization deliberately reintroduces redundancy, combining data from several normalized tables into a wider structure so frequent queries need fewer joins. It suits workloads that are read-heavy and change predictably, such as a dashboard over data loaded once per night: the nightly process can populate the reporting table right after the load, and the dashboard then reads a single, pre-joined table. The trade-off is extra storage and the responsibility to keep the redundant copy synchronized, which is manageable when updates come from one scheduled process. Normalizing further splits data into still more tables, increasing the number of joins the dashboard must perform. Replacing INNER JOINs with LEFT JOINs changes the result semantics by preserving unmatched rows and generally does not make the engine do less work. Raising the isolation level to SERIALIZABLE adds locking or validation overhead and cannot speed up a slow join plan. Exam caveat: keep the normalized OLTP tables as the system of record and treat the denormalized table as a derived copy, because transactional writes still benefit from normalization. Operational check: compare refresh time and logical reads for the dashboard before and after, and add a reconciliation query that verifies the reporting table matches the source totals after each load.