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
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.
WAITING: Queries can be slow because they're waiting on a bottleneck for a long time. See a detailed list of bottlenecks in types of Waits. RUNNING: Queries can be slow because they're running (executing) for a long time. In other words, these queries are actively using CPU resources.
Alter the query It can use a table that is suboptimal, or gather more information than necessary. Slow queries are frequently caused by combining two or more large tables together using a JOIN. Review the number of joins in your query, and determine if the query is pulling more information than is actually needed.
Create Index on ABCJan2014 table as it is currently a heap
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With