I have an Inline Table Valued Function and for some reason it runs incredibly slow, to the point where I just have to cancel it.
When I run the same exact SQL on an ad hoc basis, and create the parameters by declaring and setting them, it runs in about 3 minutes, as expected. How is it possible that simply running it as an ITVF vs ad hoc is creating a difference in performance?!
An added wrinkle that I recently discovered is if I run it as a ITVF, and pass it x value it runs in about 3 minutes, and when I pass it y value, it takes forever. When I run it ad hoc, I'm using the allegedly troublesome y value, and it runs in 3 minutes! I don't want this to detract too much from the main question which is, how can SQL run faster ad hoc vs ITVF, I just wanted to mention it in case it would offer some insight.
Here's the ITVF signature:
[aop].[itvfOpsProductivity](@Interval varchar(7),@ToMonth tinyint = 12,@AOPYear char(4),@PLANNING_EstimateType varchar(4),@ACTYear char(4))
Here's how I called the ITVF
select * from [aop].[itvfOpsProductivity]('YTD',10,'2017','AOP','2016')
When I call the ITVF with '2016' instead of '2017' in the 3rd parameter, it runs fine.
Here's how I ran the SQL inline / ad hoc (it ran in fine):
declare @Interval varchar(7)
set @Interval='YTD'
declare @ToMonth tinyint
set @ToMonth=10
declare @AOPYear char(4)
set @AOPYear='2017'
declare @PLANNING_EstimateType varchar(4)
set @PLANNING_EstimateType='AOP'
declare @ACTYear char(4)
set @ACTYear='2016'
SELECT
Financial.Segment,
Financial.EstimateRegionName as Region,
Financial.EstimateClusterName as Cluster,
Financial.EstimateCountryName as Country,
Financial.SAPCountryCode,
Operational.BU,
Operational.[Equipment Class],
Operational.ProductGroupCode,
Operational.ProductGroupCodeDesc,
Operational.ServiceCode,
Operational.ServiceCodeDesc,
Operational.[TEU or Install_ACT],
Operational.[TEU or Install_AOP],
Operational.[Hours_ACT],
Operational.[Hours_AOP],
Operational.[OriginalHours_AOP],
Operational.[Part Cost_ACT],
Operational.[Part Cost_AOP],
Operational.[OriginalPart Cost_AOP],
IsNull(Operational.TotalHoursPerCountry_ACT,0) as TotalHoursPerCountry_ACT,
IsNull(Operational.TotalHoursPerCountry_AOP,0) as TotalHoursPerCountry_AOP,
Case When Operational.TotalHoursPerCountry_ACT<>0
Then ( IsNull(Financial.TotalCostPerCountry_ACT,0) - Operational.FEPartCostPerCountry_ACT ) / Operational.TotalHoursPerCountry_ACT
End as FullyBurdenedBHR_ACT,
Case When Operational.TotalHoursPerCountry_AOP<>0
Then ( IsNull(Financial.TotalCostPerCountry_AOP,0) - Operational.FEPartCostPerCountry_AOP ) / Operational.TotalHoursPerCountry_AOP
End as FullyBurdenedBHR_AOP,
Operational.FEPartCostPerCountry_ACT,
Operational.FEPartCostPerCountry_AOP,
Financial.TotalCostPerCountry_ACT,
Financial.TotalCostPerCountry_AOP
FROM
(
SELECT IsNull(TotalCost_ACT.Segment,TotalCost_AOP.Segment) as Segment, IsNull(TotalCost_ACT.EstimateRegionName,TotalCost_AOP.EstimateRegionName) as EstimateRegionName, IsNull(TotalCost_ACT.EstimateClusterName,TotalCost_AOP.EstimateClusterName) as EstimateClusterName, IsNull(TotalCost_ACT.EstimateCountryName,TotalCost_AOP.EstimateCountryName) as EstimateCountryName, IsNull(TotalCost_ACT.SAPCountryCode,TotalCost_AOP.SAPCountryCode) as SAPCountryCode,
TotalCostPerCountry_ACT,
TotalCostPerCountry_AOP
FROM (
SELECT Segment, EstimateRegionName, EstimateClusterName, EstimateCountryName, SAPCountryCode,
sum(TotalCost) as TotalCostPerCountry_ACT
FROM [aop].[itvfProductivityCost](@Interval,@ToMonth,@ACTYear,'ACT')
GROUP BY Segment, EstimateRegionName, EstimateClusterName, EstimateCountryName, SAPCountryCode
) as TotalCost_ACT
FULL OUTER JOIN
(
SELECT Segment, EstimateRegionName, EstimateClusterName, EstimateCountryName, SAPCountryCode,
sum(TotalCost) as TotalCostPerCountry_AOP
FROM [aop].[itvfProductivityCost](@Interval,@ToMonth,@AOPYear,@PLANNING_EstimateType)
GROUP BY Segment, EstimateRegionName, EstimateClusterName, EstimateCountryName, SAPCountryCode
) as TotalCost_AOP
ON TotalCost_ACT.Segment=TotalCost_AOP.Segment and TotalCost_ACT.EstimateRegionName=TotalCost_AOP.EstimateRegionName and TotalCost_ACT.SAPCountryCode=TotalCost_AOP.SAPCountryCode
) as Financial
LEFT JOIN
(
SELECT
IsNull(vwHoursPartsPerTEU_ACT.Segment,vwHoursPartsPerTEU_AOP.Segment) as Segment,
IsNull(vwHoursPartsPerTEU_ACT.Region,vwHoursPartsPerTEU_AOP.Region) as Region,
IsNull(vwHoursPartsPerTEU_ACT.Cluster,vwHoursPartsPerTEU_AOP.Cluster) as Cluster,
IsNull(vwHoursPartsPerTEU_ACT.Country,vwHoursPartsPerTEU_AOP.Country) as Country,
IsNull(vwHoursPartsPerTEU_ACT.SAPCountryCode,vwHoursPartsPerTEU_AOP.SAPCountryCode) as SAPCountryCode,
IsNull(vwHoursPartsPerTEU_ACT.BU,vwHoursPartsPerTEU_AOP.BU) as BU,
IsNull(vwHoursPartsPerTEU_ACT.[Equipment Class],vwHoursPartsPerTEU_AOP.[Equipment Class]) as [Equipment Class],
IsNull(vwHoursPartsPerTEU_ACT.ProductGroupCode,vwHoursPartsPerTEU_AOP.ProductGroupCode) as ProductGroupCode,
IsNull(vwHoursPartsPerTEU_ACT.ProductGroupCodeDesc,vwHoursPartsPerTEU_AOP.ProductGroupCodeDesc) as ProductGroupCodeDesc,
IsNull(vwHoursPartsPerTEU_ACT.ServiceCode,vwHoursPartsPerTEU_AOP.ServiceCode) as ServiceCode,
IsNull(vwHoursPartsPerTEU_ACT.ServiceCodeDesc,vwHoursPartsPerTEU_AOP.ServiceCodeDesc) as ServiceCodeDesc,
IsNull(vwHoursPartsPerTEU_ACT.[TEU or Install],0) AS [TEU or Install_ACT],
IsNull(vwHoursPartsPerTEU_AOP.[TEU or Install],0) AS [TEU or Install_AOP],
IsNull(vwHoursPartsPerTEU_ACT.[Hours],0) AS [Hours_ACT],
IsNull(vwHoursPartsPerTEU_AOP.[Hours],0) AS [Hours_AOP],
IsNull(vwHoursPartsPerTEU_AOP.[OriginalHours],0) AS [OriginalHours_AOP],
Sum(IsNull(vwHoursPartsPerTEU_ACT.[Hours],0)) Over(Partition By IsNull(vwHoursPartsPerTEU_ACT.Segment,vwHoursPartsPerTEU_AOP.Segment),IsNull(vwHoursPartsPerTEU_ACT.SAPCountryCode,vwHoursPartsPerTEU_AOP.SAPCountryCode)) as [TotalHoursPerCountry_ACT],
Sum(IsNull(vwHoursPartsPerTEU_AOP.[Hours],0)) Over(Partition By IsNull(vwHoursPartsPerTEU_ACT.Segment,vwHoursPartsPerTEU_AOP.Segment),IsNull(vwHoursPartsPerTEU_ACT.SAPCountryCode,vwHoursPartsPerTEU_AOP.SAPCountryCode)) as [TotalHoursPerCountry_AOP],
IsNull(vwHoursPartsPerTEU_ACT.[Part Cost],0) AS [Part Cost_ACT],
IsNull(vwHoursPartsPerTEU_AOP.[Part Cost],0) AS [Part Cost_AOP],
IsNull(vwHoursPartsPerTEU_AOP.[OriginalPart Cost],0) AS [OriginalPart Cost_AOP],
Sum(IsNull(vwHoursPartsPerTEU_ACT.[Part Cost],0)) Over(Partition By IsNull(vwHoursPartsPerTEU_ACT.Segment,vwHoursPartsPerTEU_AOP.Segment),IsNull(vwHoursPartsPerTEU_ACT.SAPCountryCode,vwHoursPartsPerTEU_AOP.SAPCountryCode)) as [FEPartCostPerCountry_ACT],
Sum(IsNull(vwHoursPartsPerTEU_AOP.[Part Cost],0)) Over(Partition By IsNull(vwHoursPartsPerTEU_ACT.Segment,vwHoursPartsPerTEU_AOP.Segment),IsNull(vwHoursPartsPerTEU_ACT.SAPCountryCode,vwHoursPartsPerTEU_AOP.SAPCountryCode)) as [FEPartCostPerCountry_AOP]
FROM
(
-- Too much missing cost from PerTEU report, i.e. $200k part cost discrepancy between NonTEU and TEU report for Germany in 2015
--SELECT Region, Cluster, Country, SAPCountryCode, BU, [Equipment Class], ProductGroupCode, ProductGroupCodeDesc, PrimaryServiceCode as ServiceCode, PrimaryServiceCodeDesc as ServiceCodeDesc, Sum([TEU or Install]) as [TEU or Install], Sum([Total Labor Hours]+[Total Travel Hours]) as [Hours], Sum([Total Part Cost]) as [Part Cost]
--FROM [aop].[vwHoursPartsPerTEU]
--WHERE [Product Date] between dbo.fnCurrYearStartDateForPrevMonth(getDate()) and (select max(FromMonth) from fin.vwFinancials where FromMonth>=dbo.fnCurrYearStartDateForPrevMonth(getDate()) and EstimateType='ACT' and (Account is null or Account<>'AA_BASICCOGS') and FinanceType In('NonTradeCost','TradeCost'))
--GROUP BY Region, Cluster, Country, SAPCountryCode, BU, [Equipment Class], ProductGroupCode, ProductGroupCodeDesc, PrimaryServiceCode, PrimaryServiceCodeDesc
select Segment, EstimateRegionName as Region, EstimateClusterName as Cluster, EstimateCountryName as Country, SAPCountryCode, BU, EquipmentClass as [Equipment Class], ProductGroupCode, ProductGroupCodeDesc, ServiceCode, ServiceCodeDesc, [TEU or Install], [Hours], [Parts] as [Part Cost]
from [aop].[itvfHoursPartsPerPopulation](@Interval,@ToMonth,@ACTYear)
) AS vwHoursPartsPerTEU_ACT
FULL OUTER JOIN
(
-- Reset to Original Hours & Parts for assumptions resulting in a negative value
select [Segment], Region, Cluster, Country, SAPCountryCode, BU, [Equipment Class], ProductGroupCode, ProductGroupCodeDesc, ServiceCode, ServiceCodeDesc
,[TEU or Install_CY] as [TEU or Install]
,[OriginalHours_CY] as [OriginalHours]
,case when [Hours_CY]/nullif([TEU or Install_CY],0)<0
then [OriginalHours_CY]
else [Hours_CY]
end as [Hours]
,[OriginalPart Cost_CY] as [OriginalPart Cost]
,case when [Part Cost_CY]/nullif([TEU or Install_CY],0)<0
then [OriginalPart Cost_CY]
else [Part Cost_CY]
end as [Part Cost]
--from aop.[itvfOperationalProjections](@Interval,@AOPYear)
from aop.[itvfOperationalProjections](@Interval,@ToMonth,@AOPYear)
) AS vwHoursPartsPerTEU_AOP
ON vwHoursPartsPerTEU_ACT.Segment = vwHoursPartsPerTEU_AOP.Segment AND vwHoursPartsPerTEU_ACT.SAPCountryCode = vwHoursPartsPerTEU_AOP.SAPCountryCode AND vwHoursPartsPerTEU_ACT.BU = vwHoursPartsPerTEU_AOP.BU AND vwHoursPartsPerTEU_ACT.[Equipment Class] = vwHoursPartsPerTEU_AOP.[Equipment Class] AND vwHoursPartsPerTEU_ACT.ProductGroupCodeDesc = vwHoursPartsPerTEU_AOP.ProductGroupCodeDesc AND vwHoursPartsPerTEU_ACT.ServiceCode = vwHoursPartsPerTEU_AOP.ServiceCode
) as Operational
ON Financial.Segment=Operational.Segment and Financial.EstimateCountryName=Operational.Country
Obviously the code is very long and complex, so I was hoping we might be able to resolve this conceptually.
I tried optimize for unknown per a suggestion, but the ad hoc is still way faster than the ITVF. Here's how I tried it:
select * from [aop].[itvfOpsProductivity]('YTD',10,'2017','AOP','2016')
option (optimize for unknown)
I did further isolation testing and found that it wasn't actually related to the fact that I was running it from an ITVF, because in fact, when I took the contents of the ITVF and replaced the variables with literals, it still ran poorly! Yet, when I run the contents of the ITVF ad hoc, with declared and set parameters, the performance was still fine. I ended up resolving the issue by tuning some of the queries under the hood, but it did not make sense why that worked. Both approaches should've performed the same, I would've thought.
