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 ?