Joining table from Linked Server is very slow

Question

I have a production SQL server and a linked server (Azure SQL Database)

I join two tables to do update

Table A - small table A in TempDB that has only 100 rows on SQL Server
Table B - on linked server, is about 90 MB in size and 334,000 rows total

When I run below query

update A
    set A.ColumnA = B.ColumnB
from #Table A
    join [LinkedServer].[DB].[dbo].[Table] B on
         A.ID = B.ID

The update of 100 rows takes about 18 seconds! 1000 or more rows take much more time

I made sure below is true:

Column B.ID is indexed
Collation Compatible setting in Linked Server Options is set to "True"

I even tried to significantly scale up Azure SQL DB (from 20 DTU to 800 DTU), but the speed of query (100 rows update) went from 18 sec to 8 sec which is still not acceptable

What am I missing ? Is there any workarounds in this situation ?

Regards,

@LowlyDBA, I tried "Collation Compatible" setting from second link, and will try that crazy workaround from first link. However, I am wondering if there is a simpler way — Aleksey Vitsko, Apr 03 '18 at 19:11

score 1 · Answer 1 · answered Apr 04 '18 at 11:12

1

Put an index on A.ID

Add a <> so it can avoid taking locks

update A
    set A.ColumnA = B.ColumnB
from #Table A
join [LinkedServer].[DB].[dbo].[Table] B 
  on A.ID = B.ID 
where A.ColumnA <> B.ColumnB or A.ColumnA is null

answered Apr 04 '18 at 11:12

paparazzo

5,043
1
18
32

score 0 · Answer 2 · edited Jun 15 '20 at 09:05

If you capture waits related to this particular query you will see high values for the OLEDB wait type which is common when querying a remote server using linked servers. To my knowledge you cannot avoid that. I have wrote about it here.

You can capture query waits using below script:

DROP TABLE IF EXISTS #before;
 SELECT [wait_type], [waiting_tasks_count], 
 [wait_time_ms], [max_wait_time_ms],
 [signal_wait_time_ms]
 INTO #before
 FROM sys.[dm_db_wait_stats];
-- Execute test query here
DECLARE @Rows INT
 SELECT @Rows = 100
;WITH Q AS
 (
 SELECT A.StateProvince, A.CountryRegion,
 ROW_NUMBER() OVER (PARTITION BY A.CountryRegion ORDER BY A.StateProvince, A.CountryRegion) GrpRow
 FROM SalesLT.Address A
 )
 SELECT TOP(@Rows) Q.*
 FROM Q
 WHERE GrpRow <= 1 + CEILING(@Rows * 1.0 / ( SELECT COUNT(DISTINCT CountryRegion) FROM Q))
-- Finish test query
DROP TABLE IF EXISTS #after;
 SELECT [wait_type], [waiting_tasks_count], [wait_time_ms], [max_wait_time_ms],
 [signal_wait_time_ms]
 INTO #after
 FROM sys.[dm_db_wait_stats];
-- Show accumulated wait time
SELECT [a].[wait_type], ([a].[wait_time_ms] - [b].[wait_time_ms]) AS [wait_time]
 FROM [#after] AS [a]
 INNER JOIN [#before] AS [b] ON
 [a].[wait_type] = [b].[wait_type]
 ORDER BY ([a].[wait_time_ms] - [b].[wait_time_ms]) DESC;

For more ways how to capture specific query waits, please read here.

Look at the query plan, you may also find no indexes are participating on the join.

Can you use SQL Data Sync to keep a copy of the table on the on-premise SQL Server and then create the join with the local copy of the table?

Joining table from Linked Server is very slow

2 Answers2