title: SQL Alternatives to Slow UDF
body: <p>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.  </p>
  
  <p>I've read (<a href="https://stackoverflow.com/questions/510743/why-is-a-udf-so-much-slower-than-a-subquery">Why is a UDF so much slower than a subquery?</a>) but am struggling with how to fix/rewrite.</p>
  
  <p>Per @JasonALong's feedback below,</p>
  
  <p>Execution plan for SELECT statement that completes in 0.3 seconds:  <a href="https://www.brentozar.com/pastetheplan/?id=HJnrqC53Z" rel="nofollow noreferrer">https://www.brentozar.com/pastetheplan/?id=HJnrqC53Z</a> (note the SQL is available on this page).</p>
  
  <p>Code for Function that completes in 3.5 seconds pasted below and Execution plan at this link: <a href="https://www.brentozar.com/pastetheplan/?id=BJZbqR93b" rel="nofollow noreferrer">https://www.brentozar.com/pastetheplan/?id=BJZbqR93b</a></p>
  
  <pre><code>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 &lt; 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 &lt; 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 &lt; 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 &gt; 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 &gt; 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
  </code></pre>