Posted by jim_geissman on 01/17/07 15:23

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]