0

I have to call a stored procedure on server B from server A using open query via dynamic SQL, and insert the results into a temp table created on server A. I noticed in the execution plan, it's using a Remote Scan showplan operator. I know when possible the Remote Query showplan operator is more efficient, but I'm not sure if it's possible in this case? (The remote stored procedure returns about 1.5 million rows.)

This is my query on local server A:

CREATE TABLE #FinalResults (Field1 INT, Field2 VARCHAR(100))
DECLARE @DynamicSQL NVARCHAR(MAX) = ''

SELECT @DynamicSQL = @DynamicSQL + 
'
SELECT Field1, Field2
FROM OPENQUERY(MyRemoteServerB, ''EXEC sp_MyRemoteStoredProcedure'')
'

INSERT INTO #FinalResults
EXEC sp_ExecuteSQL @DynamicSQL;

This is my query in my remote procedure sp_MyRemoteStoredProcedure on my remote server B:

CREATE TABLE #Results (Field1 INT, Field2 VARCHAR(100))

INSERT INTO #Results
SELECT Field1, Field2
FROM Database1.dbo.Table1
WHERE DateField >= 'Date1'
    AND DateField < 'Date2'

UNION

SELECT Field1, Field2
FROM Database2.dbo.Table1
WHERE DateField >= 'Date1'
    AND DateField < 'Date2'

SELECT Field1, Field2
FROM #Results

Relevant execution plan operators: Execution Plan

1

I noticed in the execution plan, it's using a Remote Scan showplan operator. I know when possible the Remote Query showplan operator is more efficient...

There's nothing inherently inefficient about the Remote Scan operator. It is used, for example, with BULK INSERT, which is extremely efficient. The performance of the Remote Scan depends on what lies behind it, in this case a linked server, various drivers, networking etc.

A Remote Query operator can be more efficient, for example if a predicate or aggregation operation can be pushed to the remote server, rather than retrieving all rows and doing the work locally.

...but I'm not sure if it's possible in this case?

There is no choice except to use Remote Scan to read data from a T-SQL remote stored procedure call.

1
  • Thanks, I was figuring so, because essentially my stored procedure returns all the records I need, there's no more filtering at that point so to scan them makes sense to me. I actually figured out the real performance bottleneck which was completely unrelated to this. – J.D. Mar 13 '20 at 12:40

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.