Friday, March 9, 2012

Function is 10 times slower than SP

Hi all,

In order to return a table for a specific input parameter, I am using Function, but the performance is just awful! After I have tried same code as SP, the whole thing is running under 1 sec (like 0.5 sec), while the function is about 10 times slow (4-6 sec). I know in SQL 2000 function is slower than SP, but that cannot be as bad as 10 times slower.

Now, in order to use that table from SP, I have to create a temp table, then insert result into that temp table, before I can direct use any "select" sentence. Any explanation here? Or how to "select" from a SP directly?

Thanks,

Ning

It is very difficult for us to attempt to help you with being able to see the code.

You may wish to consider posting the procedure code and maybe someone here will help create an efficient TVF (table valued function.)

|||

As you said you have to insert the sp output to temp table to use in select statement ... you can not use sp in select statement... you are rightly mentioned that function used to be more or less slower than sp... also check whether all the parameters used in sp are used in function also...

Madhu

No comments:

Post a Comment