SOL-C01 · Question #276
SOL-C01 Question #276: Real Exam Question with Answer & Explanation
The correct answer is B: The 'Join' operator (if the IN clause is optimized into a join).. The ` IN' subquery can often be rewritten by Snowflake as a 'JOIN'. Therefore, a poorly performing 'JOIN' operator (especially a 'BROADCAST JOIN' with a very large table on one side) is the most likely culprit for performance issues in this scenario. Checking the 'Join' operator
Question
Using Snowsight, you observe a query with the following SQL: FROM LARGE_TABLE WHERE ID IN (SELECT ID FROM SMALL_TABLE WHERE CONDITION - SELECT The query is performing poorly. When examining the Query Profile in Snowsight, what specific operator should you MOST closely inspect to identify potential bottlenecks?
Options
- AThe 'Tablescan' operator on
- BThe 'Join' operator (if the IN clause is optimized into a join).
- CThe 'Filter' operator on after the 'Tablescan'.
- DThe 'Result' operator, indicating final result set generation.
- EThe 'Aggregate' operator (if present in the inner query).
Explanation
The ` IN' subquery can often be rewritten by Snowflake as a 'JOIN'. Therefore, a poorly performing 'JOIN' operator (especially a 'BROADCAST JOIN' with a very large table on one side) is the most likely culprit for performance issues in this scenario. Checking the 'Join' operator in the Query Profile will reveal if this optimization occurred and whether it is efficient. Table scans, filters and results will give insights but Join is the best option.
Topics
Community Discussion
No community discussion yet for this question.