title: Merge vs Select than insert update Performance Compare
body: <p>Which query is improve performance Query1 or Query2,</p>
  
  <p>Query1 uses merge statement, Query2 uses standart Select than insert update.</p>
  
  <p>I couldn't decide because Merge statement uses both side compare,
  Side 1 : Table1 > TAble1_Temp 
  Side 2 : Table1_Tempt > Table1</p>
  
  <p>Standart select compares data single side  Table1_Temp > Table1, (exists or not)</p>
  
  <p>Thanks for advange.</p>
  
  <p><strong>Query1</strong></p>
  
  <pre><code>MERGE Table1 AS T
  USING Table1_Temp AS S
  ON (T.col1= S.col1 and T.col2= S.col2) 
  WHEN NOT MATCHED BY TARGET
      THEN INSERT(col1, col2,col3,col4,col5,col6,col7,col8,col9,col10,col11) VALUES(S.col1, S.col2,S.col3,S.col4,S.col5,S.col6,S.col7,S.col8,S.col9,S.col10,S.col11)
  WHEN MATCHED 
      THEN UPDATE SET T.col3= S.col3,T.col4 = S.col4,T.col5=S.col5,T.col6=S.col6,T.col7=S.col7 ,T.col8= S.col8,T.col9= S.col9,T.col10= S.col10,T.col11= S.col11
      ;
  </code></pre>
  
  <p><strong>Query2</strong></p>
  
  <pre><code>UPDATE
      Table1
  SET
  
      col3 = Table1_Temp.col3,  
      col4 = Table1_Temp.col4,  
      col5 = Table1_Temp.col5,  
      col6 = Table1_Temp.col6,  
      col7 = Table1_Temp.col7,  
      col8 = Table1_Temp.col8,  
      col9 = Table1_Temp.col9,  
      col10 = Table1_Temp.col10,  
      col11 = Table1_Temp.col11,  
  
  FROM
      Table1
  INNER JOIN
      Table1_Temp
  ON
      Table1.col1 = Table1_Temp.col1 and 
      Table1.col2= Table1_Temp.col2
  
  
  
  
  Insert Into Table1(col1, col2,col3,col4,col5,col6,col7,col8,col9,col10,col11)
  Select  col1, col2,col3,col4,col5,col6,col7,col8,col9,col10,col11
          from Table1_Temp S  Where  not exists 
          (Select * from Table1 where S.col1 = Table1.col1 and S.col2 = Table1.col2)
  </code></pre>
  
  <p>2.680.000 rows in table1
  50.000 rows in table1_temp</p>
  
  <p>Compare 50.000 rows with 2.68 M rows.</p>
  
  <p>"Select Insert/Update" Execution time seems better than Merge.</p>
  
  <p>Any Idea ? </p>
  
  <p><strong>Client Statistics:</strong> for Merge Statement</p>
  
  <p><img src="https://i.stack.imgur.com/jFBBy.jpg" alt="Merge Statement"></p>
  
  <p><strong>Client Statistics:</strong> for Select than insert/Update</p>
  
  <p><img src="https://i.stack.imgur.com/ZPA8g.jpg" alt="Select Then update-insert"></p>
  
  <blockquote>
    <p>TableName is different on live DB. 
    Adaptv_Report = Table1,
    Adaptv_Report_Temp = Table1_temp</p>
  </blockquote>
  
  <p><strong>Execution Plan</strong> for Merge Statement
  <img src="https://i.stack.imgur.com/xYKoG.jpg" alt="MErge Execution Plan"></p>
  
  <p><strong>Execution Plan</strong> for Select Insert/Update
  <img src="https://i.stack.imgur.com/xk318.jpg" alt="enter image description here"></p>