deepakagarwalathome (10/2/2012)
Hi ChrisM@WorkThanks a zillion for your response. That works fine. No problems.
A couple of questions though :-
First, whilst both 'Over Partiion By...' and the derived table sub-query do the same thing, is the former more efficient in its execution by an order of magnitude or it happened to be so in this one instance?
It’s more efficient because it only requires one read of the table. Aggregating then joining back requires two.
Second, I am attaching the full query that I now have (I haven't replaced the similar code in the first half of the query as the inner join is very quick - it was only the left join that was the problem - which has now been addressed - thanks to you) and I find that it work fine in about 9 seconds if the last 'Where...' clause after the table 'z' definition is not included. However, if you include this last 'Where...' clause' it takes a hell of a lot longer (10+ minutes). I can certainly populate a table with the dataset without the last 'Where...' clause and then extract those records where the condition of the last 'Where...' clause' is implemented. But I wanted to know is this happening. Any suggestions.
Lack of indexes and index stats – the optimizer can only make a poor guess with little to work on.
I’ve made a couple of amendments to your full query but there’s a way to go yet. There’s a lot of almost-duplicated reads of tables.
Further, out of interest, did the execution plans and the statistics that I had included give you any more indication as to why one option took 75+ minutes whilst the other took less than 10 seconds????????????
Not really. The execution plans are estimated – the actual plans are far more useful. If you can post them as attachments, that would be grand.
P.S. I have updated this post with the output and statistics for the two options - took 105 minutes with the last where clause included.
Best regards
Deepak
Here’s what I’ve got so far – test and see if it matches your existing output. Get the indexing done if you can.
/*
recommended indexes
NETWORK_VALIDATION..evd_NonNBV_ForChecking_BAU
METER_ID, RECORD_ID, BILLING_START_DATE, billing_end_date, provider
NETWORK_VALIDATION..MSATS_DATA_BAU_FINAL
NMI, FromDate, ToDate, Role, Party
*/
-- SumNetAmount1 and SumNetAmount2 generate the same result set
; WITH SumNetAmount1 AS (
Selectd.RECORD_ID, d.METER_ID, d.BILLING_START_DATE, d.BILLING_END_DATE, d.NET_AMOUNT As NetAmount, d.PROVIDER
From (
Select METER_ID, BILLING_START_DATE, BILLING_END_DATE, PROVIDER
From NETWORK_VALIDATION..evd_NonNBV_ForChecking_BAU
Group By METER_ID, BILLING_START_DATE, BILLING_END_DATE, Provider
Having Sum(net_AMOUNT) <> 0
) c
Inner Join NETWORK_VALIDATION..evd_NonNBV_ForChecking_BAU d
on c.METER_ID = d.METER_ID
And c.BILLING_START_DATE = d.BILLING_START_DATE
And c.BILLING_END_DATE = d.BILLING_END_DATE
),
SumNetAmount2 AS (
Select RECORD_ID, METER_ID, BILLING_START_DATE, BILLING_END_DATE, NET_AMOUNT AS NetAmount, PROVIDER,
SUMNET_AMOUNT = SUM(net_AMOUNT) OVER(PARTITION BY METER_ID, BILLING_START_DATE, BILLING_END_DATE, Provider)
From NETWORK_VALIDATION..evd_NonNBV_ForChecking_BAU
)
selectz.FRMP_RecordCount, z.FRMP_Record_Id, z.FRMP_Meter_Id, z.FRMP_FromDate, z.FRMP_ToDate, z.FRMP_Billing_Start_Date, z.FRMP_Billing_End_Date
,z.FRMP_NetAmount, z.FRMP_Party, z.FRMP_Provider
,z.MDP_RecordCount, z.MDP_Record_Id, z.MDP_Meter_Id, z.MDP_FromDate, z.MDP_ToDate, z.MDP_Billing_Start_Date, z.MDP_Billing_End_Date
,z.MDP_NetAmount, z.MDP_Party, z.MDP_Provider
,z.StatusComment
From(SelectFRMP_RecordCount, FRMP_Record_Id, FRMP_Meter_Id, FRMP_FromDate, FRMP_ToDate, FRMP_Billing_Start_Date, FRMP_Billing_End_Date
,FRMP_NetAmount, FRMP_Party, FRMP_Provider
,MDP_RecordCount, MDP_Record_Id, MDP_Meter_Id, MDP_FromDate, MDP_ToDate, MDP_Billing_Start_Date, MDP_Billing_End_Date
,MDP_NetAmount, MDP_Party, MDP_Provider
,CaseWhen z1.FRMP_Party Is Not Null ThenCaseWhen z2.MDP_Party Is Not Null Then'VALID - TRU FRMP, CORRECT MDP' Else 'INVALID - TRU FRMP, INCORRECT MDP' End
Else 'INVALID - TRU NOT FRMP'
End 'StatusComment'
From ( -- z1
SelectCount(*) FRMP_RecordCount, b.RECORD_ID FRMP_Record_Id, b.METER_ID FRMP_Meter_Id, Convert(Varchar(10), a.FromDate, 103) FRMP_FromDate
,Convert(Varchar(10), a.ToDate, 103) FRMP_ToDate, Convert(Varchar(10), b.Billing_Start_Date, 103) FRMP_Billing_Start_Date,
Convert(Varchar(10), b.Billing_End_Date, 103) FRMP_Billing_End_Date
,NetAmount FRMP_NetAmount, a.Party FRMP_Party, b.PROVIDER FRMP_Provider
From SumNetAmount1 b -- CTE
Inner Join NETWORK_VALIDATION..MSATS_DATA_BAU_FINAL a
On a.NMI = b.METER_ID
and (((b.BILLING_START_DATE between a.FromDate and a.ToDate)
and (b.billing_end_date between a.FromDate and a.ToDate))
and a.Role = 'FRMP')
Group By
b.RECORD_ID,
b.METER_ID,
Convert(Varchar(10), a.FromDate, 103),
Convert(Varchar(10), a.ToDate, 103),
Convert(Varchar(10), b.Billing_Start_Date, 103),
Convert(Varchar(10), b.Billing_End_Date, 103),
NetAmount, a.Party, b.PROVIDER
) z1
Full Outer Join
( -- z2
Selectcount(*) MDP_RecordCount, b.RECORD_ID MDP_Record_Id, b.METER_ID MDP_Meter_Id, Convert(Varchar(10), a.FromDate, 103) MDP_FromDate
,Convert(Varchar(10), a.ToDate, 103) MDP_ToDate, Convert(Varchar(10), b.Billing_Start_Date, 103) MDP_Billing_Start_Date, Convert(Varchar(10), b.Billing_End_Date, 103) MDP_Billing_End_Date
,NetAmount MDP_NetAmount, a.Party MDP_Party, b.PROVIDER MDP_Provider
From SumNetAmount1 b -- CTE
Inner Join NETWORK_VALIDATION..MSATS_DATA_BAU_FINAL a
On a.NMI = b.METER_ID
and (((b.BILLING_START_DATE between a.FromDate and a.ToDate)
and (b.billing_end_date between a.FromDate and a.ToDate))
and a.Role = 'MDP')
Where1 =CaseWhen Upper(b.PROVIDER) = 'SPA' and Left(a.party, 3) In ('GLO', 'SPA') Then 1
When Upper(b.PROVIDER) <> 'SPA' and Upper(b.PROVIDER) = Left(a.party, 3) Then 1
End
Group By
b.RECORD_ID,
b.METER_ID,
Convert(Varchar(10), a.FromDate, 103),
Convert(Varchar(10), a.ToDate, 103),
Convert(Varchar(10), b.Billing_Start_Date, 103),
Convert(Varchar(10), b.Billing_End_Date, 103),
NetAmount, a.Party, b.PROVIDER
) z2
On z1.FRMP_Record_Id = z2.MDP_Record_Id
Union All
SelectFRMP_RecordCount, FRMP_Record_Id, FRMP_Meter_Id, FRMP_FromDate, FRMP_ToDate, FRMP_Billing_Start_Date, FRMP_Billing_End_Date
,FRMP_NetAmount, FRMP_Party, FRMP_Provider
,MDP_RecordCount, MDP_Record_Id, MDP_Meter_Id, MDP_FromDate, MDP_ToDate, MDP_Billing_Start_Date, MDP_Billing_End_Date
,MDP_NetAmount, MDP_Party, MDP_Provider
,CaseWhenz1.FRMP_Party Is Not Null ThenCaseWhenz2.MDP_Party Is Not Null Then'VALID - TRU FRMP, CORRECT MDP'
Else'INVALID - TRU FRMP, INCORRECT MDP'
End
Else'INVALID - TRU NOT FRMP'
End 'StatusComment'
From( -- z1
Selectcount(*) FRMP_RecordCount, b.RECORD_ID FRMP_Record_Id, b.METER_ID FRMP_Meter_Id, Convert(Varchar(10), a.FromDate, 103) FRMP_FromDate
,Convert(Varchar(10), a.ToDate, 103) FRMP_ToDate, Convert(Varchar(10), b.Billing_Start_Date, 103) FRMP_Billing_Start_Date, Convert(Varchar(10), b.Billing_End_Date, 103) FRMP_Billing_End_Date
,NetAmount FRMP_NetAmount, a.Party FRMP_Party, b.PROVIDER FRMP_Provider
From SumNetAmount2 b --- ## CTE
LEFT JOIN NETWORK_VALIDATION..MSATS_DATA_BAU_FINAL a
On a.NMI = b.METER_ID
and b.BILLING_START_DATE BETWEEN a.FromDate AND a.ToDate
and b.billing_end_date BETWEEN a.FromDate AND a.ToDate
and a.Role = 'FRMP'
Where b.SUMnet_AMOUNT <> 0
and a.NMI Is Null
Group By
b.RECORD_ID,
b.METER_ID,
Convert(Varchar(10), a.FromDate, 103),
Convert(Varchar(10), a.ToDate, 103),
Convert(Varchar(10), b.Billing_Start_Date, 103),
Convert(Varchar(10), a.ToDate, 103),
Convert(Varchar(10), b.Billing_End_Date, 103),
NetAmount, a.Party, b.PROVIDER
) z1
Full Outer Join ( -- z2
Selectcount(*) MDP_RecordCount, b.RECORD_ID MDP_Record_Id, b.METER_ID MDP_Meter_Id, Convert(Varchar(10), a.FromDate, 103) MDP_FromDate, Convert(Varchar(10), a.ToDate, 103) MDP_ToDate
,Convert(Varchar(10), b.Billing_Start_Date, 103) MDP_Billing_Start_Date, Convert(Varchar(10), b.Billing_End_Date, 103) MDP_Billing_End_Date
,NetAmount MDP_NetAmount, a.Party MDP_Party, b.PROVIDER MDP_Provider
From SumNetAmount2 b --- ## CTE
LEFT JOIN NETWORK_VALIDATION..MSATS_DATA_BAU_FINAL a
On a.NMI = b.METER_ID
and b.BILLING_START_DATE BETWEEN a.FromDate AND a.ToDate
and b.billing_end_date BETWEEN a.FromDate AND a.ToDate
and a.Role = 'MDP'
Where b.SUMnet_AMOUNT <> 0
and a.NMI Is Null
and 1 = Case
When Upper(b.PROVIDER) = 'SPA' and Left(a.party, 3) In ('GLO', 'SPA') Then 1
When Upper(b.PROVIDER) <> 'SPA' and Upper(b.PROVIDER) = Left(a.party, 3) Then 1
End
Group By
b.RECORD_ID,
b.METER_ID,
Convert(Varchar(10), a.FromDate, 103),
Convert(Varchar(10), a.ToDate, 103),
Convert(Varchar(10), b.Billing_Start_Date, 103),
Convert(Varchar(10), a.ToDate, 103),
Convert(Varchar(10), b.Billing_End_Date, 103),
NetAmount, a.Party, b.PROVIDER
) z2
On z1.FRMP_Record_Id = z2.MDP_Record_Id
) z
Wherez.FRMP_Record_Id Is Not Null
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden