CompTIA DataSys+ (DS0-001)Database FundamentalsHard
A data scientist needs to repeatedly execute a complex analytical query that involves joining five large tables, performing aggregations, and applying several filtering conditions. The query takes a significant amount of time to run each time. To improve efficiency and reduce the load on the database server, the data scientist wants to store the pre-computed results of this query and refresh them only once a day. Which database object should be used?
- AA stored procedure
- BA standard view
- CA materialized view
- DA temporary table
Show answer & explanationAnswer & explanation
Correct answer: C. A materialized view
A materialized view stores the actual pre-computed results of a query and can be refreshed periodically. This significantly reduces execution time for complex, frequently accessed queries by avoiding re-computation every time, fitting the requirement for daily refresh and performance improvement.
Why the other options are wrong
- A. A stored procedure encapsulates logic but doesn't store the results itself; it would still execute the full query each time it's called unless it explicitly populates a physical table.
- B. A standard view is a virtual table that re-executes its underlying query every time it's accessed, offering no performance benefits for complex queries.
- D. A temporary table stores data for the duration of a session or transaction, which is not suitable for persistent, daily refreshed results.
Materialized View
A database object that stores the pre-computed results of a query as a physical table. It can be refreshed periodically to keep the data current.
- Improves query performance by avoiding repeated computation of complex queries.
- Stores data physically on disk, unlike a standard view.
- Requires periodic refresh (manual or automatic) to reflect changes in base tables.
- Useful for reporting, data warehousing, and decision support systems.
Memory trick: MATERIALIZED VIEWS are like having a PRE-COOKED MEAL for your data!