Is there a way to speed up this sql? I am using a linked server. I tried to do a merge and the performance was the same.
--Update Participants by recordModified Server 1 -> Server 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
I am updating over 35 columns, I left them out to simplify the query.
I added this in and it did NOT seem to help
on p2.RecordModified >= (select LastSync from ERSS_DB_Sync)
The bottle neck seems to be the amount of columns that I am updating.
Is there anyway to speed up this update statement?
The local Sql Server doesn't know anything about statistics, number of records, or indexes on the remote/linked Sql Server. As is, it needs to bring over all of the records from the remote server to even do the check on the RecordModified column.
You may be able to improve things by using `OPENQUERY(), to let the linked remote server pre-filter the results before sending them to local server. This will limit network traffic just the relevant records and allow the remote server to use it's indexes as it builds the result set:
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
Even better if you can also limit the columns returned by changing the * to just what you need.