I am having some trouble selecting data from source table to the destination table to find out which data from the source table that has not yet completely integrated to the destination table.
The source table is another DBMS, which in SQL Server we used a linked server to access that DBMS of the source table and the integration is pretty much just straight forward from column to column of the source to destination (No other calculation)
When I execute a select statement like this
SELECT A.*
FROM [ORCLE_DB]..GROUP.TABLEA AS A
WHERE NOT EXISTS (SELECT 1 FROM TABLEA as B WHERE A.ID = B.ID)
It took forever to select the data, and the amount of data is pretty huge, about 20 million rows of data.
Is there any other ways in order to select these rows, that may allow SELECT
execution to be done efficiently and faster? Thank you so much, any ideas and advice will be so much appreciated
You are likely falling foul of a distributed query going 'N+1'. The heuristics are somewhat arcane in the way they penalise network speed. You can verify this using SQL Profiler.
If so, you can fix by: