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.