title: SQL Server Update query very slow
body: <p>I ran the following query on a previous years data and it took 3 hours, this year it took 13 days. I don't know why this is though. Any help would be much appreciated.</p>
  
  <p>I have just tested the queries in the old SQL server and it works in 3 hours. Therefore the problem must have something to do with the new SQL server I created. Do you have any ideas what the problem might be?</p>
  
  <p>The query:</p>
  
  <pre><code>USE [ABCJan]
  CREATE INDEX Link_Oct ON ABCJan2014 (Link_ref)
  GO
  CREATE INDEX Day_Oct ON ABCJan2014 (date_1)
  GO
  
  UPDATE   ABCJan2014
  SET      ABCJan2014.link_id = LT.link_id
  FROM     ABCJan2014 MT
  INNER JOIN  [Central].[dbo].[LookUp_ABC_20142015] LT
  ON MT.Link_ref = LT.Link_ref
  
  UPDATE   ABCJan2014
  SET      SumAvJT  = ABCJan2014.av_jt * ABCJan2014.n
  
  UPDATE   ABCJan2014
  SET      ABCJan2014.DayType = LT2.DayType
  FROM     ABCJan2014 MT
  INNER JOIN  [Central].[dbo].[ABC_20142015_days] LT2
  ON  MT.date_1 = LT2.date1
  </code></pre>
  
  <p>With the following data structures:</p>
  
  <p>ABCJan2014 (70 million rows - NO UNIQUE IDENTIFIER - Link_ref &amp; date_1 together are unique)</p>
  
  <pre><code>Link_ID nvarchar (17)
  Link_ref    int
  Date_1  smalldatetime
  N       int
  Av_jt       int
  SumAvJT decimal(38,14)
  DayType nvarchar (50)
  </code></pre>
  
  <p>LookUp_ABC_20142015</p>
  
  <pre><code>Link_ID nvarchar (17) PRIMARY KEY
  Link_ref    int INDEXED
  Link_metres int
  </code></pre>
  
  <p>ABC_20142015_days</p>
  
  <pre><code>Date1   smalldatetime   PRIMARY KEY &amp; INDEXED
  DayType nvarchar(50)
  </code></pre>
  
  <p>EXECUTION PLAN
  <a href="https://i.stack.imgur.com/GJFAJ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/GJFAJ.png" alt="enter image description here"></a></p>
  
  <p>It appears to be this part of the query that is taking such a long time. </p>
  
  <p>Thanks again for any help, I'm pulling my hair out.</p>