无论如何加快这个链接的服务器SQL更新？

Question

有没有办法加快这个SQL？我正在使用链接服务器。我试图做一个合并，并且性能是一样的。

--update与会者recordModified服务器1 - >服务器2

update p1 
set p1.[RecordCreated] = p2.RecordCreated 
,p1.[RecordModified] = p2.RecordModified 
,p1.[Email] = p2.Email 
from ERSS_Local.dbo.Participant p1 
join [Computer2\SqlExpress].ERSS_Local.dbo.Participant p2 
on p2.RecordModified >= (select LastSync from ERSS_DB_Sync) 
and p1.ParticipantID = p2.ParticipantID 
and p1.RecordCreated = p2.RecordCreated and p1.RecordModified < p2.RecordModified 

我更新超过35列，我离开他们去简化查询。

我加入这一点，它似乎并没有帮助

on p2.RecordModified >= (select LastSync from ERSS_DB_Sync) 

的瓶颈似乎是我更新的列的数量。

无论如何加快此更新声明？

Answer 1

本地Sql Server不知道远程/链接的Sql Server上的统计信息，记录数或索引的任何内容。因为它需要从远程服务器上带走所有的记录，甚至可以对RecordModified列进行检查。

您可以通过使用OPENQUERY（）来改善事情，让链接的远程服务器在将结果发送到本地服务器之前对结果进行预过滤。这将限制网络流量只是相关记录，并允许远程服务器使用它的指标，因为它建立在结果集：

update p1 
    set p1.[RecordCreated] = p2.RecordCreated 
     ,p1.[RecordModified] = p2.RecordModified 
     ,p1.[Email] = p2.Email 
from ERSS_Local.dbo.Participant p1 
join OPENQUERY([Computer2\SqlExpress], 
    'SELECT * FROM ERSS_Local.dbo.Participant WHERE p2.RecordModified >= ''' + (select LastSync from ERSS_DB_Sync) + '''') p2 
    on p1.ParticipantID = p2.ParticipantID 
    and p1.RecordCreated = p2.RecordCreated 
    and p1.RecordModified < p2.RecordModified 

更妙的是，如果你还可以限制通过改变*返回的列，只是你需要。

Answer 2

--assign a variable prevents some issues with query optimizer 
DECLARE @LastSync DATETIME; 
SELECT @LastSync = LastSync from ERSS_DB_Sync 

update 
    p1 
set 
    p1.[RecordCreated] = p2.RecordCreated 
    ,p1.[RecordModified] = p2.RecordModified 
    ,p1.[Email] = p2.Email 
from 
    ERSS_Local.dbo.Participant p1 
    join (
      SELECT 
       [Computer2\SqlExpress].ERSS_Local.dbo.Participant 
      WHERE 
       RecordModified >= @LastSync 
       -- force the destination engine to filter this set first 
     ) as p2 
    on p1.ParticipantID = p2.ParticipantID 
    and p1.RecordCreated = p2.RecordCreated and p1.RecordModified < p2.RecordModified