A query with two UDT parameters takes 0.3 seconds but when the encapsulated in a inline table valued function it takes 3.5+ seconds.
I've read (Why is a UDF so much slower than a subquery?) but am struggling with how to fix/rewrite.
Per @JasonALong's feedback below,
Execution plan for SELECT statement that completes in 0.3 seconds: https://www.brentozar.com/pastetheplan/?id=HJnrqC53Z (note the SQL is available on this page).
Code for Function that completes in 3.5 seconds pasted below and Execution plan at this link: https://www.brentozar.com/pastetheplan/?id=BJZbqR93b
SELECT
SelectedContracts.MeasurableID,
SelectedContracts.EntityID,
EntityName,
EntityAbbrev,
EntityLogoURL,
EntityHex1,
EntityHex2,
EntitySportID,
MeasurableName,
MeasurableOrganizationID,
YearFilter,
SeasonFilter,
CategoryFilter,
ResultFilter,
Logo4Result,
MeasurableSportID,
MouseoverFooter,
ContractRank4Org,
ContractEndUTC,
HighContractPrice4Period,
HighTradeID,
HighTradeUTC,
HighTradeNumberOfContracts,
HighTradeCurrency,
LowContractPrice4Period,
LowTradeID,
LowTradeUTC,
LowTradeNumberOfContracts,
LowTradeCurrency,
LastTradePrice,
LastTradeID,
LastTradeUTC,
LastTradeNumberOfContracts,
LastTradeCurrency,
SecondLastTradePrice,
SecondLastTradeID,
SecondLastTradeUTC,
SecondLastTradeNumberOfContracts,
SecondLastTradeCurrency,
ContractPrice4ChangeCalc,
ContractID4ChangeCalc,
ContractUTC4ChangeCalc,
ContractsNumberTraded4ChangeCalc,
ContractCurrency4ChangeCalc,
HighestBidID,
HighestBidMemberID,
HighestBidPrice,
HighestBidAvailableContracts,
HighestBidCurrency,
LowestAskID,
LowestAskMemberID,
LowestAskPrice,
LowestAskAvailableContracts,
LowestAskCurrency
FROM
(
SELECT
dbo.Contracts.MeasurableID,
dbo.Contracts.EntityID
FROM
dbo.Contracts
WHERE
dbo.Contracts.MeasurableID IN ((2030),(2017),(2018),(2019),(2020),( 2028),(2024),(2027),(2029),(2022),( 4018),(4019),(4020),(4021))
GROUP BY
dbo.Contracts.MeasurableID,
dbo.Contracts.EntityID
) SelectedContracts
INNER JOIN
(
SELECT
dbo.Entities.ID,
--dbo.Entities.OrganizationID, -- Get OrganizationID from Measurable since some Entities (European soccer teams) have multiple Orgs
dbo.Entities.EntityName,
dbo.Entities.EntityAbbrev,
dbo.Entities.logoURL AS EntityLogoURL,
dbo.Entities.Hex1 AS EntityHex1,
dbo.Entities.Hex2 AS EntityHex2,
dbo.Entities.SportID AS EntitySportID
FROM
dbo.Entities
) SelectedEntities ON SelectedContracts.EntityID = SelectedEntities.ID
INNER JOIN
(
SELECT
dbo.Measurables.ID AS MeasurableID,
dbo.Measurables.Name AS MeasurableName,
dbo.Measurables.OrganizationID AS MeasurableOrganizationID,
dbo.Measurables.[Year] AS YearFilter,
dbo.Measurables.Season AS SeasonFilter,
dbo.Measurables.Category AS CategoryFilter,
dbo.Measurables.Result AS ResultFilter,
dbo.Measurables.Logo4Result,
dbo.Measurables.SportID AS MeasurableSportID,
dbo.Measurables.MouseoverFooter,
dbo.Measurables.ContractRank4Org,
dbo.Measurables.EndUTC AS ContractEndUTC
FROM
dbo.Measurables
) MEASURABLES_table ON SelectedContracts.MeasurableID = MEASURABLES_table.MeasurableID
LEFT JOIN
(
SELECT
MeasurableID,
EntityID,
ContractPrice AS HighContractPrice4Period,
ID AS HighTradeID,
UTCMatched AS HighTradeUTC,
NumberOfContracts AS HighTradeNumberOfContracts,
CurrencyCode AS HighTradeCurrency
FROM
(
SELECT
*, ROW_NUMBER () OVER (
PARTITION BY MeasurableID,
EntityID
ORDER BY
ContractPrice DESC,
ID DESC
) RowNumber -- ID DESC means most recent trade of ties
FROM
Contracts
WHERE
MeasurableID IN ((2030),(2017),(2018),(2019),(2020),( 2028),(2024),(2027),(2029),(2022),( 4018),(4019),(4020),(4021))
AND dbo.Contracts.UTCmatched < DATEADD(DAY, -30, SYSDATETIME())
AND ( CurrencyCode IN (('GBP'), ('CAD'), ('INR'), ('BRL'), ('MXN'), ('CHF'), ('RUB'))
)
) AS InnerSelect4HighTrade
WHERE
InnerSelect4HighTrade.RowNumber = 1
) HighTrades ON SelectedContracts.MeasurableID = HighTrades.MeasurableID AND SelectedContracts.EntityID = HighTrades.EntityID
LEFT JOIN
(
SELECT
MeasurableID,
EntityID,
ContractPrice AS LowContractPrice4Period,
ID AS LowTradeID,
UTCMatched AS LowTradeUTC,
NumberOfContracts AS LowTradeNumberOfContracts,
CurrencyCode AS LowTradeCurrency
FROM
(
SELECT
*, ROW_NUMBER () OVER (
PARTITION BY MeasurableID,
EntityID
ORDER BY
ContractPrice ASC,
ID DESC
) RowNumber -- ID DESC means most recent trade of ties
FROM
Contracts
WHERE
MeasurableID IN ((2030),(2017),(2018),(2019),(2020),( 2028),(2024),(2027),(2029),(2022),( 4018),(4019),(4020),(4021))
AND dbo.Contracts.UTCmatched < DATEADD(DAY, -30, SYSDATETIME())
AND ( CurrencyCode IN (('GBP'), ('CAD'), ('INR'), ('BRL'), ('MXN'), ('CHF'), ('RUB'))
)
) AS InnerSelect4LowTrade
WHERE InnerSelect4LowTrade.RowNumber = 1
) LowTrades ON SelectedContracts.MeasurableID = LowTrades.MeasurableID AND SelectedContracts.EntityID = LowTrades.EntityID
LEFT JOIN
(
SELECT
MeasurableID,
EntityID,
ContractPrice AS LastTradePrice,
ID AS LastTradeID,
UTCMatched AS LastTradeUTC,
NumberOfContracts AS LastTradeNumberOfContracts,
CurrencyCode AS LastTradeCurrency
FROM
(
SELECT
*, ROW_NUMBER () OVER (
PARTITION BY MeasurableID,
EntityID
ORDER BY
ID DESC
) RowNumber -- ID DESC means most recent trade of ties
FROM
Contracts
WHERE
MeasurableID IN ((2030),(2017),(2018),(2019),(2020),( 2028),(2024),(2027),(2029),(2022),( 4018),(4019),(4020),(4021))
AND ( CurrencyCode IN (('GBP'), ('CAD'), ('INR'), ('BRL'), ('MXN'), ('CHF'), ('RUB'))
)
) AS InnerSelect4LastTrade
WHERE InnerSelect4LastTrade.RowNumber = 1
) LastTrades ON SelectedContracts.MeasurableID = LastTrades.MeasurableID AND SelectedContracts.EntityID = LastTrades.EntityID
LEFT JOIN
(
SELECT
MeasurableID,
EntityID,
ContractPrice AS SecondLastTradePrice,
ID AS SecondLastTradeID,
UTCMatched AS SecondLastTradeUTC,
NumberOfContracts AS SecondLastTradeNumberOfContracts,
CurrencyCode AS SecondLastTradeCurrency
FROM
(
SELECT
*, ROW_NUMBER () OVER (
PARTITION BY MeasurableID,
EntityID
ORDER BY
ID DESC
) RowNumber -- ID DESC means most recent trade of ties
FROM
Contracts
WHERE
MeasurableID IN ((2030),(2017),(2018),(2019),(2020),( 2028),(2024),(2027),(2029),(2022),( 4018),(4019),(4020),(4021))
AND ( CurrencyCode IN (('GBP'), ('CAD'), ('INR'), ('BRL'), ('MXN'), ('CHF'), ('RUB'))
)
--need time filter???
) AS InnerSelect4SecondToLastTrade
WHERE InnerSelect4SecondToLastTrade.RowNumber = 2
) SecondToLastTrade ON SelectedContracts.MeasurableID = SecondToLastTrade.MeasurableID AND SelectedContracts.EntityID = SecondToLastTrade.EntityID
LEFT JOIN
(
SELECT
MeasurableID,
EntityID,
ContractPrice AS ContractPrice4ChangeCalc,
ID AS ContractID4ChangeCalc,
UTCMatched AS ContractUTC4ChangeCalc,
NumberOfContracts AS ContractsNumberTraded4ChangeCalc,
CurrencyCode AS ContractCurrency4ChangeCalc
FROM
(
SELECT
*, ROW_NUMBER () OVER (
PARTITION BY MeasurableID,
EntityID
ORDER BY
ID DESC -- ID DESC equals the most recent trade if ties
) RowNumber
FROM
Contracts
WHERE
MeasurableID IN ((2030),(2017),(2018),(2019),(2020),( 2028),(2024),(2027),(2029),(2022),( 4018),(4019),(4020),(4021))
AND ( CurrencyCode IN (('GBP'), ('CAD'), ('INR'), ('BRL'), ('MXN'), ('CHF'), ('RUB'))
)
AND dbo.Contracts.UTCmatched < DATEADD(Day ,-30, SYSDATETIME())
) AS InnerSelect4ChangeCalcPerPeriod
WHERE InnerSelect4ChangeCalcPerPeriod.RowNumber = 1
) Trade4ChangeCalcPerPeriod ON SelectedContracts.MeasurableID = Trade4ChangeCalcPerPeriod.MeasurableID AND SelectedContracts.EntityID = Trade4ChangeCalcPerPeriod.EntityID
LEFT JOIN
(
SELECT
MeasurableID,
EntityID,
ID AS HighestBidID,
MemberID AS HighestBidMemberID,
BidPrice AS HighestBidPrice,
AvailableContracts AS HighestBidAvailableContracts,
CurrencyCode AS HighestBidCurrency
FROM
(
SELECT
*, ROW_NUMBER () OVER (
PARTITION BY MeasurableID,
EntityID
ORDER BY
BidPrice DESC,
ID DESC
) RowNumber
FROM
dbo.Interest2Buy
WHERE
MeasurableID IN ((2030),(2017),(2018),(2019),(2020),( 2028),(2024),(2027),(2029),(2022),( 4018),(4019),(4020),(4021))
AND AvailableContracts > 0
AND ( CurrencyCode IN (('GBP'), ('CAD'), ('INR'), ('BRL'), ('MXN'), ('CHF'), ('RUB'))
)
) AS InnerSelect4HighestBid
WHERE InnerSelect4HighestBid.RowNumber = 1
) HighestBids ON SelectedContracts.MeasurableID = HighestBids.MeasurableID AND SelectedContracts.EntityID = HighestBids.EntityID
LEFT JOIN
(
SELECT
MeasurableID,
EntityID,
ID AS LowestAskID,
MemberID AS LowestAskMemberID,
AskPrice AS LowestAskPrice,
AvailableContracts AS LowestAskAvailableContracts,
CurrencyCode AS LowestAskCurrency
FROM
(
SELECT
*, ROW_NUMBER () OVER (
PARTITION BY MeasurableID,
EntityID
ORDER BY
AskPrice ASC,
ID DESC
) RowNumber
FROM
dbo.Interest2Sell
WHERE
MeasurableID IN ((2030),(2017),(2018),(2019),(2020),( 2028),(2024),(2027),(2029),(2022),( 4018),(4019),(4020),(4021))
AND AvailableContracts > 0
AND ( CurrencyCode IN (('GBP'), ('CAD'), ('INR'), ('BRL'), ('MXN'), ('CHF'), ('RUB'))
)
) AS InnerSelect4BestAsk
WHERE InnerSelect4BestAsk.RowNumber = 1
) BestAsks ON SelectedContracts.MeasurableID = BestAsks.MeasurableID AND SelectedContracts.EntityID = BestAsks.EntityID
Using joins instead of "IN" clause helped a great deal. (Though I also changed the table var to a temp table and that too helped significantly.)