DEA-C01 Question 142
Select 3You are working as a data engineer for a company that uses Amazon Redshift as their data warehouse. One of your SQL queries, which aggregates sales data by region and calculates monthly totals, is running slower than expected. Upon investigation, you notice that the query is performing a full table scan on a large sales table. How can you optimize the query to improve performance?
- A
Add appropriate SORT and DIST keys to the sales table.
- B
Use WHERE conditions to filter unnecessary rows before performing aggregations.
- C
Use the COPY command to reload the sales data into Amazon Redshift.
- D
Use an interleaved sort key instead of a compound sort key to optimize for multiple query patterns.
- E
Use the ANALYZE command to update table statistics before running the query.
Show answer and explanation
Correct answers: A, B, E
Explanation
To optimize a slow SQL query in Amazon Redshift, it is important to ensure that the table is properly configured with SORT and DIST keys to avoid unnecessary full table scans. Additionally, using WHERE conditions to filter rows early in the query can significantly reduce the amount of data processed. Updating table statistics with the ANALYZE command ensures that the query optimizer has the most up-to-date information about the table, leading to better query execution plans. Each of these steps directly addresses the performance issue of the query performing a full table scan.
- A. Correct.
Adding SORT and DIST keys ensures that the data is distributed and sorted efficiently, which reduces the need for full table scans during query execution.
- B. Correct.
Filtering unnecessary rows early in the query using WHERE conditions reduces the amount of data scanned and processed, improving query performance.
- C. Incorrect.
The COPY command is used to load data into Amazon Redshift and does not directly optimize query performance.
- D. Incorrect.
Interleaved sort keys are useful for optimizing queries with multiple query patterns, but they are not necessarily the best choice for this specific scenario, where a single query is being optimized.
- E. Correct.
Running the ANALYZE command updates the table statistics, helping the query optimizer make better decisions about how to execute the query efficiently.