CompTIA DataSys+ (DS0-001)Database Management and MaintenanceHard
A database developer is writing SQL queries for a new reporting module. They need to retrieve the total sales for each product category over the last year. The database contains millions of sales records. The current query uses a `GROUP BY` clause on `product_category` and `SUM(sales_amount)`. To optimize this query for faster execution, which of the following techniques should the developer consider?
- ADisabling query caching for the reporting database.
- BCreating a materialized view for aggregated sales data.
- CApplying a `HAVING` clause before the `WHERE` clause.
- DUsing `SELECT *` instead of specific columns.
Show answer & explanationAnswer & explanation
Correct answer: B. Creating a materialized view for aggregated sales data.
A materialized view pre-calculates and stores the results of a complex query (like aggregations). For frequently run reporting queries on large datasets, querying the materialized view is significantly faster than re-executing the aggregation every time.
Why the other options are wrong
- A. Disabling query caching would likely degrade performance, as caching stores frequently accessed query results to avoid re-execution, which is beneficial for reporting.
- C. A `HAVING` clause is always applied after `GROUP BY` and `WHERE` clauses. Changing its order would either be syntactically incorrect or not improve performance, as `WHERE` filters rows before grouping.
- D. Using `SELECT *` is generally inefficient as it retrieves all columns, potentially more than needed, increasing I/O and network traffic, thus hindering optimization.
Materialized View
A database object that contains the results of a query, physically stored in the database. It is pre-computed and refreshed periodically, providing fast access to aggregated or joined data.
- Optimizes complex queries, especially aggregations and joins.
- Trades storage space and refresh time for faster query execution.
- Useful for data warehousing and reporting scenarios.
Memory trick: Materialized views are like pre-cooked meals for queries.