title: SQL Server Merge and Indexing Speed
body: <p>I have a merge statement that needs to compare on many columns. The source table has 26,000 rows. The destination table has several million rows. The desintation table only has a typical Primary Key index on an int-type column.</p>
  
  <p>I did some selects with group by to count the number of unique values in the source.</p>
  
  <p>The test part of the Merge is</p>
  
  <pre><code>Merge Into desttable
  Using #temptable
  On 
  (
  desttable.ColumnA = #temptable.ColumnA  
  and
  desttable.ColumnB  =  #temptable.ColumnB  
  and
  desttable.ColumnC  = #temptable.ColumnC 
  and
  desttable.ColumnD  = #temptable.ColumnD 
  and
  desttable.ColumnE  = #temptable.ColumnE 
  and
  desttable.ColumnF  = #temptable.ColumnF
  ) 
  When Not Matched Then  Insert Values (.......)
  
  -- ColumnA: 167 unique values in #temptable
  -- ColumnB: 1 unique values in #temptable
  -- ColumnC: 13 unique values in #temptable
  -- ColumnD: 89 unique values in #temptable
  -- ColumnE: 550 unique values in #temptable
  -- ColumnF: 487 unique values in #temptable
  
  -- ColumnA: 3690 unique values in desttable
  -- ColumnB: 3 unique values (plus null is possible) in desttable
  -- ColumnC: 1113 unique values in desttable
  -- ColumnD: 2662 unique values in desttable
  -- ColumnE: 1770 unique values in desttable
  -- ColumnF: 1480 unique values in desttable
  </code></pre>
  
  <p>The merge right now takes a very, very long time. I think I need to change my primary key but am not sure what the best tactic might be. 26,000 rows can be inserted on the first merge, but subsequent merges might only have ~2,000 inserts to do. Since I have no indexes and only a simple PK, everything is slow. :)</p>
  
  <p>Can anyone point out how to make this better?</p>
  
  <p>Thanks!</p>