CDS Performance Optimization 3 — Questions and Answers
Question 1: What is query plan analysis primarily used for in performance optimization?
- Validating data types across schema versions
- Identifying inefficient execution paths chosen by the query optimizer (Correct answer)
- Auditing user access to sensitive tables
- Measuring network latency between application tiers
Correct answer: Identifying inefficient execution paths chosen by the query optimizer
Query plan analysis reveals the execution steps the optimizer selects, exposing full scans, missing indexes, and costly joins that slow performance.
Question 2: Which partitioning strategy is best suited for time-series data that is mostly queried by date range?
- Hash partitioning on the primary key
- Range partitioning on the timestamp column (Correct answer)
- List partitioning on a status column
- Round-robin partitioning across nodes
Correct answer: Range partitioning on the timestamp column
Range partitioning on a timestamp aligns physical storage with the most common query pattern, enabling efficient partition pruning.
Question 3: What does 'partition pruning' mean in the context of query optimization?
- Deleting old partitions to free disk space
- The optimizer skipping irrelevant partitions during query execution (Correct answer)
- Splitting a partition into smaller sub-partitions
- Merging underutilized partitions to reduce overhead
Correct answer: The optimizer skipping irrelevant partitions during query execution
Partition pruning is the optimizer's ability to exclude partitions that cannot contain relevant rows, reducing the data scanned.
Question 4: A report that joins a 500M-row fact table with a 100-row dimension table is running slowly. Which optimization is most impactful?
- Replacing the inner join with a subquery
- Using a broadcast join to replicate the small dimension to all nodes (Correct answer)
- Adding a composite index on the fact table's foreign key and date column
- Compressing the fact table using columnar encoding
Correct answer: Using a broadcast join to replicate the small dimension to all nodes
Broadcasting a small dimension table to all compute nodes eliminates expensive shuffle operations when joining with a large fact table.
Question 5: What is the main trade-off when adding indexes to improve read performance on a table?
- Indexes increase storage costs and slow down write operations (Correct answer)
- Indexes reduce query parallelism and increase CPU usage
- Indexes prevent the optimizer from using statistics
- Indexes cause table locks during all SELECT operations
Correct answer: Indexes increase storage costs and slow down write operations
Each additional index consumes storage space and must be updated on every INSERT, UPDATE, or DELETE, increasing write overhead.
Question 6: Which approach best reduces the performance impact of running large analytical queries on an OLTP production database?
- Scheduling analytical queries during off-peak hours only
- Routing analytical queries to a read replica or separate data warehouse (Correct answer)
- Adding more RAM to the production OLTP server
- Converting all OLTP tables to columnar format
Correct answer: Routing analytical queries to a read replica or separate data warehouse
Offloading analytical queries to a read replica or dedicated warehouse isolates analytical workloads from transactional operations, preserving OLTP performance.
Question 7: In the context of data performance, what does 'cardinality' refer to and why does it matter for indexing?
- The number of tables in a schema; higher means more joins needed
- The number of unique values in a column; higher cardinality makes indexes more selective (Correct answer)
- The number of rows in a table; larger tables need more indexes
- The depth of a query execution tree; deeper trees indicate poor performance
Correct answer: The number of unique values in a column; higher cardinality makes indexes more selective
High-cardinality columns have many unique values, making indexes on them highly selective and efficient at narrowing query result sets.
CDS Performance Optimization 3
What is query plan analysis primarily used for in performance optimization?