How can I improve a slow comparison query that have over partition and group by

Question

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

Answer 1

Since [ExternalTable] is linked to a view on another database server, it probably makes sense to "materialize" 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 < 2 
    AND CombinedTables.SourceTable = '2';

Answer 2

The stated purpose of your query is to return new and updated rows from [ExternalTable]. In that case you could use EXCEPT syntax. You might also want to use temp table for [ExternalTable], because the estimates for it seem totaly wrong in the execution plan you provided. The query would look like this:

SELECT foo, bar, data
INTO #t
FROM [ExternalTable]

SELECT foo, bar, data
FROM #t
EXCEPT
SELECT foo, bar, data
FROM [Table]