title: UPDATE from CTE
body: <p>I am updating a table from the result of a self join via a CTE. When doing a select to view the expected results of the update it is able to return the results in 16 seconds.</p>
  
  <p>However, when I then use the same syntax to try to actually update the underlying table, it is excruciatingly slow.</p>
  
  <p>For the life of me I cannot figure out why such a dramatic slowdown. I frequently update tables via CTE and it is usually adequetely fast and reasonable compared to the equivalent select.</p>
  
  <p>I have tried both with and without a PrimaryKey/Clustered index on the underlying table and it makes no difference.</p>
  
  <p>The join is on a calculated column so cannot be indexed.</p>
  
  <p>If the difference in time between the SELECT and the UPDATE was say double then it wouldn't be a concern. The problem here is the sheer magnitude of the time increase when going from the select to the update. 16 seconds for the select with a result of 1323 rows, to update just 2 of those rows takes 59 seconds, to update 4 takes 1 min 19 seconds and to update 6 takes 1 min 39 seconds (so it seems each additional row adds 10 seconds).</p>
  
  <p>Can anybody shed some light on this for me and suggest a way to speed this up?</p>
  
  <p>Here's the sample code:</p>
  
  <pre><code>;WITH CTE AS (SELECT
                  DENSE_RANK() OVER (Order by
                                  col1,
                                  col2,
                                  col3) SetID,                
                  COUNT(*) OVER (partition by
                                  col1,
                                  col2,
                                  col3) DupsInSet,
                  row_number() OVER (PARTITION BY
                                  col1,
                                  col2,
                                  col3
                                  ORDER BY                                    
                                  col4 desc) RowInSet,
                  COUNT(col4) OVER (partition by
                                  col1,
                                  col2,
                                  col3) NonNull,
                  *
              FROM mytable)
  </code></pre>
  
  <p>--The following completes in 16 seconds and returns 1323 rows</p>
  
  <pre><code>    select b.col4,a.*
  
      from cte a
      join cte b on b.SetID=a.SetID
  
      where a.DupsInSet&gt;1
      and a.NonNull&gt;0
      and b.RowInSet=1
      and a.RowInSet&gt;1
      and b.col4 is not null
      and a.col4 is null
  </code></pre>
  
  <p>--Updating from this runs for so long that I didn't even let it finish
  --As a test I limited the update to the Top 2. This then took 59 seconds just to update 2 rows</p>
  
  <pre><code>    UPDATE TOP(2) a
  
      SET a.col4=b.col4
  
      from cte a
      join cte b on b.SetID=a.SetID
  
      where a.DupsInSet&gt;1
      and a.NonNull&gt;0
      and b.RowInSet=1
      and a.RowInSet&gt;1
      and b.col4 is not null
      and a.col4 is null
  </code></pre>
  
  <p>Actual Execution Plan for SELECT <a href="https://www.brentozar.com/pastetheplan/?id=ByItSnhuW" rel="nofollow noreferrer">https://www.brentozar.com/pastetheplan/?id=ByItSnhuW</a></p>
  
  <p>Actual Execution Plan for UPDATE <a href="https://www.brentozar.com/pastetheplan/?id=Ske_In3_Z" rel="nofollow noreferrer">https://www.brentozar.com/pastetheplan/?id=Ske_In3_Z</a>  </p>
  
  <p><strong>UPDATE:</strong></p>
  
  <p>Taking the suggestion from SqlZim it ran for over 4 minutes without finishing at which point i stopped it.</p>
  
  <p>However, with</p>
  
  <ol>
  <li>a change in the schema (thank you SqlZim) from several VARCHAR(MAX) columns down to VARCHAR(?) where ? is the max(len(column))</li>
  </ol>
  
  <p>and</p>
  
  <ol start="2">
  <li>some modifications to SqlZims suggested query, the update was able to run in 23 seconds!...which is approximately 3,300 times faster :)</li>
  </ol>
  
  <p>Here is the final query (unless someone can make it work without having to list all the darn columns e.g. joining on the SetID):</p>
  
  <p>(note, the CTE is still used to filter the original table down from its 600k+ rows)</p>
  
  <pre><code>;WITH CTE AS (SELECT                             
                  COUNT(*) OVER (partition by
                                  [col1]
                                ,[col2]
                                ,[col3]
                                ,[col4]
                                ,[col5]
                                ,[col6]
                                ,[col7]
                                ,[col8]
                                ,[col9]
                                ,[col10]
                                ,[col11]
                                ,[col12]
                                ,[col13]
                                ,[col14]
                                ,[col15]
                                ,[col16]
                                ,[col17]
                                ,[col18]
                                ,[col19]
                                ,[col20]
                                ,[col21]
                                ,[col22]
                                ,[col23]) DupsInSet,
                  COUNT(col24) OVER (partition by
                                  [col1]
                                ,[col2]
                                ,[col3]
                                ,[col4]
                                ,[col5]
                                ,[col6]
                                ,[col7]
                                ,[col8]
                                ,[col9]
                                ,[col10]
                                ,[col11]
                                ,[col12]
                                ,[col13]
                                ,[col14]
                                ,[col15]
                                ,[col16]
                                ,[col17]
                                ,[col18]
                                ,[col19]
                                ,[col20]
                                ,[col21]
                                ,[col22]
                                ,[col23]) NonNull,
                  *
              FROM mytable
      )
  
  update a
    set a.col24 = b.col24
  from cte a
    cross apply (
      select top 1 i.col24
      from cte i
      where (i.col1=a.col1 OR (i.col1 is null AND a.col1 is null))
           and (i.col2=a.col2 OR (i.col2 is null AND a.col2 is null))
           and (i.col3=a.col3 OR (i.col3 is null AND a.col3 is null))
           and (i.col4=a.col4 OR (i.col4 is null AND a.col4 is null))
           and (i.col5=a.col5 OR (i.col5 is null AND a.col5 is null))
           and (i.col6=a.col6 OR (i.col6 is null AND a.col6 is null))
           and (i.col7=a.col7 OR (i.col7 is null AND a.col7 is null))
           and (i.col8=a.col8 OR (i.col8 is null AND a.col8 is null))
           and (i.col9=a.col9 OR (i.col9 is null AND a.col9 is null))
           and (i.col10=a.col10 OR (i.col10 is null AND a.col10 is null))
           and (i.col11=a.col11 OR (i.col11 is null AND a.col11 is null))
           and (i.col12=a.col12 OR (i.col12 is null AND a.col12 is null))
           and (i.col13=a.col13 OR (i.col13 is null AND a.col13 is null))
           and (i.col14=a.col14 OR (i.col14 is null AND a.col14 is null))
           and (i.col15=a.col15 OR (i.col15 is null AND a.col15 is null))
           and (i.col16=a.col16 OR (i.col16 is null AND a.col16 is null))
           and (i.col17=a.col17 OR (i.col17 is null AND a.col17 is null))
           and (i.col18=a.col18 OR (i.col18 is null AND a.col18 is null))
           and (i.col19=a.col19 OR (i.col19 is null AND a.col19 is null))
           and (i.col20=a.col20 OR (i.col20 is null AND a.col20 is null))
           and (i.col21=a.col21 OR (i.col21 is null AND a.col21 is null))
           and (i.col22=a.col22 OR (i.col22 is null AND a.col22 is null))
           and (i.col23=a.col23 OR (i.col23 is null AND a.col23 is null))
          and i.col24 is not null
      order by col24 desc
    ) b
  where a.col24 is null
  and a.DupsInSet&gt;1
  and a.NonNull&gt;0
  </code></pre>