How can I improve a slow comparison query that have over partition and group by
Since [ExternalTable] is linked to a view on another database server, it probably makes sense to &quot;materialize&quot; the rows in [ExternalTable] into a local temp table, then run the query against that.
So, something like this:
CREATE TABLE #t_combined
(
    SourceTable char(1) NOT NULL
    , foo int NOT NULL
    , bar int NOT NULL
    , [data] int NOT NULL
    , INDEX ix CLUSTERED (foo, bar, [data], SourceTable)
);

INSERT INTO #t_combined WITH (TABLOCKX) (SourceTable, foo, bar, [data])
SELECT '2', foo, bar, [data]
FROM [ExternalTable] et;

INSERT INTO #t_combined WITH (TABLOCKX) (SourceTable, foo, bar, [data])
SELECT '1', foo, bar, [data]
FROM [Table] et;


;WITH CombinedTables AS 
(
    SELECT SourceTable
        , foo
        , bar
        , data
        , ROW_NUMBER() OVER (PARTITION BY foo, bar, data ORDER BY foo, bar, data) Row_Nbr
    FROM #t_combined
    GROUP BY foo
        , bar
        , data
        , SourceTable
)
SELECT DISTINCT * 
FROM CombinedTables 
WHERE CombinedTables.Row_Nbr &lt; 2 
    AND CombinedTables.SourceTable = '2';