2

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%')

enter image description here

Query plan

  • The remote query is running 43,000 times. Perhaps you should consider materializing that table locally prior to running the query. At least you could see if that makes a substantial difference in a test environment. – Max Vernon Feb 13 '17 at 20:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.