CDS Performance Optimization 3

Question 1

What is query plan analysis primarily used for in performance optimization?

Accepted Answer

Identifying inefficient execution paths chosen by the query optimizer

Answer

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?

Accepted Answer

Range partitioning on the timestamp column

Answer

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?

Accepted Answer

The optimizer skipping irrelevant partitions during query execution

Answer

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?

Accepted Answer

Using a broadcast join to replicate the small dimension to all nodes

Answer

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?

Accepted Answer

Indexes increase storage costs and slow down write operations

Answer

Each additional index consumes storage space and must be updated on every INSERT, UPDATE, or DELETE, increasing write overhead.

(CDS) Certified Data Steward Practice Test