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
Improve this question
3
  • 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? 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) Commented Oct 8, 2017 at 0:56
  • For any readers following along fyi I tried adding Option(Recompile) stackoverflow.com/questions/20864934/… 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. Commented Oct 10, 2017 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. Commented Oct 11, 2017 at 5:44

2 Answers 2

Reset to default
5

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

Improve this answer
Sign up to request clarification or add additional context in comments.

11 Comments

@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 . . .)
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.
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');
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.
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?
|
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.)

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.