0

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

  • Difference between SELECT only and INSERT...SELECT is most likely the transaction binding the two systems together in the latter case. You can try changing the linked server property of remote proc transaction promotion / "Enable Promotion of Distributed Transaction for RPC" to false (unless it's already false). you can also try putting that select into a stored proc on the remote system and doing an INSERT...EXEC. Either way, cross-system DML is very prone to these issues. – Solomon Rutzky Feb 3 at 18:44
1

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]%').

|improve this answer|||||
  • Thanks - Query plans have now been added to my question. Adding a view to the Remote server sounds like a good idea - I'll give that a try. Would be interested to see if I can do it without having to alter the remote DB too, though! – Chris Roberts Feb 3 at 12:04
  • Thanks! Must admit - I didn't even spot the warning in the execution plan! I'll try and resolve the type mis-match and see how it goes... – Chris Roberts Feb 3 at 16:04
1

In the plans you posted we can see it's transferring more records (more network I/O), and it's also converting both the arguments you deliver to it, as well as converting the output upon inserting.

By the looks of it the remote DB has it's field as a N(VAR)CHAR instead of (VAR)CHAR. So when you're quering it using VARCHAR expression, it first has to convert these and also COLLATE them to the right character set.

2 of the 3 tables/sources hit are running somewhat fine. But one takes up nearly all the time:

<RunTimeCountersPerThread Thread="0" ActualRebinds="4787" ActualRewinds="0" 
ActualRows="2228" Batches="0" ActualEndOfScans="2559" ActualExecutions="4787" 
ActualExecutionMode="Row" ActualElapsedms="49195" ActualCPUms="48521" ActualScans="0" 
ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" 
ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0"/>
</RunTimeInformation>
<RemoteQuery RemoteSource="NET2-1\NET2" RemoteQuery="SELECT "Expr1018" FROM 
(SELECT "Col1036",MAX("Col1031") "Expr1018" FROM (SELECT 
"Tbl1017"."Address" "Col1024", "Tbl1017"."SubAddr" "Col1025", "Tbl1017"."Name""Col1026",
"Tbl1017"."PeripheralID" "Col1023","Tbl1017"."Type" "Col1027" FROM "net2"."dbo"."Peripherals" "Tbl1017" 
WHERE "Tbl1017"."Name" like N'%(In)%' COLLATE Latin1_General_CI_AS 
AND (NOT "Tbl1017"."Name" like N'%(Internal)%' COLLATE Latin1_General_CI_AS 
AND NOT "Tbl1017"."Name" like N'%Cats%' COLLATE Latin1_General_CI_AS 
AND NOT "Tbl1017"."Name" like N'%Dingle%' COLLATE Latin1_General_CI_AS OR "Tbl1017"."Name" like N'%(Internal)%' COLLATE Latin1_General_CI_AS 
AND ("Tbl1017"."Name" like N'%Cats%' COLLATE Latin1_General_CI_AS OR "Tbl1017"."Name" like N'%Dingle%' COLLATE Latin1_General_CI_AS))) Qry1028,
(SELECT "Tbl1015"."EventTime" "Col1031", "Tbl1015"."EventType" "Col1032", "Tbl1015"."Address" "Col1034", "Tbl1015"."SubAddr" "Col1035","Tbl1015"."UserID" "Col1036","Tbl1015"."CardNo" "Col1037", 
  "Tbl1015"."EventSubType" "Col1033","Tbl1015"."EventID" "Col1030" 
  FROM "Net2Events"."dbo"."Events" "Tbl1015" WHERE "Tbl1015"."EventType"=(20) 
  AND "Tbl1015"."EventTime"&lt;?) Qry1038 WHERE "Col1024"="Col1034" AND "Col1025"="Col1035" GROUP BY "Col1036") 
Qry1039 WHERE "Col1036"=?"/>

In the above query you can see a ton of like compares with collation. I suspect this is the slowdown. We can see it has a ~50 seconds execution time of which the most is CPU (which would suggest it's converting and comparing).

I would try to see if making both your temporary table use NVARCHARS as well as specifying your where arguments as N'' helps out. If not you can always try and use Openrowset and force execution entirely on remote side.

Basically in this case, you have two slow down factors. The 3 seperate executions (more data being transferred), as well as this last query from the execution plan which takes 50 seconds to execute on its own.

My apologies for the XML formatting, i already made it a lot less horrible than it is in the execution plan source.

Openrowset documentation

|improve this answer|||||
  • Thanks - I'll check the temporary table. It's a pretty nasty query, really - I was just surprised at the difference in plan between a straight SELECT and an INSERT / SELECT. I'll get the data types matching in the temporary table and let you know how I get on! – Chris Roberts Feb 3 at 16:06

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.