Function call as INNER JOIN causes slow RID lookup
Looking at the query plan, there are massive cardinality issues indicated by the large differences between estimated rows and actual rows.
My gut feeling is that there is nothing wrong with the query itself, but perhaps the way the statistics and/or indexes are set up on the tables. The statistics are updated daily so I know it's not a case of outdated stats.
Would appreciate any help anyone can give on this.
Edit: Based on help and advice given so far, I've attached non-anonymised execution plans for the query before and after the change that speeds it up. Here's the one with the function call as part of an INNER JOIN. Here's the one with the function call in the WHERE clause instead. The latter runs much quicker and yields the same query results. It appears that removing the RID lookup that Sujai Karnam and SQLkiwi mentioned is problematic improves the performance massively.
But, remove the table valued function.
A)dump the results of that into a temp table and use that in the query if using 100% of its output
B) find another way to to its activity if not.
The problem is TVF always estimate at one row, here it is returning 564 rows.
Table2 is also giving a relatively poor estimate, the error of which is being magnified up the tree.
HTH
The non-anonymised query analysis (see here) shows an horrendous number of rows being outputted, but it takes a long time in SSMS before it even starts outputting the rows to the 'Results' window. What I found is that the function call simply returns a single-column list and the purpose of the JOIN is to filter the results on that list. It occurred to me that this should actually go in the WHERE clause.
So, the resultant query is
SELECT * FROM F_V_IrZeroDelta (NOLOCK) IRD INNER JOIN dbo.D_Trade (NOLOCK) T ON T.Trade_PK = IRD.Trade_FK_LK WHERE T.TradeTypeName_LK IN (SELECT TradeTypeName_LK FROM feeds.fn_GetTradeTypes(NULL))
This is much quicker although not perfect – it still takes several minutes, but that's better than over an hour.
2.The Statistics of the table is "F_V_IrZeroDelta" is either outdated or is based on a small sampling
1. Extract the function output to a temp table and joined that temp table in the query.
2. Or, make it a sub query say, INNER JOIN (SELECT /DISTINCT/ TradeTypeName FROM feeds.fn_GetTradeTypes(@TradeTypeFilter) ) TT ON TT.TradeTypeName = T.TradeTypeName_LK
What is the function feeds.fn_GetTradeType doing ?
Does that run fast in isolation ?
Cache the output to a temp table and then join , is that better ?
Second the result set is massive , plan explorer states it 268,998mb , erm 268gb.
Think how long that takes to transfer ?!
Try running the query again in SSMS using the "Discard results after execution" option on, SSMS is really slow at displaying data