sp_executesql Performance

Grant Fritchey takes a look at the performance of sp_executesql:

The results are fun.

Execution TypeAverage Duration
sp_executesqlAVG: 57946.03187251
Ad HocAVG: 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.

Related Posts

Fill Factor And The Performance Tradeoff

Tara Kizer explains the performance tradeoff when setting fill factor for an index: There are workloads where frequent page splits are a problem. I thought I had a system like this many years ago, so I tested various fill factor settings for the culprit table’s clustered index. While insert performance improved by lowering the fill […]

Read More

Finding The Closest Numeric Match

Itzik Ben-Gan has a T-SQL puzzle for us: As you can see, both T1 and T2 have a numeric column (INT type in this example) called val. The challenge is to match to each row from T1 the row from T2 where the absolute difference between T2.val and T1.val is the lowest. In case of […]

Read More

Categories

November 2016
MTWTFSS
« Oct Dec »
 123456
78910111213
14151617181920
21222324252627
282930