How can I improve a slow comparison query that have over partition and group by I have a query (see below) that is working just fine, when there aren't that many rows, around 5k.
However, if I run the same query on a table with around 80k rows the query takes between 30 and 60 seconds to complete.  Don't ask me why it fluctuates so much...
I need help or tips on how I can improve this query so it runs faster, preferable a lot faster, but still retains the same functionality.
The [ExternalTable] in the query is a external table that are pointing on a view that i have little control over (it is a view residing on a linked server). I have full control over the [Table].
The query below is shortened a little for readability; we have 7 columns that we check against in the real query.
The purpose of the query is to find new or updated rows in the [ExternalTable] compared with the [Table] and then output the new or changed rows. This is working just fine on smaller amounts of rows but it takes a very long time when the number of rows increase.
If it makes a difference, this runs on a SQL Server in Azure.
WITH CombinedTables AS 
(SELECT SourceTable,foo, bar, data, ROW_NUMBER() 
OVER (PARTITION BY foo, bar, data
ORDER BY foo, bar, data) Row_Nbr
FROM (SELECT '2' AS SourceTable, foo, bar, data FROM [ExternalTable]      
         UNION ALL 
         SELECT '1' AS SourceTable, foo, bar, data FROM [Table] WITH (NOLOCK)
) AS CombinedTables GROUP BY foo, bar, data, SourceTable
) SELECT DISTINCT * FROM CombinedTables WHERE CombinedTables.Row_Nbr &lt; 2 AND CombinedTables.SourceTable = '2' 

What can I do to improve the performance? Add indexes? Make it into a stored procedure?  Rewrite it somehow?
As you might have gathered, I'm a bit too inexperienced with SQL Server to solve this myself and I need some help from you smart people!
edit: added a sql fiddle
http://sqlfiddle.com/#!18/86075/1
edit 2: added actual executionplan image: http://prntscr.com/uuys04
edit 3: It might be that the query is fine and it's Azure that is limiting the query executiontime, I posted a new question about this: Tweak Azure Data IO operations to lower the DTU cost
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';