无论如何可以加快此链接服务器SQL更新的速度

Question

有没有办法加速这个sql？我正在使用链接服务器。我尝试进行合并，性能相同。

--

按记录更新参与者修改的服务器 1 ->服务器 2

update p1 
set  p1.[RecordCreated] = p2.RecordCreated
,p1.[RecordModified] = p2.RecordModified
,p1.[Email] = p2.Email
from ERSS_Local.dbo.Participant p1
join [Computer2SqlExpress].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([Computer2SqlExpress], 
    '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 
                [Computer2SqlExpress].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