Finding and locally deleting deleted records in a million-record table

Question

I'm trying to incrementally load data from a remote server to a local one (using SSIS and linked server). Remote table has 1.7 million of records, increasing every hour. So far, I have been able to load new records and update existing records using their RECID and LASTMODIFIEDDATEANDTIME fields. But when I try to find records which are deleted since last refresh, I face a never-ending operation:

DELETE FROM localdb.dbo.INVENTTRANS WHERE RECID NOT IN (SELECT RECID FROM REMOTESERVER.remotedb.dbo.INVENTTRANS)

I tried running SELECT RECID FROM REMOTESERVER.remotedb.dbo.INVENTTRANS and it loads data in less than 10 seconds, hence there is no network/performance issue. But when I run the above DELETE query, it doesn't finish even after 15 minutes. I tried copying RECIDs to a local table to prevent possible reciprocations between local and remote server, no luck. Can someone guide me to improve performance of such a query?

Answer 1

try this merge statement...

with trg as (
select RECID, LASTMODIFIEDDATEANDTIME, column1,column2 FROM targettable
),
src as (
select * from openquery(linked_server_name,'select * from soucetable')
)
MERGE trg AS TARGET
USING src AS SOURCE
ON TARGET.RECID = SOURCE.RECID
WHEN MATCHED AND SOURCE.LASTMODIFIEDDATEANDTIME > TARGET.LASTMODIFIEDDATEANDTIME
   THEN UPDATE
       set TARGET.LASTMODIFIEDDATEANDTIME = SOURCE.LASTMODIFIEDDATEANDTIME
       TARGET.column1 = SOURCE.column1
       TARGET.column2 = SOURCE.column2
WHEN NOT MATCHED BY TARGET
   THEN INSERT
     (RECID, LASTMODIFIEDDATEANDTIME, column1, column2) VALUES (SOURCE.RECID, SOURCE.LASTMODIFIEDDATEANDTIME, SOURCE.column1, SOURCE.column2) 
WHEN NOT MATCHED BY SOURCE
   THEN DELETE 
OUTPUT $action, 
DELETED.RECID AS DeletedTargetRECID, 
INSERTED.RECID AS INSERTEDRECID
;