none
Help,how to avoid "remote scan" operator at the remote machine?

    Question

  • I have two machine,
    one is local,
    the other is the remote machine,it's IP:192.168.1.21

    It will always execute the table scan at the remote machine when I run the following script at the local machine.

    script 1

    UPDATE syn
     SET
      SOD_CONTACTPERSONID = crr.SOD_CONTACTPERSONID,
      SOD_CHI_CONTACTPERSONID=crr.SOD_CHI_CONTACTPERSONID,
      SOD_PROCESS_FLAG=crr.SOD_PROCESS_FLAG,
      SOD_PROCESS_REASON=crr.SOD_PROCESS_REASON
     FROM [192.168.1.21].[SYN_DATA].[dbo].[SYN_OFFLINE_DATA] syn
     INNER JOIN [dbo].[SYN_OFFLINE_DATA_Crr] crr ON syn.SOD_ID=crr.SOD_ID
     WHERE crr.SOD_SOL_BATCH_ID='3413' AND crr.SOD_PROCESS_FLAG = 'Y'

    the following are details information for the two table.
    1,both the table syn and table crr have same scheme and same data.
    2,both table have 2,000 thousand rows
    3,both table has a clustered index on SOD_ID
    4,both table has a Unclustered index on SOD_SOL_BATCH_ID
    5 the result of the filter condition " WHERE crr.SOD_SOL_BATCH_ID='3413' AND crr.SOD_PROCESS_FLAG= 'Y'" is 50 rows


    because when I run the above UPDATE script,it will consumed all of the  remote machine's memory,
    So I'm sure the remote machine do the "table scan" operator,So how to avoid  "table scan"operation on the remote machine?

    Also the graphic estimated executive plan show that the "remote sacn" operator occupy "100%"

    BTW ,When I run the following two scripts,both of them will return the result very quickly.

    script 2

    UPDATE syn
     SET
      SOD_CONTACTPERSONID = crr.SOD_CONTACTPERSONID,
      SOD_CHI_CONTACTPERSONID=crr.SOD_CHI_CONTACTPERSONID,
      SOD_PROCESS_FLAG=crr.SOD_PROCESS_FLAG,
      SOD_PROCESS_REASON=crr.SOD_PROCESS_REASON
     FROM [dbo].[SYN_OFFLINE_DATA] syn
     INNER JOIN [dbo].[SYN_OFFLINE_DATA_Crr] crr ON syn.SOD_ID=crr.SOD_ID
     WHERE crr.SOD_SOL_BATCH_ID='3413' AND crr.SOD_PROCESS_FLAG = 'Y'
    By look up the query plan,it effectively use the clustered index seek instead of table scan.


    script 3, 
    SELECT *
    FROM [192.168.1.21].[SYN_DATA].[dbo].[SYN_OFFLINE_DATA] syn
    INNER JOIN [dbo].[SYN_OFFLINE_DATA_Crr] crr ON syn.SOD_ID=crr.SOD_ID
    WHERE crr.SOD_SOL_BATCH_ID='3413' AND crr.SOD_PROCESS_FLAG = 'Y'

    the script 1 and script 2 have the same content except for the script 1 's syn table at the remote machine, but they cause a completely different execute plan!why ?how the avoid that?

    thank you.





    • Edited by Jacky_shen Wednesday, November 30, 2011 2:14 PM
    • Moved by Tom PhillipsModerator Wednesday, November 30, 2011 2:59 PM TSQL question (From:SQL Server Database Engine)
    Wednesday, November 30, 2011 2:01 PM

Answers

  • checkout this link:

    http://sqlblog.com/blogs/linchi_shea/archive/2009/07/21/performance-impact-linked-server-security-configuration-and-how-it-can-hurt-you.aspx


    -- Yaniv www.sqlserverutilities.com http://blogs.microsoft.co.il/blogs/yaniv_etrogi
    • Marked as answer by Jacky_shen Thursday, December 01, 2011 2:17 AM
    Wednesday, November 30, 2011 3:03 PM
  • I cannot fully describe all the interior workings of the SQL Server optimizer, but I know that joins across servers are slow and (as you noted) the update is doing a roundtrip back to the server.  I can tell you, do not be surprised when that is slow.  (And be pleased when it works well.)

    If you can create a stored procedure on the remote server, the OPENQUERY would merely execute that stored procedure.   The code internal to the stored procedure would join back to the local server to get the few rows that it needs.

    Added:  Note that the local table has specific values being searched for while the remote table is joining.   By moving the code to the remote server, it can query back to the local server for only the rows matching the parameters and bring only those rows remotely to finish the join.

    Does that make sense to you?

    RLF


    • Edited by SQLWorkMVP Wednesday, November 30, 2011 4:43 PM Added: explanation
    • Marked as answer by Jacky_shen Thursday, December 01, 2011 2:17 AM
    Wednesday, November 30, 2011 3:52 PM

All replies

  • When doing a join across a linked server, the local optimizer does not have the information to make a good plan for a table on another server.   Although cross server joins can sometimes be useful, they are also expensive.  (As you see.)

    So, script 1 has an expensive plan.  But it does work.  However, please note that you are (1) pulling the data across to your local server from  [192.168.1.21], (2) joining it locally, then (3) sending that data back to the remote server to update it there.

    Script 2 is running on the same server and database, so the optimizer has a full view of the statistics, etc, needed for a good plan.  Also, all the data movement is within one server, not being pulled from a remote server.

    Script 3 is doing a select and apparently the streaming works better in that case.

    It might work better if you create a stored procedure on the remote server that uses a link back to your local server to get the [SYN_OFFLINE_DATA_Crr]  date which seems to be fairly limited.  Then it will do the update locally on [192.168.1.21].

    FWIW,
    RLF


    • Edited by SQLWorkMVP Wednesday, November 30, 2011 2:27 PM
    • Proposed as answer by Naomi NModerator Thursday, December 01, 2011 1:11 AM
    Wednesday, November 30, 2011 2:26 PM
  • Thank you Russell ,

    but by comparing Script 3 to script 1,I think the Script 3 also do the step1(pulling the data across to your local server from [192.168.1.21], ) and step2( joining it locally) just like Script 1 does, why Script 3 can pull the data just it wanted,but script 1 pull the all entire table ? do you means the step3(sending that data back to the remote server ) consume the most of the time and resources ? because the Script 3 doesn't need to do the step3, So Script 3 is very quick,but Script 1 is very slow.

    also,because I must invoke the query on local , So if I create a stored procedure on the remote server ,how to invoke the stored procedure on the remote server ? it seems it is forbid to invoke the remote server' stored procedure  on local machine directly .






    • Edited by Jacky_shen Wednesday, November 30, 2011 2:47 PM
    Wednesday, November 30, 2011 2:44 PM
  • Try using OPENQUERY

    http://msdn.microsoft.com/en-us/library/ms188427.aspx


    -- Yaniv www.sqlserverutilities.com http://blogs.microsoft.co.il/blogs/yaniv_etrogi
    Wednesday, November 30, 2011 2:57 PM
  • checkout this link:

    http://sqlblog.com/blogs/linchi_shea/archive/2009/07/21/performance-impact-linked-server-security-configuration-and-how-it-can-hurt-you.aspx


    -- Yaniv www.sqlserverutilities.com http://blogs.microsoft.co.il/blogs/yaniv_etrogi
    • Marked as answer by Jacky_shen Thursday, December 01, 2011 2:17 AM
    Wednesday, November 30, 2011 3:03 PM
  • Try using OPENQUERY

    http://msdn.microsoft.com/en-us/library/ms188427.aspx


    -- Yaniv www.sqlserverutilities.com http://blogs.microsoft.co.il/blogs/yaniv_etrogi


    thank you very much Yaniv,

     but how to write inner join operation by using the OPENQUERY?


    • Edited by Jacky_shen Wednesday, November 30, 2011 3:34 PM
    Wednesday, November 30, 2011 3:30 PM
  • checkout this link:

    http://sqlblog.com/blogs/linchi_shea/archive/2009/07/21/performance-impact-linked-server-security-configuration-and-how-it-can-hurt-you.aspx


    -- Yaniv www.sqlserverutilities.com http://blogs.microsoft.co.il/blogs/yaniv_etrogi


    Hi Yaniv,

    I use the 192.168.1.21's sa account as the linked server's  login account, and also the script 3's performance is very good,but poor performance just for the script 1

    • Edited by Jacky_shen Wednesday, November 30, 2011 3:33 PM
    Wednesday, November 30, 2011 3:32 PM
  • I cannot fully describe all the interior workings of the SQL Server optimizer, but I know that joins across servers are slow and (as you noted) the update is doing a roundtrip back to the server.  I can tell you, do not be surprised when that is slow.  (And be pleased when it works well.)

    If you can create a stored procedure on the remote server, the OPENQUERY would merely execute that stored procedure.   The code internal to the stored procedure would join back to the local server to get the few rows that it needs.

    Added:  Note that the local table has specific values being searched for while the remote table is joining.   By moving the code to the remote server, it can query back to the local server for only the rows matching the parameters and bring only those rows remotely to finish the join.

    Does that make sense to you?

    RLF


    • Edited by SQLWorkMVP Wednesday, November 30, 2011 4:43 PM Added: explanation
    • Marked as answer by Jacky_shen Thursday, December 01, 2011 2:17 AM
    Wednesday, November 30, 2011 3:52 PM
  • thank you Russell,your description is very make sense :-)
    Thursday, December 01, 2011 12:56 AM
  • I cannot fully describe all the interior workings of the SQL Server optimizer, but I know that joins across servers are slow and (as you noted) the update is doing a roundtrip back to the server.  I can tell you, do not be surprised when that is slow.  (And be pleased when it works well.)

    If you can create a stored procedure on the remote server, the OPENQUERY would merely execute that stored procedure.   The code internal to the stored procedure would join back to the local server to get the few rows that it needs.

    Added:  Note that the local table has specific values being searched for while the remote table is joining.   By moving the code to the remote server, it can query back to the local server for only the rows matching the parameters and bring only those rows remotely to finish the join.

    Does that make sense to you?

    RLF



    I use your method(create a stored procedure on the remote server ,and invoke it on local ),and made a great performance improve.

    thank you.



    • Edited by Jacky_shen Thursday, December 01, 2011 2:21 AM
    Thursday, December 01, 2011 2:19 AM