A large table with 200 columns contains two years of historical data. When queried, the table is filtered on a single day. Below is the Query Profile:
Using a size 2XL virtual warehouse, this query took over an hour to complete.
What will improve the query performance the MOST?
To improve the query performance the most, adding a date column as a cluster key on the table would be the best option. This approach helps to reduce the number of partitions scanned when filtering by specific dates because clustering organizes data based on the values in the key column, leading to more efficient querying. Increasing the size of the virtual warehouse or the number of clusters might provide some improvement in processing power but would not address the underlying issue of scanning too many partitions, as seen in the high number of partitions scanned in the Query Profile.
although there are bytes spilling, but compare with the size, it is not much, so option B may improve, but not much. considering filter is on a single field, option D make sense.
D makes sense
D Clustering key will not scan the full table which is what is required here.
are these 71 questions enough to prepare for advanced DE exam ?