SQL Server–Always use sp_executesql over exec
Today I was looking through some legacy code and found a loop that dynamically created a SQL string and executed using exec().
A quick check of the execution plans showed that SQL Server was creating a new plan for each loop.
We changed it to use sp_executesql instead. Because this uses parameterised queries, it can re-use the execution plan each time.
Final result – our routine ran 9 times faster and as a bonus it used about a 1/3 less CPU. Happy days!