0

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

0
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';
3
  • I tried your query but im sorry to say that it took even longer to run. between 90s and 110s Would it help with the actual exekution plan?
    – merger
    Oct 7 '20 at 18:51
  • image of the exekution plan prntscr.com/uuyidn
    – merger
    Oct 7 '20 at 18:56
  • Im starting to think that it might not be the actual query that is the problem, but something with the SQL server. Im attaching a screenshot of the insert from the external table into the #temptable, and it take 17 seconds for around 80k records! prntscr.com/uv8e4c
    – merger
    Oct 8 '20 at 6:42
1

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]

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.