Grant Fritchey takes a look at the performance of sp_executesql:
The results are fun.
Execution Type Average Duration sp_executesql AVG: 57946.03187251 Ad Hoc AVG: 14788.8924302789 What’s going on? Is the conclusion that, in fact, ad hoc queries are faster than sp_executesql?
Absolutely not.
I cheated.
Using sp_executesql is about protecting yourself when executing dynamic SQL, not about performance gains.