ARA-C01 Performance Optimization Practice Question

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

• ✗

  Rewrite the query to use a nested loop join instead of a hash join.

  Why it's wrong here

  Nested loop joins are generally less efficient for large datasets and would likely perform worse. Hash joins are preferred for equi-joins between large and small tables. Rewriting to a nested loop join would not solve the memory spilling and would probably increase execution time.

• ✗

  Enable the Query Acceleration Service on the warehouse.

  Why it's wrong here

  Query Acceleration Service offloads portions of scan-intensive queries to shared resources, but it does not provide additional memory for join operations. Spilling is caused by insufficient memory for the hash join, so this service would not eliminate the spilling. It is not the right tool for this scenario.

• ✓

  Increase the warehouse size to provide more memory per node.

  Why this is correct

  Hash join spilling occurs when the build side (often the smaller table) does not fit in memory. Increasing the warehouse size adds more memory per node, allowing the build side to fit in memory and eliminating spilling. This directly addresses the issue shown in the Query Profile.

• ✗

  Add a clustering key on the join column of the large fact table.

  Why it's wrong here

  Clustering improves partition pruning and can reduce I/O for scans, but it does not directly address memory spilling in the join operator. Spilling is a memory issue, not a data organization issue. While clustering might reduce the amount of data processed, it is not the most direct fix for spilling.