Function taking long time

  • Hi,

    I have given a function which takes about 3 minutes to executes, can u plz suggest how to improve the performance of the function. I have also attached the execution plan

    Create FUNCTION [dbo].[fnGetProductionProjection] --'2012','may','COR'

    (

    --DECLARE

    @fYear INT = 2011,

    @fMonth VARCHAR(15) = 'May',

    @rLocation CHAR(3) = 'COR'

    )

    RETURNS @tbl TABLE (fLocation VARCHAR(3), fType TINYINT, Specialty VARCHAR(15), fProductionByDrDays MONEY, fBudgetByDrDays MONEY, fProjection MONEY, fBudget MONEY, fProduction MONEY, fDrDays INT, fGoalDrDays INT, fOfficeDrDays INT)

    AS

    BEGIN

    DECLARE

    @FromDate AS DATETIME,

    @ToDate AS DATETIME

    DECLARE

    @LocationList AS TABLE (fLocation NVARCHAR(6))

    SET @FromDate = CONVERT(DATE,LTRIM(@fyear)+'/'+@fMonth+'/01')

    SET @ToDate = CONVERT(DATE,DATEADD(MINUTE,-1, DATEADD(MONTH,1,@FromDate)))

    IF @rLocation <> 'COR' INSERT INTO @LocationList (fLocation) VALUES (@rLocation )

    ELSE

    INSERT INTO @LocationList SELECT DimLocation.fLocation FROM DimLocation WHERE DimLocation.fLocation <> 'COR';

    WITH tProduction AS

    (

    SELECT

    Z.Flocation,

    Z.fSpecialty,

    isnull(SUM(Z.fProdAmt),0) AS fProdAmt,

    isnull(SUM(Z.fWorkedDays),0) AS fWorkedDays

    FROM

    (

    SELECT

    Y.Flocation,

    Y.DimProviderID,

    isnull(SUM(Y.fProdAmt),0) fProdAmt,

    isnull(SUM(Y.fWorkedDays),0) fWorkedDays,

    y.fSpecialty

    FROM

    (

    SELECT

    DimLocation.Flocation, a.DimProviderID, isnull(SUM(a.Fprodamt),0) AS fProdAmt, isnull(COUNT(DISTINCT a.Ftxdate),0) AS fWorkedDays,

    a.dimSpecialtyId as fSpecialty

    FROM

    (

    SELECT fctDxTxDetail.dimLocationId, fctDxTxDetail.dimProviderId, fctDxTxDetail.Fprodamt, fctDxTxDetail.Ftxdate,

    (CASE WHEN lkpProvider.RDH = 1 THEN ( SELECT DimSpecialities.dimSpecId FROM DimSpecialities WHERE DimSpecialities.fDesc = 'RDH' ) ELSE fctDxTxDetail.dimSpecialtyId END ) AS dimSpecialtyId

    from fctDxTxDetail

    INNER JOIN lkpProvider ON fctDxTxDetail.dimProviderId = lkpProvider.dimlkpProviderId

    ) AS a

    INNER JOIN DimLocation ON a.dimLocationId = DimLocation.dimLocationId

    WHERE

    DimLocation.fLocation IN (SELECT fLocation FROM @LocationList )

    AND A.Ftxdate IS NOT NULL

    AND a.Ftxdate BETWEEN @FromDate and @ToDate

    GROUP BY

    DimLocation.Flocation,

    a.DimProviderID,

    a.dimSpecialtyId

    UNION ALL

    SELECT

    X.fLocation,

    X.dimProviderId,

    SUM(X.eProd) AS fProdAmt,

    0 AS fWorkedDays,

    x.fSpecialty

    FROM

    (

    SELECT

    DimLocation.fLocation,

    b.dimProviderId,

    b.dimPatientId,

    b.ProductionAdjustmentAmount as eProd,

    B.dimSpecialtyId as fSpecialty

    FROM

    (

    SELECT fctProdAdj.dimLocationId, fctProdAdj.dimProviderId, fctProdAdj.dimPatientId, fctProdAdj.ProductionAdjustmentAmount, fctProdAdj.TxNumber, fctProdAdj.fDate, fctProdAdj.ftxdate ,-- fctProdAdj.FSuffix,

    (CASE WHEN lkpProvider.RDH = 1 THEN ( SELECT DimSpecialities.dimSpecId FROM DimSpecialities WHERE DimSpecialities.fDesc = 'RDH' ) ELSE fctProdAdj.dimSpecialtyId END ) AS dimSpecialtyId

    FROM fctProdAdj

    INNER JOIN lkpProvider ON fctProdAdj.dimProviderId = lkpProvider.dimlkpProviderId

    ) as b

    INNER JOIN DimLocation ON b.dimLocationId = DimLocation.dimLocationId

    WHERE DimLocation.fLocation IN (SELECT fLocation FROM @LocationList )

    AND b.TxNumber <> 0

    AND b.fDate BETWEEN @FromDate AND @ToDate -- and FSuffix=0

    and (B.fTxDate is null or B.fTxDate >= dateadd(year,-1,@FromDate))

    ) AS X

    GROUP BY

    X.fLocation,

    X.dimProviderId,

    x.fSpecialty

    ) AS Y

    GROUP BY

    Y.Flocation,

    Y.DimProviderID ,

    y.fSpecialty

    ) AS Z

    GROUP BY

    Z.Flocation,

    Z.fSpecialty

    ),

    tOfficeDays AS

    (

    SELECT

    X.Flocation,

    X.fSpecialty,

    isnull(SUM(X.fOfficeDays),0) AS fOfficeDays

    FROM

    (

    SELECT

    DimLocation.fLocation,

    a.DimProviderID,

    a.dimSpecialtyId as fSpecialty,

    isnull(COUNT(DISTINCT a.Ftxdate),0) AS fOfficeDays

    FROM

    (

    SELECT FctTxdxProdn.dimLocationId, FctTxdxProdn.DimProviderID, FctTxdxProdn.Ftxdate,

    (CASE WHEN lkpProvider.RDH = 1 THEN ( SELECT DimSpecialities.dimSpecId FROM DimSpecialities WHERE DimSpecialities.fDesc = 'RDH' ) ELSE FctTxdxProdn.dimSpecId END ) AS dimSpecialtyId

    FROM FctTxdxProdn

    INNER JOIN lkpProvider ON FctTxdxProdn.DimProviderID = lkpProvider.dimlkpProviderId

    ) AS a

    INNER JOIN DimLocation ON a.dimLocationId = DimLocation.dimLocationId

    INNER JOIN lkpProvider AS b ON A.DimProviderID = b.dimlkpProviderId

    WHERE

    DimLocation.fLocation IN ( SELECT fLocation FROM @LocationList )

    AND A.Ftxdate IS NOT NULL

    AND a.Ftxdate BETWEEN @FromDate and @ToDate

    and b.fOffProd = 0

    GROUP BY

    DimLocation.fLocation,

    a.dimSpecialtyId,

    a.DimProviderID

    ) AS X

    GROUP BY X.Flocation,

    X.fSpecialty

    )

    INSERT INTO @tbl

    SELECT

    DimGoal.fLocation, DimSpecialities.fType, DimSpecialities.fDesc as Specialty,

    ISNULL(tProduction.fProdAmt/CASE WHEN tProduction.fWorkedDays = 0 THEN 1 ELSE tProduction.fWorkedDays END,0) AS fProductionByDrDays,

    ISNULL(DimGoal.fBProd / (CASE WHEN ISNULL(DimGoal.fDrDays,0)=0 THEN 1 ELSE ISNULL(DimGoal.fDrDays,0) END),0) AS fBudgetByDrDays,

    ISNULL(CASE WHEN ISNULL(tProduction.fProdAmt / tOfficeDays.fOfficeDays * DimGoal.fDrDays,0) = 0 THEN DimGoal.fBProd ELSE tProduction.fProdAmt / tOfficeDays.fOfficeDays * DimGoal.fDrDays END,0) AS fProjection,

    ISNULL(DimGoal.fBProd,0) AS fBudget,

    isnull(tProduction.fProdAmt,0) AS fProduction,

    ISNULL(tProduction.fWorkedDays,0) AS fDrDays,

    isnull(DimGoal.fDrDays,0) as fGoalDrDays,

    isnull(tOfficeDays.fOfficeDays,0) as fOfficeDrDays

    FROM

    DimGoal

    INNER JOIN DimSpecialities ON DimGoal.fOrthoGen = DimSpecialities.ftype

    LEFT JOIN tProduction ON DimSpecialities.dimSpecId = tProduction.fSpecialty AND DimGoal.fLocation = tProduction.Flocation

    LEFT JOIN tOfficeDays ON DimSpecialities.dimSpecId = tOfficeDays.fSpecialty AND DimGoal.fLocation = tOfficeDays.Flocation

    WHERE

    DimGoal.fLocation IN ( SELECT fLocation FROM @LocationList)

    AND DimGoal.fYear = YEAR(@FromDate) AND DimGoal.fMonth = MONTH(@FromDate)

    ORDER BY

    DimGoal.fLocation, DimSpecialities.fType ;

    RETURN

    END

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • The first thing you should consider is, is it possible to remove the code from the function structure. The SQL Optimizer treats Functions as if they will only return one row which is why your performance is so bad.

    If you can't remove the code from the function, then i would try to encapsulate it as a single satement within the RETURN statement and drop the BEGIN and END statements.

    You might also see if you can change some of the "independent" SELECT statements can be combined with more traditional joins.

    See the blogs on optimization from Grant Fritchey and Rob Farley. They have written quite a bit on this topic.

    Happy tuning.

    Mickey Stuewe
    Sr Database Developer
    My blog
    Follow me on twitter: @SQLMickey
    Connect with me on LinkedIn
    --------------------------------------------------------------------------
    I laugh loudly and I laugh often. Just ask anyone who knows me.

  • Where exactly you are using this function , well to me it doesnt look like that this function itself might create some problem.as it doesnt have any kind of comple logc or calculation.What is the second query all about ? or i am overlooking something.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • I'd be willing to bet that you're joining this function to other tables or other functions. Multi-statement table valued user defined functions are notorious for this sort of bad behavior. As SQLMickey said, better to pull this out of the function and write the code as standard T-SQL. Don't trust the execution plan that you can see from the outer procedure. It's masking what's really going on. Instead, you'll need to query the cache to see how this function is behaving. You can use a script similar to what I have posted here on my blog[/url].

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • We had issues with Functions taking a long time to run when using parameters where the scope might change each time it's run.

    To resolve the issue, it was quicker and more efficient to let the Function perform the full query unfiltered, then add the parameters purely in the stored procedure that calls it.

    Alternatively, unless you're using the Function many times in different SPs, just have the whole code in a stored procedure in full.

  • stuart.holloway (1/4/2013)


    We had issues with Functions taking a long time to run when using parameters where the scope might change each time it's run.

    To resolve the issue, it was quicker and more efficient to let the Function perform the full query unfiltered, then add the parameters purely in the stored procedure that calls it.

    Alternatively, unless you're using the Function many times in different SPs, just have the whole code in a stored procedure in full.

    So you let a multi-statement UDF run without any filtering inside the UDF and then filtered the result set? How? As a WHERE clause in the calling statement? I'd be surprised if that radically improved performance. You're still dealing with the fact that the UDF uses table variables which don't have statistics. The filtering on the UDF would be scans on table variables that were loaded by scans against tables. If anything I'd expect to see performance degrade. Can you provide more details on this?

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • I don't have time right now to do anything real with the code, but if anyone is interested I have reformatted the code to make it a little bit easier to figure out what is going on the code.

    Create FUNCTION [dbo].[fnGetProductionProjection] --'2012','may','COR'

    (

    --DECLARE

    @fYear INT = 2011,

    @fMonth VARCHAR(15) = 'May',

    @rLocation CHAR(3) = 'COR'

    )

    RETURNS @tbl TABLE (

    fLocation VARCHAR(3),

    fType TINYINT,

    Specialty VARCHAR(15),

    fProductionByDrDays MONEY,

    fBudgetByDrDays MONEY,

    fProjection MONEY,

    fBudget MONEY,

    fProduction MONEY,

    fDrDays INT,

    fGoalDrDays INT,

    fOfficeDrDays INT

    )

    AS

    BEGIN

    DECLARE

    @FromDate AS DATETIME,

    @ToDate AS DATETIME

    DECLARE @LocationList AS TABLE (fLocation NVARCHAR(6))

    SET @FromDate = CONVERT(DATE,LTRIM(@fyear)+'/'+@fMonth+'/01')

    SET @ToDate = CONVERT(DATE,DATEADD(MINUTE,-1, DATEADD(MONTH,1,@FromDate)))

    IF @rLocation <> 'COR'

    INSERT INTO @LocationList (fLocation)

    VALUES (@rLocation )

    ELSE

    INSERT INTO @LocationList

    SELECT DimLocation.fLocation

    FROM DimLocation

    WHERE DimLocation.fLocation <> 'COR';

    WITH tProduction AS (

    SELECT

    Z.Flocation,

    Z.fSpecialty,

    isnull(SUM(Z.fProdAmt),0) AS fProdAmt,

    isnull(SUM(Z.fWorkedDays),0) AS fWorkedDays

    FROM

    (

    SELECT

    Y.Flocation,

    Y.DimProviderID,

    isnull(SUM(Y.fProdAmt),0) fProdAmt,

    isnull(SUM(Y.fWorkedDays),0) fWorkedDays,

    y.fSpecialty

    FROM

    (

    SELECT

    DimLocation.Flocation,

    a.DimProviderID,

    isnull(SUM(a.Fprodamt),0) AS fProdAmt,

    isnull(COUNT(DISTINCT a.Ftxdate),0) AS fWorkedDays,

    a.dimSpecialtyId as fSpecialty

    FROM

    (

    SELECT

    fctDxTxDetail.dimLocationId,

    fctDxTxDetail.dimProviderId,

    fctDxTxDetail.Fprodamt,

    fctDxTxDetail.Ftxdate,

    (CASE WHEN lkpProvider.RDH = 1

    THEN ( SELECT DimSpecialities.dimSpecId

    FROM DimSpecialities

    WHERE DimSpecialities.fDesc = 'RDH' )

    ELSE fctDxTxDetail.dimSpecialtyId

    END ) AS dimSpecialtyId

    FROM

    fctDxTxDetail

    INNER JOIN lkpProvider

    ON fctDxTxDetail.dimProviderId = lkpProvider.dimlkpProviderId

    ) AS a

    INNER JOIN DimLocation

    ON a.dimLocationId = DimLocation.dimLocationId

    WHERE

    DimLocation.fLocation IN (SELECT fLocation FROM @LocationList )

    AND A.Ftxdate IS NOT NULL

    AND a.Ftxdate BETWEEN @FromDate and @ToDate

    GROUP BY

    DimLocation.Flocation,

    a.DimProviderID,

    a.dimSpecialtyId

    UNION ALL

    SELECT

    X.fLocation,

    X.dimProviderId,

    SUM(X.eProd) AS fProdAmt,

    0 AS fWorkedDays,

    x.fSpecialty

    FROM

    (

    SELECT

    DimLocation.fLocation,

    b.dimProviderId,

    b.dimPatientId,

    b.ProductionAdjustmentAmount as eProd,

    B.dimSpecialtyId as fSpecialty

    FROM

    (

    SELECT

    fctProdAdj.dimLocationId,

    fctProdAdj.dimProviderId,

    fctProdAdj.dimPatientId,

    fctProdAdj.ProductionAdjustmentAmount,

    fctProdAdj.TxNumber,

    fctProdAdj.fDate,

    fctProdAdj.ftxdate,

    -- fctProdAdj.FSuffix,

    (CASE WHEN lkpProvider.RDH = 1

    THEN ( SELECT DimSpecialities.dimSpecId

    FROM DimSpecialities

    WHERE DimSpecialities.fDesc = 'RDH' )

    ELSE fctProdAdj.dimSpecialtyId

    END ) AS dimSpecialtyId

    FROM

    fctProdAdj

    INNER JOIN lkpProvider

    ON fctProdAdj.dimProviderId = lkpProvider.dimlkpProviderId

    ) as b

    INNER JOIN DimLocation

    ON b.dimLocationId = DimLocation.dimLocationId

    WHERE

    DimLocation.fLocation IN (SELECT fLocation FROM @LocationList )

    AND b.TxNumber <> 0

    AND b.fDate BETWEEN @FromDate AND @ToDate -- and FSuffix=0

    and (B.fTxDate is null or B.fTxDate >= dateadd(year,-1,@FromDate))

    ) AS X

    GROUP BY

    X.fLocation,

    X.dimProviderId,

    x.fSpecialty

    ) AS Y

    GROUP BY

    Y.Flocation,

    Y.DimProviderID ,

    y.fSpecialty

    ) AS Z

    GROUP BY

    Z.Flocation,

    Z.fSpecialty

    ),

    tOfficeDays AS

    (

    SELECT

    X.Flocation,

    X.fSpecialty,

    isnull(SUM(X.fOfficeDays),0) AS fOfficeDays

    FROM

    (

    SELECT

    DimLocation.fLocation,

    a.DimProviderID,

    a.dimSpecialtyId as fSpecialty,

    isnull(COUNT(DISTINCT a.Ftxdate),0) AS fOfficeDays

    FROM

    (

    SELECT

    FctTxdxProdn.dimLocationId,

    FctTxdxProdn.DimProviderID,

    FctTxdxProdn.Ftxdate,

    (CASE WHEN lkpProvider.RDH = 1

    THEN ( SELECT DimSpecialities.dimSpecId

    FROM DimSpecialities

    WHERE DimSpecialities.fDesc = 'RDH' )

    ELSE FctTxdxProdn.dimSpecId

    END ) AS dimSpecialtyId

    FROM

    FctTxdxProdn

    INNER JOIN lkpProvider

    ON FctTxdxProdn.DimProviderID = lkpProvider.dimlkpProviderId

    ) AS a

    INNER JOIN DimLocation

    ON a.dimLocationId = DimLocation.dimLocationId

    INNER JOIN lkpProvider AS b

    ON A.DimProviderID = b.dimlkpProviderId

    WHERE

    DimLocation.fLocation IN ( SELECT fLocation FROM @LocationList )

    AND A.Ftxdate IS NOT NULL

    AND a.Ftxdate BETWEEN @FromDate and @ToDate

    and b.fOffProd = 0

    GROUP BY

    DimLocation.fLocation,

    a.dimSpecialtyId,

    a.DimProviderID

    ) AS X

    GROUP BY

    X.Flocation,

    X.fSpecialty

    )

    INSERT INTO @tbl

    SELECT

    DimGoal.fLocation,

    DimSpecialities.fType,

    DimSpecialities.fDesc as Specialty,

    ISNULL(tProduction.fProdAmt/CASE WHEN tProduction.fWorkedDays = 0

    THEN 1

    ELSE tProduction.fWorkedDays

    END,0) AS fProductionByDrDays,

    ISNULL(DimGoal.fBProd / (CASE WHEN ISNULL(DimGoal.fDrDays,0) = 0

    THEN 1

    ELSE ISNULL(DimGoal.fDrDays,0)

    END),0) AS fBudgetByDrDays,

    ISNULL(CASE WHEN ISNULL(tProduction.fProdAmt / tOfficeDays.fOfficeDays * DimGoal.fDrDays,0) = 0

    THEN DimGoal.fBProd

    ELSE tProduction.fProdAmt / tOfficeDays.fOfficeDays * DimGoal.fDrDays

    END,0) AS fProjection,

    ISNULL(DimGoal.fBProd,0) AS fBudget,

    isnull(tProduction.fProdAmt,0) AS fProduction,

    ISNULL(tProduction.fWorkedDays,0) AS fDrDays,

    isnull(DimGoal.fDrDays,0) as fGoalDrDays,

    isnull(tOfficeDays.fOfficeDays,0) as fOfficeDrDays

    FROM

    DimGoal

    INNER JOIN DimSpecialities

    ON DimGoal.fOrthoGen = DimSpecialities.ftype

    LEFT JOIN tProduction

    ON DimSpecialities.dimSpecId = tProduction.fSpecialty AND

    DimGoal.fLocation = tProduction.Flocation

    LEFT JOIN tOfficeDays

    ON DimSpecialities.dimSpecId = tOfficeDays.fSpecialty AND

    DimGoal.fLocation = tOfficeDays.Flocation

    WHERE

    DimGoal.fLocation IN ( SELECT fLocation FROM @LocationList)

    AND DimGoal.fYear = YEAR(@FromDate) AND DimGoal.fMonth = MONTH(@FromDate)

    ORDER BY

    DimGoal.fLocation,

    DimSpecialities.fType ;

    RETURN

    END

  • Bhuvnesh (1/4/2013)


    well to me it doesnt look like that this function itself might create some problem.as it doesnt have any kind of comple logc or calculation.

    i am taking my words back here , i overlooked the second part (CTE query in my above post).

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply