February 19, 2009 at 2:59 pm
Seggerman (2/19/2009)
I had this problem at an earlier job and it was a dog - arf! Making the WHERE select into a temp file helped a bit. If you have to go the dymanic SQL route, I am told that sp_executesql is a trifle faster.
Yes, using the (column = parameter or parameter is null) construct can be quite a performance killer. Note, I said can be. If the column is not indexed - the above method will be no faster nor slower than using dynamic SQL.
Where it really starts to bog down is when the execution plan is generated. The execution plan will be generated for a particular set of parameters and will most likely not be very good for a different set of parameters.
If this is going to support a report, that is accessed by the users from a report server (for example), and is not accessed a lot (i.e. several times a day as opposed to several times a minute) - then you can set the option to recompile the procedure and a new plan would be generated for every execution.
On the other hand, if the procedure is going to be called a lot - then using dynamic SQL would probably be best so you don't have the extra overhead of recompiling.
And yes, sp_executesql would be a better option for a lot of reasons.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing post 16 (of 15 total)
You must be logged in to reply to this topic. Login to reply