Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Function taking long time Expand / Collapse
Author
Message
Posted Thursday, January 3, 2013 11:11 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
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/


  Post Attachments 
ExecutionPlan.sqlplan (3 views, 8.89 KB)
Post #1402690
Posted Friday, January 4, 2013 12:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 3:37 PM
Points: 25, Visits: 93
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.
Post #1402710
Posted Friday, January 4, 2013 1:03 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:45 AM
Points: 2,836, Visits: 3,952
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
Post #1402738
Posted Friday, January 4, 2013 4:04 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 6:00 AM
Points: 15,518, Visits: 27,902
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.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1402787
Posted Friday, January 4, 2013 5:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 20, 2013 1:56 PM
Points: 17, Visits: 29
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.
Post #1402819
Posted Friday, January 4, 2013 6:09 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 6:00 AM
Points: 15,518, Visits: 27,902
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1402837
Posted Friday, January 4, 2013 6:12 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:41 PM
Points: 23,033, Visits: 31,555
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





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1402838
Posted Sunday, January 6, 2013 10:42 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:45 AM
Points: 2,836, Visits: 3,952
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
Post #1403406
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse