Hi,
I have a recursive CTE query to view all sub mtls in a Method of Manufacture (ECOMtl table). This recursive call does not generate the parent part in the table. To do this I have added a union to the CTE table’s output with a single row subquery. By doing this I can retrieve the parent row however it dramatically increases the query execution time from 7 seconds to 30 seconds. Essentially all rows in the single row subquery are static.
How can I improve performance on this union or is there a better way to add a static row? Conceptually I don’t understand why a union with a single row should reduce performance this dramatically.
Thank you very much in advance,
The Code
/*
- Disclaimer!!!
- This is not a real query being executed, but a simplified version for general vision.
- Executing it with any other tool may produce a different result.
*/with [SubQuery1] as
(select
[ECOMtl].[PartNum] as [ECOMtl_PartNum],
[ECOMtl].[RevisionNum] as [ECOMtl_RevisionNum],
[ECOMtl].[MtlPartNum] as [ECOMtl_MtlPartNum],
[ECOMtl].[QtyPer] as [ECOMtl_QtyPer],
[ECOMtl].[MtlSeq] as [ECOMtl_MtlSeq],
(1) as [Calculated_Hierarchy],
(cast ( substring(‘…’,1 ,(Hierarchy + 1) ) + ECOMtl.MtlPartNum
as nvarchar(25))) as [Calculated_Ind1],
[ECORev2].[RevisionNum] as [ECORev2_RevisionNum]
from Erp.ECOMtl as ECOMtl
inner join Erp.ECORev as ECORev2 on
ECOMtl.Company = ECORev2.Company
and ECOMtl.GroupID = ECORev2.GroupID
and ECOMtl.MtlPartNum = ECORev2.PartNum
and ECOMtl.RevisionNum = ECORev2.RevisionNum
and ECOMtl.AltMethod = ECORev2.AltMethod
where (ECOMtl.PartNum = @PartNum)union all
select
[ECOMtl1].[PartNum] as [ECOMtl1_PartNum],
[ECOMtl1].[RevisionNum] as [ECOMtl1_RevisionNum],
[ECOMtl1].[MtlPartNum] as [ECOMtl1_MtlPartNum],
[ECOMtl1].[QtyPer] as [ECOMtl1_QtyPer],
[ECOMtl1].[MtlSeq] as [ECOMtl1_MtlSeq],
(SubQuery11.Calculated_Hierarchy + 1) as [Calculated_Hierarchy2],
(cast ( substring(‘…’,1 ,(Hierarchy2 + 1) ) + ECOMtl1.MtlPartNum
as nvarchar(25))) as [Calculated_Ind2],
[ECORev3].[RevisionNum] as [ECORev3_RevisionNum]
from Erp.ECOMtl as ECOMtl1
inner join SubQuery1 as SubQuery11 on
SubQuery11.ECOMtl_MtlPartNum = ECOMtl1.PartNum
inner join Erp.ECORev as ECORev3 on
ECOMtl1.Company = ECORev3.Company
and ECOMtl1.GroupID = ECORev3.GroupID
and ECOMtl1.MtlPartNum = ECORev3.PartNum
and ECOMtl1.RevisionNum = ECORev3.RevisionNum
and ECOMtl1.AltMethod = ECORev3.AltMethod)select
[SubQuery3].[ECOMtl_MtlPartNum] as [ECOMtl_MtlPartNum],
[SubQuery3].[ECORev2_RevisionNum] as [ECORev2_RevisionNum],
[SubQuery3].[Part_PartDescription] as [Part_PartDescription],
[SubQuery3].[ECOMtl_QtyPer] as [ECOMtl_QtyPer],
[SubQuery3].[ECOMtl_PartNum] as [ECOMtl_PartNum],
[SubQuery3].[ECOMtl_RevisionNum] as [ECOMtl_RevisionNum],
[SubQuery3].[Calculated_Hierarchy] as [Calculated_Hierarchy],
[SubQuery3].[Calculated_Ind1] as [Calculated_Ind1],
[SubQuery9].[ECORev1_ECO] as [ECORev1_ECO],
(avg (SubQuery6.Calculated_AdjEstProdHours )) as [Calculated_AvgEstHours],
(avg ( SubQuery6.Calculated_AdjActProdHours )) as [Calculated_AvgProdHours],
((case when (AvgProdHours > 0) then AvgEstHours / AvgProdHours else null end)) as [Calculated_EstimateOverActual],
[SubQuery9].[Calculated_sumEstHours] as [Calculated_sumEstHours],
(SubQuery9.Calculated_sumEstHours* SubQuery3.ECOMtl_QtyPer) as [Calculated_EstHoursTotal],
((case when (AvgProdHours > 0) then (SubQuery9.Calculated_sumEstHours) / AvgProdHours else null end)) as [Calculated_MoMEstOverActual]
from (select
[SubQuery1].[ECOMtl_PartNum] as [ECOMtl_PartNum],
[SubQuery1].[ECOMtl_RevisionNum] as [ECOMtl_RevisionNum],
[SubQuery1].[ECOMtl_MtlPartNum] as [ECOMtl_MtlPartNum],
[Part].[PartDescription] as [Part_PartDescription],
[SubQuery1].[ECOMtl_QtyPer] as [ECOMtl_QtyPer],
[SubQuery1].[Calculated_Hierarchy] as [Calculated_Hierarchy],
[SubQuery1].[Calculated_Ind1] as [Calculated_Ind1],
[SubQuery1].[ECORev2_RevisionNum] as [ECORev2_RevisionNum]
from SubQuery1 as SubQuery1
inner join Erp.Part as Part on
SubQuery1.ECOMtl_MtlPartNum = Part.PartNum
group by [SubQuery1].[ECOMtl_PartNum],
[SubQuery1].[ECOMtl_RevisionNum],
[SubQuery1].[ECOMtl_MtlPartNum],
[Part].[PartDescription],
[SubQuery1].[ECOMtl_QtyPer],
[SubQuery1].[Calculated_Hierarchy],
[SubQuery1].[Calculated_Ind1],
[SubQuery1].[ECORev2_RevisionNum]
union
select
(‘’) as [Calculated_PartNum],
(‘’) as [Calculated_RevNum2],
(@PartNum) as [Calculated_mtlPartNum],
[Part1].[PartDescription] as [Part1_PartDescription],
(1) as [Calculated_qtyPer],
(0) as [Calculated_Hiearchy],
(‘.’ + @PartNum) as [Calculated_ind],
[ECORev4].[RevisionNum] as [ECORev4_RevisionNum]
from Erp.Part as Part1
inner join Erp.ECORev as ECORev4 on
Part1.Company = ECORev4.Company
and Part1.PartNum = ECORev4.PartNum
where (Part1.PartNum = @PartNum)) as SubQuery3
left outer join (select
[JobAsmbl].[PartNum] as [JobAsmbl_PartNum],
[JobAsmbl].[RevisionNum] as [JobAsmbl_RevisionNum],
[JobAsmbl].[JobNum] as [JobAsmbl_JobNum],
[JobAsmbl].[AssemblySeq] as [JobAsmbl_AssemblySeq],
[JobAsmbl].[Parent] as [JobAsmbl_Parent],
[JobAsmbl].[RequiredQty] as [JobAsmbl_RequiredQty],
[JobAsmbl].[QtyPer] as [JobAsmbl_QtyPer],
[JobAsmbl].[StartDate] as [JobAsmbl_StartDate],
[JobAsmbl].[DueDate] as [JobAsmbl_DueDate],
[JobAsmbl].[JobComplete] as [JobAsmbl_JobComplete],
[SubQuery4].[Calculated_SumEstProdHours] as [Calculated_SumEstProdHours],
[SubQuery4].[Calculated_SumActProdHours] as [Calculated_SumActProdHours],
[SubQuery4].[Calculated_CurrentOpSeq] as [Calculated_CurrentOpSeq],
[JobOper1].[OpCode] as [JobOper1_OpCode],
[JobOper1].[OpDesc] as [JobOper1_OpDesc],
((case when JobAsmbl.RequiredQty <> 0 then SubQuery4.Calculated_SumEstProdHours / JobAsmbl.RequiredQty else null end)) as [Calculated_AdjEstProdHours],
((case when JobAsmbl.RequiredQty <> 0 then SubQuery4.Calculated_SumActProdHours / JobAsmbl.RequiredQty else null end)) as [Calculated_AdjActProdHours]
from (select
[JobOper].[JobNum] as [JobOper_JobNum],
[JobOper].[AssemblySeq] as [JobOper_AssemblySeq],
(min(
case when JobOper.OpComplete = 0 then
JobOper.OprSeq
else
null
end
)) as [Calculated_CurrentOpSeq],
(sum( JobOper.EstProdHours )) as [Calculated_SumEstProdHours],
(sum( JobOper.ActProdHours )) as [Calculated_SumActProdHours],
(max( JobOper.OprSeq )) as [Calculated_MaxOprSeq],
(min( JobOper.OprSeq )) as [Calculated_MinOprSeq],
(min(
case when JobOper.OpComplete = 0 then
JobOper.OprSeq
else
null
end
)) as [Calculated_MinNotCompleted],
(max(
case when JobOper.OpComplete = 1 then
JobOper.OprSeq
else 0
end
)) as [Calculated_MaxCompleted]
from Erp.JobOper as JobOper
where (JobOper.OpComplete = true)
group by [JobOper].[JobNum],
[JobOper].[AssemblySeq]) as SubQuery4
inner join Erp.JobAsmbl as JobAsmbl on
JobAsmbl.JobNum = SubQuery4.JobOper_JobNum
and JobAsmbl.AssemblySeq = SubQuery4.JobOper_AssemblySeq
and ( JobAsmbl.JobComplete = true )inner join Erp.JobHead as JobHead on
JobAsmbl.Company = JobHead.Company
and JobAsmbl.JobNum = JobHead.JobNum
left outer join Erp.JobOper as JobOper1 on
SubQuery4.JobOper_JobNum = JobOper1.JobNum
and SubQuery4.JobOper_AssemblySeq = JobOper1.AssemblySeq
and SubQuery4.Calculated_MinNotCompleted = JobOper1.OprSeq
left outer join (select
[PORel].[JobNum] as [PORel_JobNum],
[PORel].[AssemblySeq] as [PORel_AssemblySeq],
[PORel].[JobSeq] as [PORel_JobSeq],
(min(
case when PORel.OpenRelease = 1 then
PORel.PONum
end
)) as [Calculated_MinOpenPONum],
(count(PORel.PONum)) as [Calculated_PORelCount]
from Erp.PORel as PORel
where (PORel.JobSeqType = ‘S’)
group by [PORel].[JobNum],
[PORel].[AssemblySeq],
[PORel].[JobSeq]) as SubQuery5 on
JobOper1.JobNum = SubQuery5.PORel_JobNum
and JobOper1.AssemblySeq = SubQuery5.PORel_AssemblySeq
and JobOper1.OprSeq = SubQuery5.PORel_JobSeq) as SubQuery6 on
SubQuery3.ECOMtl_MtlPartNum = SubQuery6.JobAsmbl_PartNum
and SubQuery3.ECOMtl_RevisionNum = SubQuery6.JobAsmbl_RevisionNum
inner join (select
[SubQuery8].[ECORev_PartNum] as [ECORev_PartNum],
[SubQuery8].[ECORev_RevisionNum] as [ECORev_RevisionNum],
(sum(ECOOpr.EstProdHours)) as [Calculated_sumEstHours],
[ECORev1].[ECO] as [ECORev1_ECO]
from (select
[ECORev].[PartNum] as [ECORev_PartNum],
[ECORev].[RevisionNum] as [ECORev_RevisionNum],
(max (ECORev.CheckInDate)) as [Calculated_newestCheckIn]
from Erp.ECORev as ECORev
group by [ECORev].[PartNum],
[ECORev].[RevisionNum]) as SubQuery8
inner join Erp.ECORev as ECORev1 on
ECORev1.PartNum = SubQuery8.ECORev_PartNum
and ECORev1.RevisionNum = SubQuery8.ECORev_RevisionNum
and ECORev1.CheckInDate = SubQuery8.Calculated_newestCheckIn
inner join Erp.ECOOpr as ECOOpr on
ECORev1.Company = ECOOpr.Company
and ECORev1.GroupID = ECOOpr.GroupID
and ECORev1.PartNum = ECOOpr.PartNum
and ECORev1.RevisionNum = ECOOpr.RevisionNum
and ECORev1.AltMethod = ECOOpr.AltMethod
group by [SubQuery8].[ECORev_PartNum],
[SubQuery8].[ECORev_RevisionNum],
[ECORev1].[ECO]) as SubQuery9 on
SubQuery3.ECOMtl_MtlPartNum = SubQuery9.ECORev_PartNum
group by [SubQuery3].[ECOMtl_MtlPartNum],
[SubQuery3].[ECORev2_RevisionNum],
[SubQuery3].[Part_PartDescription],
[SubQuery3].[ECOMtl_QtyPer],
[SubQuery3].[ECOMtl_PartNum],
[SubQuery3].[ECOMtl_RevisionNum],
[SubQuery3].[Calculated_Hierarchy],
[SubQuery3].[Calculated_Ind1],
[SubQuery9].[ECORev1_ECO],
[SubQuery9].[Calculated_sumEstHours]
order by SubQuery3.Calculated_Ind1 Desc