How to limit the rows on remote/linked server

Question

I have a linked server that I have to fetch data from. I'm joining on a table that I expect very few rows from. The query is below, and seems to be returning all of the rows to the original server to do the sort there.

I'm looking for a way to tell the query to filter on the target machine, with a query hint or something else.

Query

INSERT INTO #DealerHierarchy(DealerId, Level)
SELECT cd.ParentId, cd.Level
FROM [dbo].[AssignedDealer] ad 
JOIN [nlsdb].[nls].[dbo].[vw_parentDealers] cd ON cd.RootId = ad.DealerId
WHERE ad.UserId = @userId 
AND ad.IsActive = 1
AND (@DealerId IS NULL OR ad.DealerId = @DealerId)

When I add the following line, it seems to change and only send back the needed rows

and cd.RootId = 72311

I have tried moving out the local query into a separate temp table, and then select from the view WHERE DealerId IN (select from temp table) but it still runs slowly. Adding the REMOTE hint in the JOIN also does nothing.

Query plan: https://www.brentozar.com/pastetheplan/?id=r1iazaaFZ

Slow code executed on linked server

declare @p1 int
set @p1=7
exec sp_prepexec @p1 output,N'@P1 numeric(10)',N'SELECT "Tbl1007"."ParentId" "Col1010","Tbl1007"."Level" "Col1011" FROM "nls"."dbo"."vw_parentDealers" "Tbl1007" WHERE @P1="Tbl1007"."RootId"',72311
select @p1

Fast code executed on linked server

declare @p1 int
set @p1=10
exec sp_prepexec @p1 output,NULL,N'SELECT "Tbl1007"."ParentId" "Col1010","Tbl1007"."Level" "Col1011" FROM "nls"."dbo"."vw_parentDealers" "Tbl1007" WHERE "Tbl1007"."RootId"=(72311.)'
select @p1

Answer 1

You can force a specific query to be run on the remote database by using OPENQUERY. OPENQUERY doesn't accept a parameter, so you can make it dynamic by further wrapping it in EXEC.

Example

DECLARE @SearchString NVARCHAR = ...
DECLARE @OpenQueryString NVARCHAR = 'SELECT * FROM OPENQUERY(remotedb, ''' + @SearchString + ''')'
EXEC (@OpenQueryString)