Blackball Software is a software consultancy and design company based in Auckland, New Zealand.

We love what we do and we're very good at it. We don't muck around, bamboozling or intimidating with nerd speak.

Nup, we just listen to what you need then we build it better than you ever imagined.

SQL Server–Always use sp_executesql over exec

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!

Meet the star of my new book

Meet the star of my new book

Is your SQL Azure sys.event_log empty?

Is your SQL Azure sys.event_log empty?