0

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?

5
  • Queries across servers are much slower than local ones. Pull the current RECID data into a local staging or temporary table then separately act on it. Carefully sequence or add conditions to things so there is no chance a very new record gets deleted in error.
    – Brian
    Commented Jun 11, 2022 at 15:25
  • Yea so the issue here is likely the Linked Server. Is this database for Microsoft's Dynamics Axapta ERP system by any chance? If so, if I recall correctly, RecID is the primary key and / or clustered index? If so, then re-writing your query in a more relational way may prevent you from getting a Remote Scan operation and instead result in a more performant Remote Query operation.
    – J.D.
    Commented Jun 12, 2022 at 0:43
  • @Brian as I have written in my post, I tried copying all RECIDs to a local table, but his didn't change anything. The problem is number of records, not cross-server query.
    – Mohammad
    Commented Jun 12, 2022 at 6:05
  • @J.D. as I replied to Brian, even copying data locally doesn't change anything. My data source is Microsoft Dynamics 365 F&O (AKA AX 365). I'm trying to find a rational way for re-writing this query, but I already know that I should prevent remove scanning, hence tested copying data to local table. This should prevent any problem rising from PK/clustered index status as my destination table neither has PK not index (because I don't need such things).
    – Mohammad
    Commented Jun 12, 2022 at 6:09
  • You copied all the data locally and ran your query with no Linked Server involved and it's still slow? Or you only brought the RECIDs over locally but still executed the query against the Linked Server?
    – J.D.
    Commented Jun 12, 2022 at 12:20

1 Answer 1

0

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
;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.