L.10 Flashcards
Optimizing For Query Performance
What are the main topics covered in Query Performance Optimization?
Views (Chapter 7), Indices (Chapter 17 - practical use), Query Optimization (Chapter 19)
Why do different queries have different execution times?
Query performance depends on:
* The number of rows in involved tables
* The type of operations used (projection, selection, joins)
* The order of execution (Query Plan)
What are some ways to improve slow query performance?
Improve slow query performance by:
* Use caches
* Use views to store frequent query results
* Add indices for faster selection
* Rewrite queries for faster execution order
What is a materialized view, and how is it different from a regular view?
A regular view is a saved query that gets executed every time it’s called.
A materialized view is stored on disk and improves performance but requires updates.
What are the different strategies for updating materialized views?
Strategies for updating materialized views include:
* Immediate Update: Updates as soon as base tables change (slower inserts)
* Lazy Update: Updates when queried (slower query execution)
* Periodic/On-Demand Update: Updates on a schedule (risk of outdated data)
What is a full table scan, and why is it slow?
A full table scan occurs when a query searches every row in a table (O(n) complexity), making it slow for large datasets.
How do indices improve query performance?
Indices provide fast lookups by reducing search complexity from O(n) to O(1) or O(log n), depending on the index type.
How do you create an index in SQL?
CREATE INDEX index_name ON table_name(column_name);
What is a unique index, and how do you create one?
A unique index ensures no duplicate values in a column. CREATE UNIQUE INDEX index_name ON table_name(column_name);
What are the trade-offs of using an index?
Trade-offs of using an index include:
* ✅ Faster queries (avoids full table scans)
* ❌ More disk space usage
* ❌ Slower insert/update/delete operations
What are the two types of query rewriting (optimization)?
The two types of query rewriting are:
* Heuristic Optimization: Uses general transformation rules to improve efficiency.
* Cost-Based Optimization: Estimates query cost and chooses the best execution plan.
What is an example of query rewriting for optimization?
Instead of:
SELECT * FROM city JOIN country
ON city.country = country.code
WHERE country.area > 100000;
Rewrite to:
SELECT * FROM (SELECT * FROM country WHERE area > 100000) c
JOIN city ON city.country = c.code;
This reduces the number of rows before joining, making it faster.
What does EXPLAIN do in SQL?
EXPLAIN shows the query execution plan without running the actual query.
What is the difference between EXPLAIN and EXPLAIN ANALYZE?
EXPLAIN: Shows the query plan without execution.
EXPLAIN ANALYZE: Runs the query and displays actual performance statistics.
How does cost-based optimization work in databases?
-Cost-based optimization generates multiple equivalent query plans
-Assigns a cost value to each plan
-Executes the plan with the lowest cost.
What are the main cost components of a query?
Main cost components of a query include:
* Access cost (disk reads/writes)
* Computation cost (CPU operations)
* Memory usage
* Communication cost (if querying remote data)
What is the problem with excessive joins in a query?
Joins across many tables can be slow, especially in normalized databases.
Queries requiring many joins often have high computational costs.
What are some solutions to improve performance in queries with excessive joins?
Solutions include:
* Caching results
* Keeping redundant data (denormalization)
* Using (materialized) views
What are the limitations of views?
Limitations of views include:
* Cannot be updated in most cases
* Cannot use JOIN, DISTINCT, or aggregation
* Does not always improve performance (unless materialized)
What are the two main view implementation techniques?
The two main view implementation techniques are:
* Query Modification: Saves a query but runs it every time (no real performance gain).
* Materialized View: Saves results to disk for faster queries.
Why are materialized views not considered redundant data?
Because the DBMS manages them, ensuring updates when necessary.
What is the computational complexity of a full table scan?
O(n), where n is the number of rows in the table.
What type of index reduces query complexity to O(1)?
A hash-based index.
What is the most common type of database index, and what is its complexity?
A B-tree index, which supports range queries with O(log n) complexity.
