CDS Performance Optimization 2 — Questions and Answers
Question 1: Which indexing strategy is most effective for queries that frequently filter on multiple columns together?
- Single-column index on the most selective column
- Composite index ordered by query filter columns (Correct answer)
- Full-table scan with parallel processing
- Bitmap index on all columns
Correct answer: Composite index ordered by query filter columns
Composite indexes ordered to match query filter columns allow the database to satisfy multi-column WHERE clauses without additional lookups.
Question 2: A data warehouse query performs slowly due to data skew. What technique best addresses this?
- Increasing memory allocation to the query executor
- Repartitioning data using a more evenly distributed key (Correct answer)
- Adding more indexes to the skewed column
- Reducing the number of joins in the query
Correct answer: Repartitioning data using a more evenly distributed key
Data skew causes uneven partition load; repartitioning with a better distribution key balances workload across nodes.
Question 3: What is the primary purpose of materialized views in performance optimization?
- To enforce referential integrity constraints
- To store precomputed query results for faster retrieval (Correct answer)
- To compress large tables automatically
- To replace primary keys with surrogate keys
Correct answer: To store precomputed query results for faster retrieval
Materialized views persist precomputed results, eliminating the need to re-execute expensive aggregations on every query.
Question 4: Which metric best indicates that a database system is experiencing I/O bottlenecks?
- High CPU utilization with low wait times
- Elevated disk read/write latency and queue depth (Correct answer)
- Increased network packet loss
- High memory page faults with fast resolution
Correct answer: Elevated disk read/write latency and queue depth
High disk latency and long I/O queue depths are the primary signals of an I/O bottleneck limiting query performance.
Question 5: In columnar storage, why are queries that aggregate a single column faster than in row-based storage?
- Columnar storage applies automatic indexing on every column
- Only the relevant column's data is read from disk, reducing I/O (Correct answer)
- Columnar storage uses in-memory caching exclusively
- Row-based storage cannot perform aggregations
Correct answer: Only the relevant column's data is read from disk, reducing I/O
Columnar storage reads only the columns needed by a query, drastically reducing disk I/O compared to reading entire rows.
Question 6: A data steward notices that an ETL pipeline is processing records sequentially and running overtime. What is the best immediate optimization approach?
- Switch from SQL to NoSQL for the target system
- Implement parallel processing or batch partitioning (Correct answer)
- Reduce the frequency of ETL runs
- Archive older records to reduce dataset size
Correct answer: Implement parallel processing or batch partitioning
Parallel processing or partitioned batching distributes ETL workload across multiple threads or nodes, reducing total run time.
Question 7: Which caching strategy is most appropriate for data that changes infrequently but is queried very often?
- Write-through cache with immediate invalidation
- Read-through cache with a long TTL (Correct answer)
- Write-behind cache with asynchronous updates
- No-cache strategy with direct DB reads
Correct answer: Read-through cache with a long TTL
A read-through cache with a long TTL is ideal for slowly changing, frequently read data because it minimizes cache misses without frequent refreshes.
CDS Performance Optimization 2
Which indexing strategy is most effective for queries that frequently filter on multiple columns together?