Fix for slow SQL_INLINE_TABLE_VALUED_FUNCTION

Question

My query with two UDT parameters takes 0.3 seconds but when the same code is encapsulated into an inline table valued function it takes 3.5+ seconds.

I've spend days researching/attempting fixes (correlated subqueries??) but have not found a solution on how to fix/rewrite.

Execution plan for SELECT statement that completes in 0.3 seconds: https://www.brentozar.com/pastetheplan/?id=HJnrqC53Z

Execution plan for Function that completes in 3.5 seconds: https://www.brentozar.com/pastetheplan/?id=BJZbqR93b

Function Code below:

ALTER FUNCTION [dbo].[WIPfn_getContractDataPER_MeasurableId_Currencies_ChangePeriod]
(
 @UDT4MeasurableIDs MeasurableIDs4Bricks READONLY,
 @UDT4CurrencyCodes CurrenciesTable4sp READONLY,
 @TimePeriod4ChangCalcs INT = 30
)
RETURNS TABLE
RETURN
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 (Select MeasurableID from @UDT4MeasurableIDs)
 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 (Select MeasurableID from @UDT4MeasurableIDs)
 AND ( ( (SELECT TOP 1 CurrencyCode from @UDT4CurrencyCodes) IS NULL) -- if no CurrencyCodes are provided then ignore currencies clause/return all 
 OR CurrencyCode IN (SELECT CurrencyCode from @UDT4CurrencyCodes) 
 ) 
 AND dbo.Contracts.UTCmatched < DATEADD(DAY, @TimePeriod4ChangCalcs, SYSDATETIME())
 ) 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 (Select MeasurableID from @UDT4MeasurableIDs)
 AND ( ( (SELECT TOP 1 CurrencyCode from @UDT4CurrencyCodes) IS NULL) -- if no CurrencyCodes are provided then ignore currencies clause/return all 
 OR CurrencyCode IN (SELECT CurrencyCode from @UDT4CurrencyCodes) 
 ) 
 AND dbo.Contracts.UTCmatched < DATEADD(DAY, @TimePeriod4ChangCalcs, SYSDATETIME())
 ) 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 (Select MeasurableID from @UDT4MeasurableIDs)
 AND ( ( (SELECT TOP 1 CurrencyCode from @UDT4CurrencyCodes) IS NULL) -- if no CurrencyCodes are provided then ignore currencies clause/return all 
 OR CurrencyCode IN (SELECT CurrencyCode from @UDT4CurrencyCodes) 
 ) 
 ) 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 (Select MeasurableID from @UDT4MeasurableIDs)
 AND ( ( (SELECT TOP 1 CurrencyCode from @UDT4CurrencyCodes) IS NULL) -- if no CurrencyCodes are provided then ignore currencies clause/return all 
 OR CurrencyCode IN (SELECT CurrencyCode from @UDT4CurrencyCodes) 
 ) 
 ) 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 (Select MeasurableID from @UDT4MeasurableIDs)
 AND ( ( (SELECT TOP 1 CurrencyCode from @UDT4CurrencyCodes) IS NULL) -- if no CurrencyCodes are provided then ignore currencies clause/return all 
 OR CurrencyCode IN (SELECT CurrencyCode from @UDT4CurrencyCodes) 
 ) 
 AND dbo.Contracts.UTCmatched < DATEADD(Day ,@TimePeriod4ChangCalcs, 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 (Select MeasurableID from @UDT4MeasurableIDs)
 AND ( ( (SELECT TOP 1 CurrencyCode from @UDT4CurrencyCodes) IS NULL) -- if no CurrencyCodes are provided then ignore currencies clause/return all 
 OR CurrencyCode IN (SELECT CurrencyCode from @UDT4CurrencyCodes) 
 ) 
 AND AvailableContracts > 0
 ) 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 (Select MeasurableID from @UDT4MeasurableIDs)
 AND ( ( (SELECT TOP 1 CurrencyCode from @UDT4CurrencyCodes) IS NULL) -- if no CurrencyCodes are provided then ignore currencies clause/return all 
 OR CurrencyCode IN (SELECT CurrencyCode from @UDT4CurrencyCodes) 
 ) 
 AND AvailableContracts > 0
 ) AS InnerSelect4BestAsk
 WHERE InnerSelect4BestAsk.RowNumber = 1
) BestAsks ON SelectedContracts.MeasurableID = BestAsks.MeasurableID AND SelectedContracts.EntityID = BestAsks.EntityID

Answer 1

Not sure how much it will help, but (as an example) here's your HighTrades LEFT JOIN, rewritten to use JOINs instead of IN:

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
 INNER JOIN @UDT4MeasurableIDs m ON dbo.Contracts.MeasurableID = m.MeasurableID
 LEFT JOIN @UDT4CurrencyCodes cc ON dbo.Contracts.CurrencyCode = cc.CurrencyCode
 WHERE dbo.Contracts.UTCmatched < DATEADD(DAY, @TimePeriod4ChangCalcs, SYSDATETIME())
 AND ( NOT EXISTS (SELECT 1 from @UDT4CurrencyCodes) -- if no CurrencyCodes are provided then ignore currencies clause/return all 
 OR cc.CurrencyCode IS NOT NULL
 ) 
 ) AS InnerSelect4HighTrade
 WHERE 
 InnerSelect4HighTrade.RowNumber = 1
) HighTrades ON SelectedContracts.MeasurableID = HighTrades.MeasurableID AND SelectedContracts.EntityID = HighTrades.EntityID

Beyond that, a wholesale refactoring of the process, possibly generating a SelectedContracts temporary table (and maybe including the matching IDs for the various RowNumber = 1s from the LEFT JOINs), then adding in the rest of the data you need may help. Ideally, you'd have an index on Contracts that would include just what you need to get the ID values, which might be much smaller than the full Contracts table. As far as I can see, the following columns are all that's needed to generate the row numbers for all of the various LEFT JOINs:

• MeasurableID
• EntityID
• ID
• UTCMatched
• CurrencyCode
• ContractPrice
• BidPrice
• AskPrice
• AvailableContracts

For instance, an index on the first three columns (including the other six) might help. Then, you join the temporary table back to Contracts to get the other columns you need. Again, I'm assuming that these 9 columns would be less than half the width of each Contracts row - if there aren't many other columns, this is unlikely to make a big difference.

• Mike S: 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.)