January 3, 2013 at 11:11 pm
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/
January 4, 2013 at 12:01 am
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.
January 4, 2013 at 1:03 am
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;-)
January 4, 2013 at 4:04 am
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
January 4, 2013 at 5:19 am
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.
January 4, 2013 at 6:09 am
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
January 4, 2013 at 6:12 am
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
January 6, 2013 at 10:42 pm
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