title: Improving horrible MERGE performance
body: <p>I recently asked a question about how to solve a problem in tsql query which led me to use a MERGE statement. This is however proving problematic as its performance is horrible.</p>
  
  <p><a href="https://stackoverflow.com/questions/59106360/how-do-i-get-inserted-row-id-along-with-related-data-back-after-insert-without">How do I get inserted row Id, along with related data back after insert without inserting the related data</a></p>
  
  <p><strong>What I need to do is insert rows based on a result set and save away the id of the inserted row along with the data that it resulted from (see related question).</strong></p>
  
  <p>I ended up with a query like this. </p>
  
  <pre><code>DECLARE @temp AS TABLE(
        [action] NVARCHAR(20)
       ,[GlobalId] BIGINT
       ,[Personnumber] NVARCHAR(100)
       ,[Firstname] NVARCHAR(100)
       ,[Lastname] NVARCHAR(100)
  );
  
  ;WITH person AS
  (
      SELECT top 1
          t.[Personnumber]
          ,t.[Firstname]
          ,t.[Lastname]
      FROM [temp].[RawRoles] t
      WHERE t.Personnumber NOT IN 
          (
              SELECT i.Account FROM [security].[Accounts] i
          )
  )
  
  MERGE [security].[Identities] AS tar
  USING person AS src
  ON 0 = 1 -- all rows from src need to be inserted, ive already filtered out using CTE Query.
  WHEN NOT MATCHED THEN
     INSERT
     (
       [Created], [Updated]
     )
     VALUES
     (
          GETUTCDATE(), GETUTCDATE()
     )
  OUTPUT $action, inserted.GlobalId, src.[Personnumber], src.[Firstname], src.[Lastname]  INTO @temp;
  
  
  SELECT * FROM @temp
  </code></pre>
  
  <p>Using this query I'm inserting all the rows and then saving them away to a temp table along with the source values for later processing.</p>
  
  <p>This works great on sub 10k rows. But the data set I'm doing this against is close to 2 million rows. <strong>I ran this query for about an hour without it completing</strong> (on a juiced up premium tier Azure database).</p>
  
  <p>Question: <strong>How can I make this faster.</strong> Can I achieve this same result without Merge?</p>