CompTIA DataSys+ (DS0-001)Database Management and MaintenanceMedium
A database administrator is tasked with improving the performance of a reporting query that frequently calculates aggregate values (e.g., SUM, COUNT, AVG) over a large dataset. This query is run multiple times a day, and the underlying data changes only once every 24 hours. Which database object would be most beneficial to pre-compute and store the results of this query?
- AMaterialized View
- BView
- CStored Procedure
- DIndex
Show answer & explanationAnswer & explanation
Correct answer: A. Materialized View
A materialized view pre-computes and stores the results of a query, which is ideal for frequently executed aggregate queries on data that changes infrequently. This significantly reduces query execution time by avoiding re-calculation on each run.
Why the other options are wrong
- B. A view is a virtual table that executes its underlying query every time it's accessed, offering no performance improvement for complex aggregations.
- C. A stored procedure encapsulates SQL logic but still executes the query each time it's called, not pre-computing results.
- D. An index speeds up data retrieval but does not pre-compute and store the results of an entire aggregate query.
Materialized View
A database object that contains the results of a query, physically stored in the database and periodically refreshed.
- Improves performance for complex queries, especially aggregations.
- Data is pre-computed and stored.
- Requires refresh mechanisms to keep data up-to-date.
Memory trick: Materialized views store the 'material' for quick reports.