I have this task of importing rows from a source table to a destination table while doing some mapping of columns on the way. The rows are identified by a GUID, and only rows not already present should be imported. The job needs to be batched to enable interruption and resumption and to avoid excessive log growth. The tables lives in different DBs on the same server. There could be a few thousand to a few million records.
The best I have managed to come up with is this.
INSERT INTO DST_DB.dbo.dst_table (MyGUID, Col1, Col2, ...) SELECT TheirGUID, ColA, ColB, ... FROM SRC_DB.dbo.src_table AS SRC1 WHERE SRC1.TheirGUID IN ( SELECT TOP 10000 TheirGUID FROM SRC_DB.dbo.src_table AS SRC0 WHERE SRC0.TheirGUID NOT IN ( SELECT MyGUID FROM DST_DB.dbo.dst_table ) ORDER BY SRC0.CreationTime )
The TOP takes care of the batching.
Both tables are clustered on CreationTime so the ORDER BY is just insurance.
The inner select is to avoid fetching
ColA, ColB, ... from src_table until after the TOP has taken effect, which actually helps a lot. I have also tried a version of this based on a left join, but this seems to make very little difference to the query plan and performance.
The problem is that performance slows down a lot as the dst_table fills up. It starts around 5000 rows/sec and slows down to 500 towards the end. As far as I can tell this is mostly due to the innermost "leftAntiSemiJoin" involving more and more rows.
The challenge is to find a way to avoid doing that
NOT IN (SELECT.. repeatedly, while still getting the benefits of batching.
If I could just select all the
NOT IN GUIDs into a
#TempTable at the start, there is no need to update those for every batch - except for the actual batching.
I know I could use a cursor loop, but that would make this a row-by-row operation, which I expect is much slower by nature. What I intuitively want to do is to "consume" GUIDs in batches from my
#TempTable, while simultaneously building my INSERT <- SELECT.
Is there any way I can make this work?
I have posted the solution I actually implemented as an answer below.