SQL Server Update query very slow

Create Index on ABCJan2014 table as it is currently a heap


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?


Why have 3 update statements when you can do it in one?

UPDATE   MT
SET      MT.link_id = CASE WHEN LT.link_id IS NULL THEN MT.link_id ELSE LT.link_id END,
         MT.SumAvJT  = MT.av_jt * MT.n,
         MT.DayType = CASE WHEN LT2.DayType IS NULL THEN MT.DayType ELSE LT2.DayType END
FROM     ABCJan2014 MT
LEFT OUTER JOIN  [Central].[dbo].[LookUp_ABC_20142015] LT
    ON MT.Link_ref = LT.Link_ref
LEFT OUTER JOIN  [Central].[dbo].[ABC_20142015_days] LT2
    ON MT.date_1 = LT2.date1

Also, I would create only one index for the join. Create the following index after the updates.

CREATE INDEX Day_Oct ON ABCJan2014 (date_1)
GO

Before you run, compare the execution plan by putting the update query above and your 3 update statements altogether in one query window, and do Display Estimated Execution Plan. It will show the estimated percentages and you'll be able to tell if it's any better (if new one is < 50%).

Also, it looks like the query is slow because it's doing a Hash Match. Please add a PK index on [LookUp_ABC_20142015].Link_ref.

[LookUp_ABC_20142015].Link_ID is a bad choice for PK, so drop the PK on that column.

Then add an index to [ABCJan2014].Link_ref.

See if that makes any improvement.

Tags:

Sql

Sql Server