Consider the following query that inserts rows from a source table only if they aren't already in the target table:

    INSERT INTO dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR WITH (TABLOCK)
    SELECT maybe_new_rows.ID
    FROM dbo.A_HEAP_OF_MOSTLY_NEW_ROWS maybe_new_rows
    WHERE NOT EXISTS (
    	SELECT 1
    	FROM dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR halloween
    	WHERE maybe_new_rows.ID = halloween.ID
    )
    OPTION (MAXDOP 1, QUERYTRACEON 7470);

One possible plan shape includes a merge join and an eager spool. The eager spool operator is present to solve the [Halloween Problem][1]:

[![first plan][2]][2]

On my machine, the above code executes in about 6900 ms. Repro code to create the tables is included at the bottom of the question. If I'm dissatisfied with performance I might try to load the rows to be inserted into a temp table instead of relying on the eager spool. Here's one possible implementation:

    DROP TABLE IF EXISTS #CONSULTANT_RECOMMENDED_TEMP_TABLE;
    CREATE TABLE #CONSULTANT_RECOMMENDED_TEMP_TABLE (
    	ID BIGINT,
    	PRIMARY KEY (ID)
    );
    
    INSERT INTO #CONSULTANT_RECOMMENDED_TEMP_TABLE WITH (TABLOCK)
    SELECT maybe_new_rows.ID
    FROM dbo.A_HEAP_OF_MOSTLY_NEW_ROWS maybe_new_rows
    WHERE NOT EXISTS (
    	SELECT 1
    	FROM dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR halloween
    	WHERE maybe_new_rows.ID = halloween.ID
    )
    OPTION (MAXDOP 1, QUERYTRACEON 7470);
    
    INSERT INTO dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR WITH (TABLOCK)
    SELECT new_rows.ID
    FROM #CONSULTANT_RECOMMENDED_TEMP_TABLE new_rows
    OPTION (MAXDOP 1);

The new code executes in about 4400 ms. I can get actual plans and use Actual Time Statistics™ to examine where time is spent at the operator level. Note that asking for an actual plan adds significant overhead for these queries so totals will not match the previous results.

``` none
╔═════════════╦═════════════╦══════════════╗
║  operator   ║ first query ║ second query ║
╠═════════════╬═════════════╬══════════════╣
║ big scan    ║ 1771        ║ 1744         ║
║ little scan ║ 163         ║ 166          ║
║ sort        ║ 531         ║ 530          ║
║ merge join  ║ 709         ║ 669          ║
║ spool       ║ 3202        ║ N/A          ║
║ temp insert ║ N/A         ║ 422          ║
║ temp scan   ║ N/A         ║ 187          ║
║ insert      ║ 3122        ║ 1545         ║
╚═════════════╩═════════════╩══════════════╝
```

The query plan with the eager spool seems to spend significantly more time on the insert and spool operators compared to the plan that uses the temp table.

Why is the plan with the temp table more efficient? Isn't an eager spool mostly just an internal temp table anyway? I believe I am looking for answers that focus on internals. I'm able to see how the call stacks are different but can't figure out the big picture.

I am on SQL Server 2017 CU 11 in case someone wants to know. Here is code to populate the tables used in the above queries:

    DROP TABLE IF EXISTS dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR;
    
    CREATE TABLE dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR (
    ID BIGINT NOT NULL,
    PRIMARY KEY (ID)
    );
    
    INSERT INTO dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR WITH (TABLOCK)
    SELECT TOP (20000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
    CROSS JOIN master..spt_values t3
    OPTION (MAXDOP 1);
    
    
    DROP TABLE IF EXISTS dbo.A_HEAP_OF_MOSTLY_NEW_ROWS;
    
    CREATE TABLE dbo.A_HEAP_OF_MOSTLY_NEW_ROWS (
    ID BIGINT NOT NULL
    );
    
    INSERT INTO dbo.A_HEAP_OF_MOSTLY_NEW_ROWS WITH (TABLOCK)
    SELECT TOP (1900000) 19999999 + ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2;


  [1]: https://sqlperformance.com/2013/02/t-sql-queries/halloween-problem-part-1
  [2]: https://i.stack.imgur.com/sB7G3.png