November 16, 2012 at 11:16 am
HI All,
COuld you please help me to tune the below query.. Thanks in advance..
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
go
UPDATE Reading.EDV_Interval_Reading_Master
SET LocalDate_Reading_Value = OUTER_FINAL.Reading_Value,
Last_Update_DateTime = GETUTCDATE()
FROM (
SELECT MAS.Measure_ID,
MAS.Reading_Date_ID,
MAS.Reading_Value,
EIRM.Reading_Version,
ROW_NUMBER() OVER (PARTITION BY MAS.Measure_ID, MAS.Reading_Date_ID
ORDER BY EIRM.Reading_Version DESC) AS RN_EIRM
FROM Reading.EDV_Interval_Reading_Master EIRM
INNER JOIN
(
SELECT Measure_id,
Reading_Date_ID,
SUM(Reading_Value) Reading_Value
FROM (
SELECTRD.Measure_ID,
CAST(REPLACE(CONVERT(VARCHAR(10), Local_DateTime, 120), '-', '') AS INT) Reading_Date_ID,
RD.Reading_Value,
RD.Reading_Version,
ROW_NUMBER() OVER (PARTITION BY RD.Measure_ID, RD.Reading_Date_ID, RD.Time_Period
ORDER BY RD.Reading_Version DESC) AS RN
FROM Reading.EDV_Interval_Reading_Detail RD
WHERE EXISTS (
SELECT 1
FROM Reading.EDV_Interval_Reading_Detail IRD
WHERE IRD.Job_ID = 10
AND IRD.Measure_ID = RD.Measure_ID
AND CAST(RD.Local_DateTime AS DATE)= CAST(IRD.Local_DateTime AS DATE)
)
) A
WHERE RN = 1
GROUP BY Measure_ID, Reading_Date_ID
) MAS
ON MAS.Measure_ID = EIRM.Measure_ID
AND MAS.Reading_Date_ID = EIRM.Reading_Date_ID
) OUTER_FINAL
WHERE OUTER_FINAL.Measure_ID = Reading.EDV_Interval_Reading_Master.Measure_ID
AND OUTER_FINAL.Reading_Date_ID = Reading.EDV_Interval_Reading_Master.Reading_Date_ID
AND OUTER_FINAL.Reading_Version = Reading.EDV_Interval_Reading_Master.Reading_Version
AND OUTER_FINAL.RN_EIRM = 1
November 16, 2012 at 11:45 am
I'm going to suggest taking each of the subqueries, from the inside first, and turning it into a temp table, then using the final temp table in the final query.
The query is massively complex. The SQL engine is pretty much giving up on coming up with an efficient query plan. So, break it up, and make each step simpler, and you'll probably get much faster results.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply