A Redshift data warehouse has different user teams that need to query the same table with very different query types. These user teams are experiencing poor performance. Which action improves performance for the user teams in this situation?
A.
Create custom table views.
B.
Add interleaved sort keys per team.
C.
Maintain team-specific copies of the table.
D.
Add support for workload management queue hopping.
D is my answer. Different user have different query types, could be long or short. WLM should be use to allocate different type of queue to run the jobs, either automatically or manually. Hopping queue is one of the manual option to tune the queue.
C in my opinion.
Not A - Non-materialized views won't magically make your query faster.
Not B - "per team" doesn't make sense, there could be only one interleaved sort key that all use. Also, this is only feasible in case there are few teams as adding many columns to an interleaved sort key degrades performance.
Not D - query hopping only affects execution order of querys, not their performance.
C - admittedly, this is brute force and I wouldn't recommend it, but you can't argue that it actually improves performance for the user teams.
answer is D. It cannot be B because, adding interleaved sort key per team is not scalable with operational overhead. A new team comes with a new query type, interleaved sort key cannot be altered once created. https://docs.aws.amazon.com/redshift/latest/dg/r_ALTER_TABLE.html
WLM does not have such an issue.
Answer : B
Not A – Its impractical to create as many view as the query types.
Not C – Its impractical to create as many copies of the table as the user teams
Not D – Unless the table is tuned for performance, hoping queues will become more frequent resulting in degraded performance. Associating one queue per user group also won’t guarantee improved performance if the table is filtered on a column that it is cannot be sorted on
Answer is B- An interleaved sort gives equal weight to each column, or subset of columns, in the sort key. If multiple queries use different columns for filters, then you can often improve performance for those queries by using an interleaved sort style. When a query uses restrictive predicates on secondary sort columns, interleaved sorting significantly improves query performance as compared to compound sorting.
D is more correct.
B is correct if different queries have their WHERE clause on different columns that are being defined on global index. However, the problem statement did not mention that.
It is D. see https://docs.aws.amazon.com/redshift/latest/dg/cm-c-implementing-workload-management.html. The question is trying to reinforce 'different users'
Correct, it has to be D.
The question states, that "user teams are experiencing poor performance.
Which action improves performance for the user teams in this situation? ".
WLM is the one that can be used to route queries based on user groups (user teams) and alloted priorities.
On the first side, it seems B. Add interleaved sort keys per team. is correct. But how it can be "per team", this made it a wrong answer.
The nearest alternative option is D. Add support for workload management queue hopping.
it can share resources between the parallel query. And in that way, it improves performance.
This is confusing a lot "the same table with very different query types". I think it means a lot of types of queries (short living time,long living, etc). It doesn't mention about key inside tables and there're no any ways to fix it with keys (all teams share the same data)
I changed my opinion. B is the right answer.
"But how it can be "per team", this made it a wrong answer."
Easy. interleaved sort keys could include different columns for different team. As example you can create key with (n1,n2,n3) columns where different team can use their filter conditions.
A voting comment increases the vote count for the chosen answer by one.
Upvoting a comment with a selected answer will also increase the vote count towards that answer by one.
So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.
gonda
Highly Voted 3 years, 7 months agoguruguru
Most Recent 3 years, 5 months agoCorram
3 years, 5 months agosrirampc
3 years, 5 months agosrirampc
3 years, 5 months agoBulti
3 years, 5 months agojxj
3 years, 6 months agojiedee
3 years, 6 months agoG3
3 years, 6 months agoG3
3 years, 5 months agosan2020
3 years, 6 months agokalpanareddy
3 years, 6 months agoME2000
3 years, 6 months agopracticioner
3 years, 6 months agopracticioner
3 years, 6 months agomichelleY
3 years, 6 months agoBigEv
3 years, 6 months agoM2
3 years, 6 months agoexams
3 years, 7 months agomattyb123
3 years, 7 months ago