Posted by othellomy on 01/18/07 03:51

Hi Jim,
Okay.
Does the SP takes a long time to run as stand alone (executing it on
the server without calling from C#)?
Is it too much of a trouble to post the whole code? Can you time the SP
and what is it?
As a quick solution maybe you can modify the SP to save the result into
a table and return some number instead (as success). Then the C# just
access that table and once done drop it at the end. You can have a
recompile option when creating the stored procedure but might worth as
well if you can increase performance by minor fixes.

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]