Sql-server – Fix for slow SQL_INLINE_TABLE_VALUED_FUNCTION

functionssql serversubquery

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

Best Answer

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.

Related Question