Posted by: Stephen Oakman | September 9, 2009

Exec and sp_executesql

I’m working on a project that has a custom data access layer and lots and lots of stored procedures. One of my tasks right now is to look at optimising some of the more troublesome procs. Typically many of these generate dynamic sql.

So I thought (as did others) that one of the main culprits of the performance problems is down to straight execute of the generated sql statement as opposed to using sp_executesql. Using execute means that every single variation of a statement (the ‘parameter’ values themselves) would generate a new execution plan rather than using an existing cached one. Using sp_executesql means that the sql statement itself uses a cached execution plan and then the values themselves are passed in as named parameters.

So I set about modifying the proc to use sp_executesql. This involved building up a list of the parameters and their data types to pass in, as well as passing in the parameters themselves. It wasn’t as easy as I originally imagined but was fun (in a perverse way).

To validate the change I wrote a unit test which made numerous calls to the proc passing in different parameter values. In this way I could get some timings of the proc before I made the switch and then timings for afterwards as well.

But when I ran the tests to gather the timings the sp_executesql tests run SLOWER!! Usually not much slower but sometimes it is. This is against a shared dev database so part of the timings vary depending on what others are doing to the database but even so, over many of the runs I made the original execute version of the proc ran faster as the new sp_executesql version.

I have managed to speed up the proc though – but through the use of an index on two columns – although I still need to test this – I’m just surprised at how my initial assumption (or belief) in sp_executesql over execute has been dashed right now.

I’m pretty sure sp_executesql is still the way to go for various other reasons – but would ultimately like to use NHibernate and get rid of 95% of the existing procs but that’s not really an option right now.

Advertisements

Responses

  1. Great site you’ve got here.. It’s difficult to find quality writing
    like yours these days. I seriously appreciate individuals like you!
    Take care!!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: