Posted by dmarkle on 01/18/07 13:48

Your answer is: "it depends."

Instead of re-creating the stored proc, you can use the WITH RECOMPILE
option, which will probably do what you want:

CREATE PROC dbo.foo WITH RECOMPILE AS
select 'hello world';

http://msdn2.microsoft.com/en-us/library/aa258259(SQL.80).aspx


-Dave

jim_geissman@countrywide.com wrote:
> I have a table of 25-30 million properties, from which are retrieved
> ~150 centered on a point, based on the parameters -- coordinates,
> property type and date of transaction. There's an SP (also implemented
> as a function returning a table) to return the desired records.
>
> This look-up takes the most time in the C# program that calls it, and
> should be optimized. It was suggested that instead of having an SP on
> the server, each time the program should create an SP that is the same,
> however without any parameters -- with the values hard-coded. Then
> execute it, and drop it. This way, the execution plan will be
> customized for the specific parameters. I tried it and it turns out
> the suggested method is noticeably faster, even compared to recompiling
> an SP every time. I was wondering if there is a way to get equivalent
> performance out of an SP or UDF that has parameters, or is this
> approach necessarily going to be less optimized than hard-coded
> non-parameters.
>
> Thanks,
> Jim

[Back to original message]