deepakagarwalathome (10/3/2012)
Hi ChrisThanks for that!I fully understand what you are trying to do with the CTE's. I have run the query with the CTE's.
The problem, however, remains the same, that is, it still executes the option without the last 'Where...' clause in around 10 seconds but with the last 'Where...' clause included, it takes aound the same 75 minutes.
I have attached the 'Actual Execution' plans for the two options for your perusal - I don't understand how to read them. These are for the queries as was sent to you yesterday (without the CTE definition - but as I said earlier that didn't make a hell of a lot of difference in the execution times at all).
Looking forward to hearing from you about what you find therein!
Best regards
Deepak
It takes 75 minutes because the optimiser is choosing a poor plan based on the information it has. It would be interesting though to see the execution plans with and without the WHERE clause. I'm afraid the attachment is of no value to me. What you should do is save an actual plan as a .sqlplan file and attach it to the post. Do this for the actual plans with and without the WHERE clause.
I've had another play with your query. There are four queries within it which read data, something like
q1 full outer join q2
union all
q3 full outer join q4
I've reconstructed the common elements of q1 and q2 as CTE1, and q3 and q4 as CTE2. The resulting query looks like this:
; WITH
CTE1 AS ( -- used twice by part above UNION ALL
SELECT
b.RECORD_ID RECORD_ID,
b.METER_ID Meter_Id,
Convert(Varchar(10), a.FromDate, 103) FromDate,
Convert(Varchar(10), a.ToDate, 103) ToDate,
Convert(Varchar(10), b.Billing_Start_Date, 103) Billing_Start_Date,
Convert(Varchar(10), b.Billing_End_Date, 103) Billing_End_Date,
b.NetAmount NetAmount,
SUMNET_AMOUNT = SUM(b.net_AMOUNT) OVER(PARTITION BY METER_ID, BILLING_START_DATE, BILLING_END_DATE, Provider),
a.Party Party,
b.PROVIDER Provider,
a.[Role] [Role]
FROM NETWORK_VALIDATION..evd_NonNBV_ForChecking_BAU b
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
),
CTE2 AS ( -- used twice by part below UNION ALL
SELECT
b.RECORD_ID RECORD_ID,
b.METER_ID Meter_Id,
Convert(Varchar(10), a.FromDate, 103) FromDate,
Convert(Varchar(10), a.ToDate, 103) ToDate,
Convert(Varchar(10), b.Billing_Start_Date, 103) Billing_Start_Date,
Convert(Varchar(10), b.Billing_End_Date, 103) Billing_End_Date,
b.NetAmount NetAmount,
SUMNET_AMOUNT = SUM(b.net_AMOUNT) OVER(PARTITION BY METER_ID, BILLING_START_DATE, BILLING_END_DATE, Provider),
a.Party Party,
b.PROVIDER Provider,
a.[Role] [Role]
FROM NETWORK_VALIDATION..evd_NonNBV_ForChecking_BAU b
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
WHERE a.NMI IS NULL
)
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(
Select
FRMP_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,
CASE
WHEN a1.FRMP_Party IS NOT NULL AND a2.MDP_Party IS NOT NULL THEN 'VALID - TRU FRMP, CORRECT MDP'
WHEN a1.FRMP_Party IS NOT NULL AND a2.MDP_Party IS NULL THEN 'INVALID - TRU FRMP, INCORRECT MDP'
ELSE 'INVALID - TRU NOT FRMP'
END [StatusComment]
FROM ( -- q1
SELECT
Count(*) FRMP_RecordCount,
RECORD_ID FRMP_Record_Id,
Meter_Id FRMP_Meter_Id,
FromDate FRMP_FromDate,
ToDate FRMP_ToDate,
Billing_Start_Date FRMP_Billing_Start_Date,
Billing_End_Date FRMP_Billing_End_Date,
NetAmount FRMP_NetAmount,
Party FRMP_Party,
Provider FRMP_Provider
FROM CTE1
WHERE [Role] = 'FRMP'
AND SUMNET_AMOUNT <> 0
GROUP BY RECORD_ID, Meter_Id, FromDate, ToDate,
Billing_Start_Date, Billing_End_Date, NetAmount, Party, Provider
) q1
FULL OUTER JOIN ( -- q2
SELECT
Count(*) MDP_RecordCount,
RECORD_ID MDP_Record_Id,
Meter_Id MDP_Meter_Id,
FromDate MDP_FromDate,
ToDate MDP_ToDate,
Billing_Start_Date MDP_Billing_Start_Date,
Billing_End_Date MDP_Billing_End_Date,
NetAmount MDP_NetAmount,
Party MDP_Party,
Provider MDP_Provider
FROM CTE1
WHERE [Role] = 'MDP'
AND SUMNET_AMOUNT <> 0
AND ( (Upper(PROVIDER) = Left(party, 3)) OR (Upper(PROVIDER) = 'SPA' and Left(party, 3) = 'GLO') )
GROUP BY Record_Id, Meter_Id, FromDate, ToDate,
Billing_Start_Date, Billing_End_Date, NetAmount, Party, Provider
) q2 ON q1.FRMP_Record_Id = q2.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,
CASE
WHEN z1.FRMP_Party Is Not Null AND z2.MDP_Party Is Not Null Then 'VALID - TRU FRMP, CORRECT MDP'
WHEN z1.FRMP_Party Is Not Null AND z2.MDP_Party Is Null Then 'INVALID - TRU FRMP, INCORRECT MDP'
ELSE 'INVALID - TRU NOT FRMP'
END [StatusComment]
From ( -- q3
SELECT
count(*) FRMP_RecordCount,
RECORD_ID FRMP_Record_Id,
METER_ID FRMP_Meter_Id,
Convert(Varchar(10), FromDate, 103) FRMP_FromDate,
Convert(Varchar(10), ToDate, 103) FRMP_ToDate,
Convert(Varchar(10), Billing_Start_Date, 103) FRMP_Billing_Start_Date,
Convert(Varchar(10), Billing_End_Date, 103) FRMP_Billing_End_Date,
NetAmount FRMP_NetAmount, Party FRMP_Party, PROVIDER FRMP_Provider
FROM CTE2
WHERE [Role] = 'FRMP'
AND SUMNET_AMOUNT <> 0
GROUP BY Record_Id, Meter_Id, FromDate, ToDate,
Billing_Start_Date, Billing_End_Date, NetAmount, Party, Provider
) q3
FULL OUTER JOIN ( -- q4
SELECT
count(*) MDP_RecordCount,
RECORD_ID MDP_Record_Id,
METER_ID MDP_Meter_Id,
Convert(Varchar(10), FromDate, 103) MDP_FromDate,
Convert(Varchar(10), ToDate, 103) MDP_ToDate,
Convert(Varchar(10), Billing_Start_Date, 103) MDP_Billing_Start_Date,
Convert(Varchar(10), Billing_End_Date, 103) MDP_Billing_End_Date,
NetAmount MDP_NetAmount, Party MDP_Party, PROVIDER MDP_Provider
FROM CTE2
WHERE [Role] = 'MDP'
AND SUMnet_AMOUNT <> 0
AND ( (Upper(PROVIDER) = Left(party, 3)) OR (Upper(PROVIDER) = 'SPA' and Left(party, 3) = 'GLO') )
GROUP BY Record_Id, Meter_Id, FromDate, ToDate,
Billing_Start_Date, Billing_End_Date, NetAmount, Party, Provider
) q4 ON q3.FRMP_Record_Id = q4.MDP_Record_Id
) z
Wherez.FRMP_Record_Id Is Not Null
You should test that this works and amend it if it doesn't generate the expected results.
Having reconstructed the query to use those two CTE's, you can now see the wood through the trees. The query actually looks like this;
-- get all matching rows between table a and table b
SELECT columns
FROM NETWORK_VALIDATION..evd_NonNBV_ForChecking_BAU b
INNER JOIN NETWORK_VALIDATION..MSATS_DATA_BAU_FINAL a ON a.matchingcolumn = b.matchingcolumn
UNION ALL
-- get all rows from table b where there's no match in table a
SELECT columns
FROM NETWORK_VALIDATION..evd_NonNBV_ForChecking_BAU b
LEFT JOIN NETWORK_VALIDATION..MSATS_DATA_BAU_FINAL a ON a.matchingcolumn = b.matchingcolumn
WHERE a.matchingcolumn IS NULL
Now this is logically equivalent to a left join; get all rows from the left hand table whether or not they match on the right (get all rows from the lhs where they match, plus all rows from the lhs where they don't). This simplifies your overall query to the following:
; WITH
CTE1 AS ( -- used twice
SELECT
b.RECORD_ID RECORD_ID,
b.METER_ID Meter_Id,
CAST(a.FromDate AS DATE) FromDate,
CAST(a.ToDate AS DATE) ToDate,
CAST(b.Billing_Start_Date AS DATE) Billing_Start_Date,
CAST(b.Billing_End_Date AS DATE) Billing_End_Date,
b.NetAmount NetAmount,
SUMNET_AMOUNT = SUM(b.net_AMOUNT) OVER(PARTITION BY METER_ID, BILLING_START_DATE, BILLING_END_DATE, Provider),
a.Party Party,
b.PROVIDER Provider,
a.[Role] [Role]
FROM NETWORK_VALIDATION..evd_NonNBV_ForChecking_BAU b
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
)
SELECT
FRMP_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,
CASE
WHEN a1.FRMP_Party IS NOT NULL AND a2.MDP_Party IS NOT NULL THEN 'VALID - TRU FRMP, CORRECT MDP'
WHEN a1.FRMP_Party IS NOT NULL AND a2.MDP_Party IS NULL THEN 'INVALID - TRU FRMP, INCORRECT MDP'
ELSE 'INVALID - TRU NOT FRMP'
END [StatusComment]
FROM ( -- q1
SELECT
Count(*) FRMP_RecordCount,
RECORD_ID FRMP_Record_Id,
Meter_Id FRMP_Meter_Id,
FromDate FRMP_FromDate,
ToDate FRMP_ToDate,
Billing_Start_Date FRMP_Billing_Start_Date,
Billing_End_Date FRMP_Billing_End_Date,
NetAmount FRMP_NetAmount,
Party FRMP_Party,
Provider FRMP_Provider
FROM CTE1
WHERE [Role] = 'FRMP'
AND SUMNET_AMOUNT <> 0
AND RECORD_ID IS NOT NULL
GROUP BY RECORD_ID, Meter_Id, FromDate, ToDate,
Billing_Start_Date, Billing_End_Date, NetAmount, Party, Provider
) q1
LEFT JOIN ( -- q2
SELECT
Count(*) MDP_RecordCount,
RECORD_ID MDP_Record_Id,
Meter_Id MDP_Meter_Id,
FromDate MDP_FromDate,
ToDate MDP_ToDate,
Billing_Start_Date MDP_Billing_Start_Date,
Billing_End_Date MDP_Billing_End_Date,
NetAmount MDP_NetAmount,
Party MDP_Party,
Provider MDP_Provider
FROM CTE1
WHERE [Role] = 'MDP'
AND SUMNET_AMOUNT <> 0
AND ( (Upper(PROVIDER) = Left(party, 3)) OR (Upper(PROVIDER) = 'SPA' and Left(party, 3) = 'GLO') )
GROUP BY Record_Id, Meter_Id, FromDate, ToDate,
Billing_Start_Date, Billing_End_Date, NetAmount, Party, Provider
) q2 ON q1.FRMP_Record_Id = q2.MDP_Record_Id
Remember where you had a full outer join between the two derived tables – followed by that pesky WHERE clause (Where z.FRMP_Record_Id Is Not Null)? The WHERE clause turns the full outer join into a left join. I think that’s all it does, but just in case I’ve included it in q1.
Removing a full outer join from a query against tables with no indexes is likely to significantly improve performance – give it a shot and let us know how you get on. When you’re done (preferably before), you should concentrate on those indexes. Ask if you are unsure how to go about deciding which columns to use.
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