Can this batched insert be optimized with a #TempTable?

Question

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
)

Explanation
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?

UPDATE
I have posted the solution I actually implemented as an answer below.

Answer 1

It seems like it might be better to just loop a range of dates to insert.

For example:

INSERT INTO DST_DB.dbo.dst_table (MyGUID, Col1, Col2, ...) 
SELECT TheirGUID, ColA, ColB, ...
FROM SRC_DB.dbo.src_table AS SRC1 
WHERE SRC0.CreationTime >= @start
  AND SRC0.CreationTime < @end;

Just keep track of the start and end, and increment them by a fixed amount on each run.

---

If you are really set on batching it by a fixed number of rows, you can still use similar logic, keeping track of the top value. This technique is called Keyset Pagination.

INSERT INTO DST_DB.dbo.dst_table (MyGUID, Col1, Col2, ...) 
SELECT TOP (10000)
  TheirGUID, ColA, ColB, ...
FROM SRC_DB.dbo.src_table AS SRC1 
WHERE SRC0.CreationTime > @start;    -- leave this line off for the first run

SELECT TOP (1)
  @start = CreationTime 
FROM DST_DB.dbo.dst_table
ORDER BY CreationTime DESC;

Note that CreationTime must be unique. If it is not unique then you need something like this

INSERT INTO DST_DB.dbo.dst_table (MyGUID, Col1, Col2, ...) 
SELECT TOP (10000)
  TheirGUID, ColA, ColB, ...
FROM SRC_DB.dbo.src_table AS SRC1 
WHERE SRC0.CreationTime = @start AND SRC0.ID > @id
   OR SRC0.CreationTime > @start;    -- leave these lines off for the first run

SELECT TOP (1)
  @start = CreationTime,
  @ID = ID
FROM DST_DB.dbo.dst_table
ORDER BY CreationTime DESC, ID DESC;

Answer 2

if you rewrite your query to

INSERT INTO DST_DB.dbo.dst_table (MyGUID, Col1, Col2, ...) 
SELECT top 1000 TheirGUID, ColA, ColB, ...
FROM SRC_DB.dbo.src_table AS SRC1 
WHERE SRC1.TheirGUID not exists
( 
   SELECT * FROM DST_DB.dbo.dst_table d  WHERE SRC1.TheirGUID = d.TheirGUID
)

will it do what you need? I guess TheirGUID is indexed on both tables.

Answer 3

@charlieface provided the ideas here, but this is the solution I implemented. This could probably still be improved, but the first impression is that it's considerably faster. (I can't run the full 500GB job again just to test). Importantly it does not slow down as the target tables fill up!

CREATE TABLE #TempTable (
   ID [uniqueidentifier] NOT NULL,
   [CreationTime] [datetime] NOT NULL,
   CONSTRAINT [PK_PartIDTmp] PRIMARY KEY NONCLUSTERED (ID),
   INDEX [ix_PartIDTmp_sort] CLUSTERED ([CreationTime] ASC)
)

INSERT INTO #TempTable
SELECT TheirGUID, CreationTS
FROM SRC_DB.dbo.src_table
WHERE TheirGUID NOT IN (
    SELECT MyGUID FROM FROM DST_DB.dbo.dst_table
)
ORDER BY CreationTS

Prepare a list of from/to dates using the mentioned techique (in Python, if you want to know), then loop through those dates like this. (also handling the corner-cases)

INSERT INTO DST_DB.dbo.dst_table (MyGUID, Col1, Col2, ...) 
SELECT TheirGUID, ColA, ColB, ...
FROM SRC_DB.dbo.src_table AS SRC
WHERE SRC.TheirGUID IN ( 
 SELECT ID FROM #PartIDTmp
 WHERE CreationTime >= ? AND CreationTime < ?
)
ORDER BY SRC.CreationTime

I'm a little uncertain if some of the ordering and indexes might be superfluous, but this is a great improvement already, at the cost of a little extra complexity and a few extra seconds spent on upfront setup.

Answer 4

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.

Another factor that can affect insert performance are the indexes on dst_table. With a GUID index a FILLFACTOR of 100 will cause a lot of bad page splits. If dst_table already has a lot of rows, and you are adding to them, I would start by having the FILLFACTOR on the clustered GUID index of about 70 and a daily job to REBUILD the index if the fragmentation goes above 1%. If you are adding to an empty table, I would make it a heap and only have a nonclustered index on the GUID; the indexes you really want can be added once the load is completed.