Search code examples
sqlsql-server

SQL Server Update query very slow

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.

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?

The query:

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

With the following data structures:

ABCJan2014 (70 million rows - NO UNIQUE IDENTIFIER - Link_ref & date_1 together are unique)

Link_ID nvarchar (17)
Link_ref    int
Date_1  smalldatetime
N       int
Av_jt       int
SumAvJT decimal(38,14)
DayType nvarchar (50)

LookUp_ABC_20142015

Link_ID nvarchar (17) PRIMARY KEY
Link_ref    int INDEXED
Link_metres int

ABC_20142015_days

Date1   smalldatetime   PRIMARY KEY & INDEXED
DayType nvarchar(50)

EXECUTION PLAN enter image description here

It appears to be this part of the query that is taking such a long time.

Thanks again for any help, I'm pulling my hair out.

Solution

  • If you look at the execution plan the time is in the actual update

    Look at the log file
    Is the log file on a fast disk?
    Is the log file on the same physical disk?
    Is the log file required to grow?
    Size the log file to like 1/2 the size of the data file

    As far as indexes test and tune this
    If the join columns are indexed not much to do here

    select   count(*) 
FROM     ABCJan2014 MT
INNER JOIN  [Central].[dbo].[LookUp_ABC_20142015] LT
ON MT.Link_ref = LT.Link_ref

select   count(*) 
FROM     ABCJan2014 MT
INNER JOIN  [Central].[dbo].[ABC_20142015_days] LT2
ON  MT.date_1 = LT2.date1

    Start with a top (1000) to get update tuning working
    For grins please give this a try
    Please post this query plan
    (do NOT add an index to ABCJan2014 link_id) 

    UPDATE   top (1000) ABCJan2014
SET      MT.link_id = LT.link_id
FROM     ABCJan2014 MT
JOIN     [Central].[dbo].[LookUp_ABC_20142015] LT
          ON MT.Link_ref = LT.Link_ref 
         AND MT.link_id <> LT.link_id

    If LookUp_ABC_20142015 is not active then add a nolock 

    JOIN     [Central].[dbo].[LookUp_ABC_20142015] LT with (nolock)

    nvarchar (17) for a PK to me is just strange
    why n - do you really have some unicode?
    why not just char(17) and let it allocate space?