Nested loop no join predicate
-
Hi All, I have the following T-SQL;
With
CTECTD1(dossierno,custno,regnno,costcentre,odometer,fuel,repairs,leasecharges,tollroadcharges,rucrego,otherexpenses,accident,managment,litersper,litersavg)
AS
(
SELECT
f.DossierNo as [DossierNo]
,f.CustNo
,f.RegnNo
,d.CostCentre
,MAX(f.Odo) as [Odometer]
,SUM(CASE WHEN l.[InvSeq] in ('70') THEN CAST(ROUND([CTD_Amount],2) AS INT) ELSE '' END) as [Fuel/Oil]
,SUM(CASE WHEN l.[InvSeq] in ('40','45') THEN CAST(ROUND([CTD_Amount],2) AS INT) ELSE '' END) as [Repairs/Maintenance]
,SUM(CASE WHEN l.[InvSeq] in ('35') THEN CAST(ROUND([CTD_Amount],2) AS INT) ELSE '' END) as [LeaseCharges]
,SUM(CASE WHEN l.[InvSeq] in ('65') THEN CAST(ROUND([CTD_Amount],2) AS INT) ELSE '' END) as [TollRoadCharges]
,SUM(CASE WHEN l.[InvSeq] in ('60','61') THEN CAST(ROUND([CTD_Amount],2) AS INT) ELSE '' END) as [RUC/Rego]
,SUM(CASE WHEN l.[InvSeq] in ('10','20','21','22','23','80','90') THEN CAST(ROUND([CTD_Amount],2) AS INT) ELSE '' END) as [OtherExpenses]
,SUM(CASE WHEN l.[InvSeq] in ('50') THEN CAST(ROUND([CTD_Amount],2) AS INT) ELSE '' END) as [AccidentInsurance]
,SUM(CASE WHEN l.[InvSeq] in ('30','31') THEN CAST(ROUND([CTD_Amount],2) AS INT) ELSE '' END) as [ManagementCharges]
--,SUM(i.NetAmount) as [Total]
--,SUM(i.NetAmount)/NULLIF((MAX(f.odo)),0) as [TotalCentsPerKm]
,0 as [LitresPerVehicle]
,0 as [LitresNationalAverage]
FROM [FPNZ_Extra].[tblFleetSmartVehicleLifetoDate] l
INNER JOIN [FPNZ_Extra].[tblFuelTran] f
ON f.dossierNo = l.dossierNo and f.CustNo = l.CustNo and f.RegnNo = l.RegnNo
INNER JOIN [FPNZ_Extra].[vwDataDossier] d
ON f.DossierNo = d.DossierNo and f.RegnNo = d.RegnNo and d.CustNo = f.CustNo
--INNER JOIN [FPNZ_Extra].[tblFLEETSMARTCustomerReportDistribution] c
--on f.custno = c.[CustNo]and l.CustNo = c.CustNo
WHERE
IsOdoValid = '1'
AND d.costcentre != ' '
--AND c.ReportName = 'DM Vehicle Analysis'
--AND l.CustNo in ('300562','309032','312832','316984','317264','317671','381297','381612')
GROUP BY f.custno,f.regnno,d.costcentre,f.DossierNo,l.custno
)
,
CTECTD2(costcentre,custno,dossierno,regnno,modelyear,vehicledesc,drivername,total,totalcents,span)
as
(
SELECT
i.costCentre as [CostCentre]
,i.CustNo
,i.DossierNo as [DossierNo]
,i.regnno
,d.ModelYear as [ModelYear]
,i.VehicleDesc
,i.DriverName
,SUM(i.NetAmount) as [Total]
,SUM(i.NetAmount)/NULLIF((MAX(f.odo)),0) as [TotalCentsPerKm]
,MAX(f.odo) - MAX(sub.[odo]) as [Span(Kms)]
--,MAX(f.odo) - MAX(sub.[odo]) as [TotalKmsToDate]
FROM [FPNZ_Extra].[vwDataDossier] d
INNER JOIN [FPNZ_Extra].[tblInvoiceItemData] i
ON d.DossierNo = i.DossierNo
INNER JOIN [FPNZ_Extra].[tblFLEETSMARTCustomerReportDistribution] c
on d.custno = c.[CustNo]
INNER JOIN [FPNZ_Extra].[tblFuelTran] f
ON f.dossierNo = i.DossierNo
LEFT OUTER JOIN
(SELECT MIN(f1.odo)as [odo],regnno, MIN(f1.LastOdoDate) as [oldest]
FROM [FPNZ_Extra].[tblFuelTran] f1
WHERE
IsOdoValid = '1'
AND f1.regnno != ' '
GROUP BY RegnNo
)as sub
ON f.regnno = sub.RegnNo
WHERE
i.costcentre != ' '
AND c.ReportName = 'DM Vehicle Analysis'
--AND i.CustNo in ('300562','309032','312832','316984','317264','317671','381297','381612')
GROUP BY i.CustNo, i.costcentre, i.regnno, i.DossierNo, d.ModelYear,i.VehicleDesc,i.DriverName
)
,
CTECTD4(costcentre,custno,dossierno,regnno,fuel,repairs,leasecharges,tollroadcharges,rucrego,otherexpenses,accident,managment,total)
AS
(
--------------Vehicle Analysis BPT
SELECT
i.costCentre as [CostCentre]
,i.CustNo
,i.DossierNo as [DossierNo]
,i.regnno
,SUM(CASE WHEN [InvSeq] in ('70') THEN NetAmount ELSE '' END) as [Fuel/Oil]
,SUM(CASE WHEN [InvSeq] in ('40','45') THEN NetAmount ELSE '' END) as [Repairs/Maintenance]
,SUM(CASE WHEN [InvSeq] in ('35') THEN NetAmount ELSE '' END) as [LeaseCharges]
,SUM(CASE WHEN [InvSeq] in ('65') THEN NetAmount ELSE '' END) as [TollRoadCharges]
,SUM(CASE WHEN [InvSeq] in ('60','61') THEN NetAmount ELSE '' END) as [RUC/Rego]
,SUM(CASE WHEN [InvSeq] in ('10','20','21','22','23','80','90') THEN NetAmount ELSE '' END) as [OtherExpenses]
,SUM(CASE WHEN [InvSeq] in ('50') THEN NetAmount ELSE '' END) as [AccidentInsurance]
,SUM(CASE WHEN [InvSeq] in ('30','31') THEN NetAmount ELSE '' END) as [ManagementCharges]
,SUM(i.NetAmount) as [Total]
FROM [FPNZ_Extra].[tblFuelTran] f
INNER JOIN [FPNZ_Extra].[tblInvoiceItemData] i
ON f.DossierNo = i.DossierNo and f.RegnNo = i.RegnNo
INNER JOIN [FPNZ_Extra].[tblFLEETSMARTCustomerReportDistribution] c
on f.custno = c.[CustNo]
WHERE DATEPART(m, f.[FuelTranDate]) = DATEPART(m, DATEADD(m, -2, getdate())) AND DATEPART(yyyy, f.[FuelTranDate] ) = DATEPART(yyyy, DATEADD(m, -2, getdate()))
AND IsOdoValid = '1'
AND i.costcentre != ' '
AND c.ReportName = 'DM Vehicle Analysis'
--AND i.CustNo in ('300562','309032','312832','316984','317264','317671','381297','381612')
GROUP BY i.CustNo, i.costcentre, i.regnno, i.DossierNo,i.VehicleDesc,i.DriverName
)
,
CTECTD5 (custno,dossierno,Regnno,c02)
AS
(
SELECT f1.CustNo, f1.DossierNo,f1.RegnNo, sum([C02])
FROM
(SELECT CustNo, dossierno,regnno,
CASE WHEN Product = 'Diesel' THEN ISNULL(CAST(2.70 * (SUM([Quantity]) / NULLIF(1000,0)) AS DECIMAL (18,2)),0)
WHEN Product = 'Premium' THEN ISNULL(CAST(ROUND(2.36 * (SUM([Quantity]) / NULLIF(1000,0)),2) AS DECIMAL (18,2)),0)
WHEN Product = 'Regular' THEN ISNULL(CAST(ROUND(2.34 * (SUM([Quantity]) / NULLIF(1000,0)),2) AS DECIMAL (18,2)),0) END AS [C02]
FROM [FPNZ_Extra].[tblFuelTran]
WHERE
Producttype = 'Fuel'
AND Product in ('Diesel','Premium','Regular')
AND quantity is not null
AND odo is not null
AND odo != ' '
GROUP BY custno,DossierNo, RegnNo, Product
) f1
GROUP BY f1.CustNo, f1.DossierNo,f1.RegnNo
)
SELECT 'CTD' as [CTDRecType]
,d1.costcentre
, D2.regnno
, D2.modelyear
, d2.vehicledesc
, d2.drivername
, d1.odometer
, d2.span
, (d1.fuel + d4.fuel) [Fuel/Oil]
, (d1.repairs + d4.repairs) [Repairs]
, (d1.leasecharges + d4.leasecharges) [LeaseCharges]
, (d1.tollroadcharges + d4.tollroadcharges) [TollCharges]
, (d1.rucrego + d4.rucrego) [RUC/Rego]
, (d1.otherexpenses + d4.otherexpenses) [OtherExpenses]
, (d1.accident + d4.accident) [Accident]
, (d1.managment + d4.managment) [Managment]
,(d2.total + d4.total) [TotalexclGST]
, d2.totalcents [TotalCents]
, d1.litersper [Litres/100Km]
, d1.litersavg [Litres/National]
--, d5.c02 [C02]
, d2.span as [TotalKms]
FROM CTECTD1 D1
JOIN CTECTD2 D2
ON D1.CostCentre = D2.costcentre and d1.dossierno = d2.dossierno and d1.regnno = d2.regnno
JOIN CTECTD4 D4
ON D2.CostCentre = D4.costcentre and d2.regnno = d4.regnno
--JOIN CTECTD5 D5
--ON D1.Custno = D5.Custno and d1.regnno = d5.regnno
This generates calculated columns for all records in a year.It used to take between 2-3 hours to finish running, I have improved the performance by adding some clustered and non clustered index's on the tables. But in the query planner it has a nested loop with a no join predicate, see attached image.
Does anyone have any suggestions? thanks.
[/url][/img] -
Run the code of the three relevant CTE's separately to identify which one is responsible, then doublecheck the execution plan. The optimiser throws up false positives from time to time, and this might be one of them.
The code references a number of tables / views multiple times. There's almost certainly scope for significant improvement. Have you considered hiring a consultant for a while for some hands-on training?
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 -
How about trying temporary tables with indexes?
- Damian
-
I found and fixed the problem in the CTE's I added more joins and this removed the No Join Predicate, thanks for the replies.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply