Sql-server – How to prevent the SQL Server Query Optimiser splitting a remote query

optimizationsql server

I am using Microsoft SQL Server and I have a query which reads data from a remote server. This query reads data from 3 different tables across two different databases, all of which are on the same remote server.

When I run this query in isolation as a SELECT in SQL Server Management Studio, and inspect the execution plan – I can see that the entire query is being executed as a single remote query and the whole thing takes about 7 seconds to run.

If I attempt to INSERT the results of this SELECT into a temporary table, the execution plan completely changes. It runs 3 separate queries on the remote server, then merges the results locally – and takes nearly a minute to run.

Is there any way I can persuade the Query Optimiser to stick to the first, faster option!?

Edit:
Thanks for the requests for the execution plans – here they are:

SELECT Only: https://www.brentozar.com/pastetheplan/?id=rygMoFHM8

INSERT (using the same SELECT): https://www.brentozar.com/pastetheplan/?id=S1EZhYBGI

Best Answer

Without seeing the query plans we can't answer in detail. There is likely a query hint you can use to encourage the query planner to use the alternate plan.

Another option is to control it from the other side: create a view in the remote end that does the work and select directly from that at the local side.

Update Once Query Plans Presented:

The warnings in the plan are often good clues, that is why they are there. In this case: Type conversion in expression (CONVERT_IMPLICIT(varchar(20),[NET2-1\NET2].[Net2].[dbo].[Users].[Field14_50],0)) may affect "Cardinality Estimate" in query plan choice.

It would appear that a difference in types between the source [NET2-1\NET2].Net2.dbo.Users.Field14_50 and the target #Net2Data.UserID is causing confusion. Without knowing the source type I suspect it is NVARCHAR(<something>). Perhaps you could add an explicit cast in the SELECT clause or change the temporary tables definition to match so there is no need for conversion at all. Also, if the source column in question is an NVARCHAR make the literal in the filtering clause explicitly a multi-byte string (LIKE N'[a-z][a-z][a-z]%' in place of LIKE '[a-z][a-z][a-z]%').

Related Question