0

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
Mike S
  • 157
  • 3
  • 13
  • 2
    This is too vague, your UDFs could be scalar functions or table valued, they could be single-statement or multi-statement, you could be using them as correlated sub-queries or joining against them. The list goes on and on. You need to give an actual example relevant to your particular situation, perhaps after reading this? https://stackoverflow.com/help/mcve *(It also wouldn't be a bad idea to get the execution plans for the two versions of your code to see how they differ, which might help you find out why they differ)* – MatBailie Oct 08 '17 at 00:56
  • For any readers following along fyi I tried adding Option(Recompile) https://stackoverflow.com/questions/20864934/option-recompile-is-always-faster-why but this did not make a difference. Also attempted was to create the SQL query in .Net (webserver) and run that directly but this proved even slower than using a function or stored procedure. – Mike S Oct 10 '17 at 20:38
  • 1
    What happens if you run it with the UDTs, but not wrapped in a function? That will help you isolate the change that caused the performance issues (adding UDTs or wrapping in a function). I suspect that is the UDTs that are the problem. If so try rewriting the query to join in the games instead of using `IN ()`, and or applying indexes and stats to the UDTs. – MatBailie Oct 11 '17 at 05:44

2 Answers2

4

Both scalar functions and muli-statement table valued functions (mTVF) are, as to mentioned in your question, "black boxes" to the optimizer...

So, I'm taking the question to be, "why is this so bad?". The answer is, In order to come up with an good plan that executes as efficiently as possible, it needs to know certain details about the specific requirements as well as information about the tables it will be pulling data from (which is why outdated statics can seriously impact performance as well). So... When you use a scalar function or mTVF, the optimizer can't assess all of the requirements the way it can with inline code. It's response is to simply assume that the function will only execute a single time and makes the plan according to that assumption.

Since the assumptions are wrong, the wrong plan gets generated and you end up with horrible performance.

The solution is to rewrite the offending functions... The key is to to #1, make sure you rewrite them as "inline table valued functions" (iTVF). These are the ONLY functions that the optimizer will see as if their code were typed directly into the outer query (thus the term "inline"). If you're not familiar with iTVFs, they have 2 requirements... 1 they have to be table functions (for whatever reason , MS STILL hasn't a made scalar version available)... and ... 2 this is the biggie... The function body MUST be a single statement.

So what if you don't need a table valued function, you need a scalar function? Well there's nothing that says that a multi-valued function can't return a single (scalar) value... Which is why those who are aware of the situation with code ALL of their functions as iTVFs.

The good thing is that there is no shortage of information on the web about creating "inline scalar functions", using table functions coded to return scalar values on the web.

Hope this Helps...

Jason A. Long
  • 4,382
  • 1
  • 12
  • 17
  • @JasonALong Thanks, your info has me pointed toward better research. However as relates to the bottom portion of your comment, my issue is that I want to return a table rather than a scalar. (Also that I don't think it possible to return the data sought without JOINS to subqueries . . .) – Mike S Oct 08 '17 at 21:20
  • 1
    It's difficult to say without having any specifics... but.. Just so there's no confusion... When I said, "as a single statement", that shouldn't be interpreted as "simple statement"...CTEs, derived table and sub-queries are all perfectly acceptable. It simply means you can't do things like add control flow using IF blocks or declare & set internal variables. Here's an easy way to think about it... SQL statements are supposed to be terminated with semicolons... If you're able to add more than 1 semicolon in the body, SQL Server will consider it multi-valued. – Jason A. Long Oct 08 '17 at 22:30
  • 1
    If want to verify that SQL Server considers a particular function "inline", simply query the sys.objects table. SELECT * FROM sys.objects o WHERE o.name = N'tfn_SomeFunction'; ... or ... you can look all the uds at once... SELECT * FROM sys.objects o WHERE o.type IN ('FN', 'FS', 'IF', 'TF'); – Jason A. Long Oct 08 '17 at 22:43
  • Not to mention functions can be procedural (rather than set based) which means they just can't be mixed in with set based SQL – Nick.Mc Oct 08 '17 at 23:07
  • Per SELECT * FROM sys.objects o WHERE o.type IN ('FN', 'FS', 'IF', 'TF'); the function is a "SQL_INLINE_TABLE_VALUED_FUNCTION" – Mike S Oct 10 '17 at 21:26
  • 1
    That's a good thing. That means it's not a black box. That, however, doesn't mean that it's doing the rest of the query any favors. This is when you start digging into the execution pan and finding out what's going on. If you aren't comfortable deciphering execution plans, There are always people on any SQL forum that love digging in to execution plans. Also... both BrentOzar.com and SentryOne will allow you to upload your plans and people review it for you. Just make sure you capture an "actual plan". If you post an estimated plan, you'll be asked for the actual anyway. – Jason A. Long Oct 10 '17 at 21:51
  • Awesome, that (plan review) is exactly what I need (I've spent the last three hours learning about them but still not comfy). Fwiw if anyone wants to take a look I will try posting at BrentOzar.com and SentryOne or can send them to you directly (ideally would love to send and then skype as you walk through them so I can learn from you how to read/tune.) – Mike S Oct 10 '17 at 23:29
  • 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 Thanks in advance! – Mike S Oct 10 '17 at 23:43
  • 1
    The optimizer is expecting 4.8 million rows and only getting back 64... At least one of you tables is a heap (no clustered index) because I see a RID lookup... 2 key lookup 22 table scans, 9 sort operations... and a hash join to top it off... I keep wanting to say, "The 1st this that strikes me"... But thinks keep striking me... This is doing some sort of repeated iteration so I'm thinking recursion or cross joins. Any chance you can add the function code to the question? – Jason A. Long Oct 11 '17 at 04:08
  • Added function code to original question. Thanks (again). – Mike S Oct 11 '17 at 05:56
  • does using subselect query still considered a single statement? – Keith Rivera Sep 23 '22 at 16:37
0

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.)

Mike S
  • 157
  • 3
  • 13