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