SQL Server Performance

Very Slow Stored Proc

Discussion in 'SQL Server 2008 Performance Tuning for DBAs' started by AMOURGH, Feb 16, 2009.

  1. AMOURGH Member

    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.
  2. Adriaan New Member

    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
  3. AMOURGH Member

    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
  4. Adriaan New Member

    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?
  5. AMOURGH Member

    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.
  6. FrankKalis Moderator

    It might make sense to post some code... [:)]
  7. AMOURGH Member

    Some Code:DECLARE 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
  8. Adriaan New Member

    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.
  9. AMOURGH Member

    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,
  10. Adriaan New Member

    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

Share This Page