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?
RECIDs over locally but still executed the query against the Linked Server?