Very Slow Stored Proc

Hi Everybody
I have a stored proc that uses a cursor .
Each time ,the cursor forward(fetch next into),i do some caluctations by using UDfs before inserting results in a local table.
And the stored proc needs 3 minutes to finish wow !!
I know that calling each time the Udfs harms performance,but i dont have a choice as the calulations are dynamic.
X=SUM(Results(udf))
Any help !
Thanks.

If you say the calculations are dynamic, then I expect you are evaluating a number of columns from the query - something like:
(if column_x = 'a' then column_a else if column_x = 'b' then column_b) multiply by 12 ...
Well, as luck will have it, you can use CASE to return the result of an expression for a given set of criteria. So you could use:
SELECT SUM((CASE column_x WHEN 'a' THEN column_a WHEN 'b' THEN column_b END) * 12) FROM MyTable

Thanks Adriaan .
Actually my stored has two datetimes parameters:StartDate en EndDate
And those two parameters are very important for my UDFs
its UDFs that calculate consumption between two dates.And sometimes i have to calculate the the conusumption for each month in the stored proc to have the evolution of consumption between the two dates.
for example if the Startdate=2008/01/01 AND EndDate=2008/12/31 i have to calculate to call the udfs 12 times(each month).
And thats without countin the cursor !!
--------in this format----------------------------------------------------
First Cursor
Cursor of Months
UDFs
End Cursor of Months
End First Cursor

Sounds similar to having a running sum, only now you need to print the difference. Plenty of reporting tools will give you options to include that type of detail on top of what SQL Server is returning. Not sure you need to bother SQL Server with that - as you see, it can kill performance.
If you need to be able to reproduce the same data more often, why not store the results in a reporting table?

I can store the results in table by creating a job that do all that calculations every nite or something like that.
The pb is that UDFs reads data in a table that can be updated(new inserts,...)
And users want to be able see consumption evolution at every time .
That why the need of dynamic calculations.

Some CodeECLARE BusinessGeoCURSOR CURSOR FOR SELECT BusinessGeoId FROM @BusinessGeoTable
OPEN BusinessGeoCURSOR FETCH NEXT FROM BusinessGeoCURSOR INTO @BusinessGeoId0
WHILE(@@FETCH_STATUS=0)
BEGINDELETE FROM @BusinessGeoChildrenTable--Delete Each time
DELETE FROM @ServiceTableCost--Delete Each time
SET @BusinessTreeGeo=NULLSET @PropertyCost=0
-------------------------------------------------------------------------INSERT INTO @BusinessGeoChildrenTable
SELECT IdBusinessGeo FROM dbo.GetBusinessGeoChildren_Function(@BusinessGeoId0) --First UDFSET @BusinessTreeGeo=(SELECT site.Name+'/'+businessTree.Name FROM OrgTreeAssociation orgTreeAssociation
INNER JOIN Site site on site.Id=orgTreeAssociation.Site_IdINNER JOIN BusinessTree businessTree ON businessTree.Id=orgTreeAssociation.BusinessTree_Id
WHERE orgTreeAssociation.Id=@BusinessGeoId0)
-----------------------------------------------------------------------------------------------------------
----------------------Costs----------------------------------------------------------INSERT INTO @ServiceTableCost SELECT serviceManagement.id
FROM ServiceManagement serviceManagementINNER JOIN @BusinessGeoChildrenTable businessGeoChildrenTable ON businessGeoChildrenTable.BusinessGeoId=serviceManagement.OrgTreeAssociations_Id
INNER JOIN dbo.ServiceDesignTemplateAssociation serviceDesignTemplateAssociation ON serviceDesignTemplateAssociation.ServiceManagement_Id=serviceManagement.idINNER JOIN ServiceDesignTemplate serviceDesignTemplate ON serviceDesignTemplateAssociation.ServiceDesignTemplate_Id=serviceDesignTemplate.IdINNER JOIN @TelecomSubCatTable telecomSubCatTable ON telecomSubCatTable.TelecomSuCatbId=serviceDesignTemplate.TelecomSubCategory_Id
---------------------Dates Cursor--------------------------------------------------------DECLARE DatesCURSOR CURSOR FOR SELECT MonthStart,MonthEnd,"Month","Year" FROM @datesTable
OPEN DatesCURSOR FETCH NEXT FROM DatesCURSOR INTO @MonthStart,@MonthEnd,@Month,@Year
WHILE(@@FETCH_STATUS=0)
BEGINSET @PropertyCost=0
SET @PropertyCost=ISNULL((SELECT SUM(dbo.GetPropertyCostInService(ServiceTableCost.ServiceId,PropertiesTable.Id,@MonthStart,@MonthEnd,@CurrencyId))--UDF
FROM @ServiceTableCost ServiceTableCost,@PropertiesTable PropertiesTable),0)
-------------------------------------------------------------------------------------------------INSERT INTO @BusinessGeoPropertyCost
SELECT @BusinessGeoId0,@BusinessTreeGeo,@PropertyCost,@MainCurrecny,@MonthStart,@MonthEnd,@Month,@Year
------------------------------------------------------------------------------------------FETCH NEXT FROM DatesCURSOR INTO @MonthStart,@MonthEnd,@Month,@Year
END CLOSE DatesCURSOR
DEALLOCATE DatesCURSORFETCH NEXT FROM BusinessGeoCURSOR INTO @BusinessGeoId0
ENDCLOSE BusinessGeoCURSOR
DEALLOCATE BusinessGeoCURSOR
--------------------------------------------------------
SELECT * FROM @BusinessGeoPropertyCost --Result of the stored Proc

You're leaving out the core processing - the UDFs. If those UDFs are nothing more than lookups, then you can simply rewrite those calls with JOIN syntax in the INSERT query, and do things set-based (skipping all cursors).
If you're staying with the cursors, then check the table variables that you're joining on: if the join column(s) is (are) the PK then declare it (them) as the PK. If you are joining on non-key columns, consider using temporary tables with indexes, instead of table variables.

Hi Adriaan
in the joint query , i have tree permanent Tables :ServiceManagement(call it A) AND serviceDesignTemplate(B) AND serviceDesignTemplateAssociation(C).
The table A has a PK column named id and a non PK named OrgTreeAssociations_Id.
B has a PK column named id and a non PK column named TelecomSubCategory_Id.C is an association of the two.
if i add a non clustered indexs in A.OrgTreeAssociations_Id and B.TelecomSubCategory_Id ,is this will improve performance.or may be add a non clustered index in C(A.id,B.id)?
SELECT
serviceManagement.id FROM ServiceManagement serviceManagement
INNER JOIN @BusinessGeoChildrenTable businessGeoChildrenTable ON businessGeoChildrenTable.BusinessGeoId=serviceManagement.OrgTreeAssociations_IdINNER JOIN dbo.ServiceDesignTemplateAssociation serviceDesignTemplateAssociation ON serviceDesignTemplateAssociation.ServiceManagement_Id=serviceManagement.id
INNER JOIN ServiceDesignTemplate serviceDesignTemplate ON serviceDesignTemplateAssociation.ServiceDesignTemplate_Id=serviceDesignTemplate.Id
INNER JOIN @TelecomSubCatTable telecomSubCatTable ON telecomSubCatTable.TelecomSuCatbId=serviceDesignTemplate.TelecomSubCategory_Id
----------------------------------------------------------------------------------------------------------------------- for the option of using temporary tables let call them #BusinessGeoChildrenTable ,
#ServiceTableCost ,#telecomSubCatTable,
how the creation will be procedeed?creatin them ->creating indexs->using them for udfs-> drop them?
and what about the risk of deadlock(or waits) if the proc called by another user.can i chose them as local temporary tables?
Thanks,

Create temp tables, insert data, add index(es).
You still haven't said anything about what the UDFs do - if it's a lookup or an aggregate value based on a couple of filter values, then try to do the same with JOINs, perhaps using a derived table.
Temp tables are local to the procedure if the name starts with a single #. Global is when the name starts with ##.
To avoid locking the source table(s), do not use the
SELECT columns
INTO #temptable
FROM source_table
... syntax, but use this:
CREATE TABLE #temptable (column_definitions)

INSERT INTO #temptable
SELECT columns
FROM source_table