70-465 · Question #354
70-465 Question #354: Real Exam Question with Answer & Explanation
The correct answer is A: A common table expression.. To improve the performance of a stored procedure, a Common Table Expression (CTE) is preferred over row-by-row or temporary storage approaches. CTEs allow the query optimizer to efficiently handle complex queries in a single, readable, and set-based operation.
Question
The performance of the SPUpdate2 stored procedure needs to be improved. Your solution must meet the design requirements. What should your solution include?
Options
- AA common table expression.
- BA derived table.
- CA Cursor.
- DA table variable.
Explanation
To improve the performance of a stored procedure, a Common Table Expression (CTE) is preferred over row-by-row or temporary storage approaches. CTEs allow the query optimizer to efficiently handle complex queries in a single, readable, and set-based operation.
Common mistakes.
- B. A derived table is a valid alternative to a CTE but is less readable and cannot be referenced multiple times within the same query, making it less optimal for complex performance improvement scenarios.
- C. A cursor processes data row-by-row rather than as a set, which significantly degrades performance compared to set-based operations and is generally the worst choice for improving stored procedure performance.
- D. A table variable stores intermediate results in tempdb and incurs overhead from row-by-row insertion and limited statistics support, which can degrade performance rather than improve it for complex query scenarios.
Concept tested. Optimizing stored procedures using Common Table Expressions
Reference. https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql
Topics
Community Discussion
No community discussion yet for this question.