title: How to optimize SQL Server Merge statement running with millions of records
body: <p>I use SQL Server 2014 and need to update a new added datetime type column in one table. There are two tables related (both have > 30 millions of records):</p>
  
  <p>TableA: </p>
  
  <pre><code>CategoryID, itemID, dataCreated, deleted, some other string properties. 
  </code></pre>
  
  <p>This table contains multiples records for each item with different <code>datecreated</code>. </p>
  
  <p>TableB:</p>
  
  <pre><code>CategoryID, itemID, LatestUpdatedDate (This is the new added column)
  </code></pre>
  
  <p>both <code>categoryID</code> and <code>itemID</code> are part of an index on this table.</p>
  
  <p>To update tableB's <code>LatestUpdatedDate</code> from table A on matched <code>CategoryID</code> and <code>ItemID</code>, I used the following merge statement:</p>
  
  <pre><code>merge [dbo].[TableB] with(HOLDLOCK) as t
  using 
  (
      select CategoryID,itemID, max(DateCreated) as LatestUpdatedDate 
      from dbo.TableA 
      where TableA.Deleted = 0
      group by CategoryID,itemID
  ) as s on t.CategoryID = s.CategoryID and t.itemID = s.itemID
  
  when matched then
      update
      set t.LatestUpdatedDate = s.LatestUpdatedDate
  
  when not matched then
      insert (CategoryID, itemID, LatestUpdatedDate)
      values (s.CategoryID, s.itemID)
  </code></pre>
  
  <p>Given the fact that millions of records in both table, How can I optimize this script? Or Is there any other way to update the table with better performance? </p>
  
  <p>Note: This is a one-off script and DB is on live, there would be a trigger added to tableA against insert to update the date in tableB in the future.</p>