DEA-C01 Question 144
Select 3A data engineering team is using Amazon Redshift to analyze a large dataset stored in a table with billions of rows. They notice that their SQL query performance is slow. Which of the following actions can improve the query performance?
- A
Use distribution keys to evenly distribute data across nodes.
- B
Create appropriate sort keys to optimize the query's filtering and sorting operations.
- C
Run the query without an ORDER BY clause to speed up execution.
- D
Enable concurrency scaling to handle additional query load.
- E
Analyze and vacuum the table to reclaim storage and update statistics.
Show answer and explanation
Correct answers: A, B, E
Explanation
Improving query performance in Amazon Redshift often involves optimizing the schema and query execution plan. Using distribution keys ensures even data distribution, while sort keys help optimize query filtering and sorting. Additionally, analyzing and vacuuming tables updates statistics and reclaims storage, improving overall performance. While concurrency scaling and avoiding ORDER BY clauses may help in specific scenarios, they are not fundamental optimizations for query performance.
- A. Correct.
Using distribution keys to evenly distribute data across nodes ensures that data is spread efficiently, reducing the risk of data skew and improving query performance.
- B. Correct.
Creating sort keys can optimize queries that frequently filter or sort data, by allowing Redshift to minimize the amount of data scanned.
- C. Incorrect.
Running the query without an ORDER BY clause may speed up execution in some cases, but it is not a reliable optimization strategy and can produce inconsistent results.
- D. Incorrect.
Concurrency scaling is useful for handling additional query load and providing faster query execution in high-concurrency scenarios, but it does not directly improve the performance of poorly optimized queries.
- E. Correct.
Analyzing and vacuuming the table is essential for reclaiming storage, removing deleted rows, and updating statistical metadata, which can significantly improve query performance.