I have an inline TVF that take much longer to run than should be necessary. The issue looks to be the Linked Server reference.
The Linked Server is SQL Server 2012. The server with the TVF is SQL 2008R2.
The Remote Query Cost is 715%! How can I improve the performance on this?
ALTER FUNCTION [Dashboard.Trades.Facade].[TESTClientData] ( @FundID nvarchar(4), @StartDate datetime, @EndDate datetime ) RETURNS TABLE AS RETURN SELECT DISTINCT TradeID, TradeAction, CASE WHEN TradeAction = 'Delete' THEN 'OK' WHEN TradeAction = 'Cancel' THEN 'OK' ELSE 'Not OK' END AS 'Status' FROM dbo.TradeData WHERE (Fund = @FundID) OR (Fund COLLATE DATABASE_DEFAULT IN (SELECT DISTINCT me.Value1 COLLATE DATABASE_DEFAULT AS Fund FROM [MyLinkedServer].[DB].[dbo].[MappingElement] AS me INNER JOIN [MyLinkedServer].[DB].[dbo].[SourceMapping] AS sm ON me.MappingID = sm.MappingID WHERE (me.ValidValue = @FundID) AND (sm.Name = 'FundTranslations'))) AND (TradeFileDate >= @StartDate) AND (TradeFileDate <= @EndDate) AND (TradeAction != 'Delete' AND TradeAction != 'Cancel') AND (TradeID NOT LIKE '%Reset%')