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