SQL Server - How to improve/optimize remote scan on a linked server
Based on my personal experiences, you will get far better performance by transferring data between the two servers and performing the update on the target server.

After watching one of these at a more detailed level (i.e. amount of data transferred between the servers), I determined that at least 90% of the remote table is pulled over to the local server before comparisons are run. This may be improved by indexing your temp tables correctly, but no guarantees.

The reason is simple: The local server doesn't understand the distribution of the remote server. It doesn't know the file structure, indexing, or any such information, so it assumes that it will have to do a table scan to identify what needs to be changed (at least this is my assumption based on experience.) Manually transferring the data to the server to be updated, then running a process there to do the update is the only way I achieved reasonable increases in efficiency.

It is awkward, and I hope someone has a better answer for you, but there you have it - my personal success story for optimizing these types of cross-server queries between the cloud and a local server.