0
\$\begingroup\$

The intent of the query is to filter out certain materials to get to a list that will then be run through a recursive CTE to drill up through the BOM to the 0 level assembly.

  • P_Parts_CTE is filtering to get just purchased parts
  • P_Parts_Inv_CTE is getting the on hand inventory of the purchased parts
  • PartDtl_Sum_CTE is taking the same list of parts from the first CTE and summing all Supply and Demand records for each part
  • PartDtl_CTE is using the results of the prior two CTEs to take the on hand inventory, add in the supply, and subtract the demand to get to a projected balance
  • Parts_Neg_CTE is filtering to get the parts that have a negative balance
  • Reverse_Recursive_BOM_CTE takes the list of parts from prior CTE and explodes them upwards in the BOM to the 0 level

It works, but I ran it last night and was tired of waiting for it and killed it at 35 minutes. I'm not sure if I did something wrong or if the query is just that complex. I would love to do this all in one query, but if there is no way to improve this then I will create a table and run a stored procedure to populate it with the exploded BOM. Thanks in advance

Query

with P_Parts_CTE (Company, PartNum)  --Identify purchased parts
as
(
    select Company, PartNum
    from dbo.Part
    where Company = 'Comp' and TypeCode = 'P'
),
P_Part_Inv_CTE (Company, PartNum, OnHandQty)  --Get on-hand inventory for parts
as
(
    select a.Company, a.PartNum, Sum(OnHandQty)
    from P_Parts_CTE as a
    left outer join dbo.PartWhse as b
    on a.Company = b.Company and a.PartNum = b.PartNum
    group by a.Company, a.PartNum
),
PartDtl_Sum_CTE (Company, PartNum, Supply, Demand)  --Get current supply & demand for parts
as
(
    select c.Company, c.PartNum, Sum(case when d.RequirementFlag = 0 then d.Quantity else 0 end) as Supply, Sum(case when d.RequirementFlag = 1 then d.Quantity else 0 end) as Demand
    from P_Parts_CTE as c
    left outer join dbo.PartDtl as d
    on c.Company = d.Company and c.PartNum = d.PartNum
    group by c.Company, c.PartNum
),
PartDtl_CTE (Company, PartNum, Balance)  --Find out the balance of inventory after supply and demand are factored in
as
(
    select e.Company, e.PartNum, (e.OnHandQty + f.Supply - f.Demand) as Balance
    from P_Part_Inv_CTE as e
    left outer join PartDtl_Sum_CTE as f
    on e.Company = f.Company and e.PartNum = f.PartNum
    group by e.Company, e.PartNum, e.OnHandQty, f.Supply, f.Demand
),
Parts_Neg_CTE (Company, PartNum)  --Get list of parts where balance is negative
as
(
    select Company, PartNum
    from PartDtl_CTE
    where Balance < 0
),
Reverse_Recursive_BOM_CTE (Company, PartNum, [Level], MtlPartNUm)  --As these are mainly materials that go into finished goods, blow out the BOM upwards
as
(
    select h.Company, h.PartNum, 0 as [Level], h.MtlPartNum
    from Parts_Neg_CTE as g
    inner join dbo.PartMtl as h
    on g.Company = h.Company and g.PartNum = h.PartNum
    union all
    select i.Company, i.PartNum, [Level] - 1, i.MtlPartNum
    from dbo.PartMtl as i
    inner join Reverse_Recursive_BOM_CTE as j
    on i.MtlPartNum = j.PartNum
)
select *
from Reverse_Recursive_BOM_CTE

Execution Plan

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.1" Build="10.50.2500.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="43701.6" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="61.7944" StatementText="with P_Parts_CTE (Company, PartNum)&#xD;&#xA;as&#xD;&#xA;(&#xD;&#xA; select Company, PartNum&#xD;&#xA;   from dbo.Part&#xD;&#xA; where Company = 'Bruce' and TypeCode = 'P'&#xD;&#xA;),&#xD;&#xA;P_Part_Inv_CTE (Company, PartNum, OnHandQty)&#xD;&#xA;as&#xD;&#xA;(&#xD;&#xA;   select a.Company, a.PartNum, Sum(OnHandQty)&#xD;&#xA;   from P_Parts_CTE as a&#xD;&#xA; left outer join dbo.PartWhse as b&#xD;&#xA; on a.Company = b.Company and a.PartNum = b.PartNum&#xD;&#xA;    group by a.Company, a.PartNum&#xD;&#xA;),&#xD;&#xA;PartDtl_Sum_CTE (Company, PartNum, Supply, Demand)&#xD;&#xA;as&#xD;&#xA;(&#xD;&#xA;  select c.Company, c.PartNum, Sum(case when d.RequirementFlag = 0 then d.Quantity else 0 end) as Supply, Sum(case when d.RequirementFlag = 1 then d.Quantity else 0 end) as Demand&#xD;&#xA; from P_Parts_CTE as c&#xD;&#xA; left outer join dbo.PartDtl as d&#xD;&#xA;  on c.Company = d.Company and c.PartNum = d.PartNum&#xD;&#xA;    group by c.Company, c.PartNum&#xD;&#xA;),&#xD;&#xA;PartDtl_CTE (Company, PartNum, Balance)&#xD;&#xA;as&#xD;&#xA;(&#xD;&#xA; select e.Company, e.PartNum, (e.OnHandQty + f.Supply - f.Demand) as Balance&#xD;&#xA;   from P_Part_Inv_CTE as e&#xD;&#xA;  left outer join PartDtl_Sum_CTE as f&#xD;&#xA;  on e.Company = f.Company and e.PartNum = f.PartNum&#xD;&#xA;    group by e.Company, e.PartNum, e.OnHandQty, f.Supply, f.Demand&#xD;&#xA;),&#xD;&#xA;Parts_Neg_CTE (Company, PartNum)&#xD;&#xA;as&#xD;&#xA;(&#xD;&#xA;   select Company, PartNum&#xD;&#xA;   from PartDtl_CTE&#xD;&#xA;  where Balance &lt; 0&#xD;&#xA;),&#xD;&#xA;Reverse_Recursive_BOM_CTE (Company, PartNum, [Level], MtlPartNUm)&#xD;&#xA;as&#xD;&#xA;(&#xD;&#xA;    select h.Company, h.PartNum, 0 as [Level], h.MtlPartNum&#xD;&#xA;   from Parts_Neg_CTE as g&#xD;&#xA;   inner join dbo.PartMtl as h&#xD;&#xA;   on g.Company = h.Company and g.PartNum = h.PartNum&#xD;&#xA;    union all&#xD;&#xA; select i.Company, i.PartNum, [Level] - 1, i.MtlPartNum&#xD;&#xA;    from dbo.PartMtl as i&#xD;&#xA; inner join Reverse_Recursive_BOM_CTE as j&#xD;&#xA; on i.MtlPartNum = j.PartNum&#xD;&#xA;)&#xD;&#xA;select *&#xD;&#xA;from Reverse_Recursive_BOM_CTE" StatementType="SELECT" QueryHash="0x7FDBDBEEA130262E" QueryPlanHash="0x9402E43E7FD31267">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan CachedPlanSize="168" CompileTime="100" CompileCPU="100" CompileMemory="5680">
            <RelOp AvgRowSize="73" EstimateCPU="0.000218508" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="43701.6" LogicalOp="Lazy Spool" NodeId="0" Parallel="false" PhysicalOp="Index Spool" EstimatedTotalSubtreeCost="61.7944">
              <OutputList>
                <ColumnReference Column="Expr1066" />
                <ColumnReference Column="Recr1028" />
                <ColumnReference Column="Recr1029" />
                <ColumnReference Column="Recr1030" />
                <ColumnReference Column="Recr1031" />
              </OutputList>
              <Spool Stack="true">
                <RelOp AvgRowSize="73" EstimateCPU="4.37016E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="43701.6" LogicalOp="Concatenation" NodeId="1" Parallel="false" PhysicalOp="Concatenation" EstimatedTotalSubtreeCost="61.7937">
                  <OutputList>
                    <ColumnReference Column="Expr1066" />
                    <ColumnReference Column="Recr1028" />
                    <ColumnReference Column="Recr1029" />
                    <ColumnReference Column="Recr1030" />
                    <ColumnReference Column="Recr1031" />
                  </OutputList>
                  <Concat>
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Column="Expr1066" />
                        <ColumnReference Column="Expr1063" />
                        <ColumnReference Column="Expr1065" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Column="Recr1028" />
                        <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartMtl]" Alias="[h]" Column="Company" />
                        <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartMtl]" Alias="[i]" Column="Company" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Column="Recr1029" />
                        <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartMtl]" Alias="[h]" Column="PartNum" />
                        <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartMtl]" Alias="[i]" Column="PartNum" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Column="Recr1030" />
                        <ColumnReference Column="Expr1020" />
                        <ColumnReference Column="Expr1027" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Column="Recr1031" />
                        <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartMtl]" Alias="[h]" Column="MtlPartNum" />
                        <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartMtl]" Alias="[i]" Column="MtlPartNum" />
                      </DefinedValue>
                    </DefinedValues>
                    <RelOp AvgRowSize="73" EstimateCPU="0.000437016" EstimateIO="0" EstimateRebinds="43701.6" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="2" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.000437016">
                      <OutputList>
                        <ColumnReference Column="Expr1063" />
                        <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartMtl]" Alias="[h]" Column="Company" />
                        <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartMtl]" Alias="[h]" Column="PartNum" />
                        <ColumnReference Column="Expr1020" />
                        <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartMtl]" Alias="[h]" Column="MtlPartNum" />
                      </OutputList>
                      <ComputeScalar>
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Column="Expr1063" />
                            <ScalarOperator ScalarString="(0)">
                              <Const ConstValue="(0)" />
                            </ScalarOperator>
                          </DefinedValue>
                        </DefinedValues>
                        <RelOp AvgRowSize="40" EstimateCPU="0.00436224" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="43622.4" LogicalOp="Compute Scalar" NodeId="3" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="22.3494">
                          <OutputList>
                            <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartMtl]" Alias="[h]" Column="Company" />
                            <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartMtl]" Alias="[h]" Column="PartNum" />
                            <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartMtl]" Alias="[h]" Column="MtlPartNum" />
                            <ColumnReference Column="Expr1020" />
                          </OutputList>
                          <ComputeScalar>
                            <DefinedValues>
                              <DefinedValue>
                                <ColumnReference Column="Expr1020" />
                                <ScalarOperator ScalarString="(0)">
                                  <Const ConstValue="(0)" />
                                </ScalarOperator>
                              </DefinedValue>
                            </DefinedValues>
                            <RelOp AvgRowSize="36" EstimateCPU="1.35964" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="43622.4" LogicalOp="Inner Join" NodeId="4" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="22.3451">
                              <OutputList>
                                <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartMtl]" Alias="[h]" Column="Company" />
                                <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartMtl]" Alias="[h]" Column="PartNum" />
                                <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartMtl]" Alias="[h]" Column="MtlPartNum" />
                              </OutputList>
                              <MemoryFractions Input="0.140598" Output="1" />
                              <Hash>
                                <DefinedValues />
                                <HashKeysBuild>
                                  <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[Part]" Column="PartNum" />
                                </HashKeysBuild>
                                <HashKeysProbe>
                                  <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartMtl]" Alias="[h]" Column="PartNum" />
                                </HashKeysProbe>
                                <ProbeResidual>
                                  <ScalarOperator ScalarString="[Epicor905].[dbo].[Part].[PartNum]=[Epicor905].[dbo].[PartMtl].[PartNum] as [h].[PartNum]">
                                    <Compare CompareOp="EQ">
                                      <ScalarOperator>
                                        <Identifier>
                                          <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[Part]" Column="PartNum" />
                                        </Identifier>
                                      </ScalarOperator>
                                      <ScalarOperator>
                                        <Identifier>
                                          <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartMtl]" Alias="[h]" Column="PartNum" />
                                        </Identifier>
                                      </ScalarOperator>
                                    </Compare>
                                  </ScalarOperator>
                                </ProbeResidual>
                                <RelOp AvgRowSize="20" EstimateCPU="1.02806" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5321.06" LogicalOp="Inner Join" NodeId="5" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="19.6618">
                                  <OutputList>
                                    <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[Part]" Column="PartNum" />
                                  </OutputList>
                                  <MemoryFractions Input="0.384615" Output="0.528321" />
                                  <Hash>
                                    <DefinedValues />
                                    <HashKeysBuild>
                                      <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[Part]" Column="PartNum" />
                                    </HashKeysBuild>
                                    <HashKeysProbe>
                                      <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[Part]" Column="PartNum" />
                                    </HashKeysProbe>
                                    <ProbeResidual>
                                      <ScalarOperator ScalarString="[Epicor905].[dbo].[Part].[PartNum]=[Epicor905].[dbo].[Part].[PartNum] AND (([Expr1005]+[Expr1011])-[Expr1012])&lt;(0.00000000)">
                                        <Logical Operation="AND">
                                          <ScalarOperator>
                                            <Compare CompareOp="EQ">
                                              <ScalarOperator>
                                                <Identifier>
                                                  <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[Part]" Column="PartNum" />
                                                </Identifier>
                                              </ScalarOperator>
                                              <ScalarOperator>
                                                <Identifier>
                                                  <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[Part]" Column="PartNum" />
                                                </Identifier>
                                              </ScalarOperator>
                                            </Compare>
                                          </ScalarOperator>
                                          <ScalarOperator>
                                            <Compare CompareOp="LT">
                                              <ScalarOperator>
                                                <Arithmetic Operation="SUB">
                                                  <ScalarOperator>
                                                    <Arithmetic Operation="ADD">
                                                      <ScalarOperator>
                                                        <Identifier>
                                                          <ColumnReference Column="Expr1005" />
                                                        </Identifier>
                                                      </ScalarOperator>
                                                      <ScalarOperator>
                                                        <Identifier>
                                                          <ColumnReference Column="Expr1011" />
                                                        </Identifier>
                                                      </ScalarOperator>
                                                    </Arithmetic>
                                                  </ScalarOperator>
                                                  <ScalarOperator>
                                                    <Identifier>
                                                      <ColumnReference Column="Expr1012" />
                                                    </Identifier>
                                                  </ScalarOperator>
                                                </Arithmetic>
                                              </ScalarOperator>
                                              <ScalarOperator>
                                                <Const ConstValue="(0.00000000)" />
                                              </ScalarOperator>
                                            </Compare>
                                          </ScalarOperator>
                                        </Logical>
                                      </ScalarOperator>
                                    </ProbeResidual>
                                    <RelOp AvgRowSize="37" EstimateCPU="0.0235022" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="20811.9" LogicalOp="Compute Scalar" NodeId="6" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="5.86434">
                                      <OutputList>
                                        <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[Part]" Column="PartNum" />
                                        <ColumnReference Column="Expr1005" />
                                      </OutputList>
                                      <ComputeScalar>
                                        <DefinedValues>
                                          <DefinedValue>
                                            <ColumnReference Column="Expr1005" />
                                            <ScalarOperator ScalarString="CASE WHEN [Expr1057]=(0) THEN NULL ELSE [Expr1058] END">
                                              <IF>
                                                <Condition>
                                                  <ScalarOperator>
                                                    <Compare CompareOp="EQ">
                                                      <ScalarOperator>
                                                        <Identifier>
                                                          <ColumnReference Column="Expr1057" />
                                                        </Identifier>
                                                      </ScalarOperator>
                                                      <ScalarOperator>
                                                        <Const ConstValue="(0)" />
                                                      </ScalarOperator>
                                                    </Compare>
                                                  </ScalarOperator>
                                                </Condition>
                                                <Then>
                                                  <ScalarOperator>
                                                    <Const ConstValue="NULL" />
                                                  </ScalarOperator>
                                                </Then>
                                                <Else>
                                                  <ScalarOperator>
                                                    <Identifier>
                                                      <ColumnReference Column="Expr1058" />
                                                    </Identifier>
                                                  </ScalarOperator>
                                                </Else>
                                              </IF>
                                            </ScalarOperator>
                                          </DefinedValue>
                                        </DefinedValues>
                                        <RelOp AvgRowSize="37" EstimateCPU="0.0235022" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="20811.9" LogicalOp="Aggregate" NodeId="7" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="5.86434">
                                          <OutputList>
                                            <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[Part]" Column="PartNum" />
                                            <ColumnReference Column="Expr1057" />
                                            <ColumnReference Column="Expr1058" />
                                          </OutputList>
                                          <StreamAggregate>
                                            <DefinedValues>
                                              <DefinedValue>
                                                <ColumnReference Column="Expr1057" />
                                                <ScalarOperator ScalarString="COUNT_BIG([Epicor905].[dbo].[PartWhse].[OnHandQty] as [b].[OnHandQty])">
                                                  <Aggregate AggType="COUNT_BIG" Distinct="false">
                                                    <ScalarOperator>
                                                      <Identifier>
                                                        <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartWhse]" Alias="[b]" Column="OnHandQty" />
                                                      </Identifier>
                                                    </ScalarOperator>
                                                  </Aggregate>
                                                </ScalarOperator>
                                              </DefinedValue>
                                              <DefinedValue>
                                                <ColumnReference Column="Expr1058" />
                                                <ScalarOperator ScalarString="SUM([Epicor905].[dbo].[PartWhse].[OnHandQty] as [b].[OnHandQty])">
                                                  <Aggregate AggType="SUM" Distinct="false">
                                                    <ScalarOperator>
                                                      <Identifier>
                                                        <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartWhse]" Alias="[b]" Column="OnHandQty" />
                                                      </Identifier>
                                                    </ScalarOperator>
                                                  </Aggregate>
                                                </ScalarOperator>
                                              </DefinedValue>
                                            </DefinedValues>
                                            <GroupBy>
                                              <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[Part]" Column="PartNum" />
                                            </GroupBy>
                                            <RelOp AvgRowSize="33" EstimateCPU="0.158188" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="21827.1" LogicalOp="Inner Join" NodeId="8" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="5.84084">
                                              <OutputList>
                                                <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[Part]" Column="PartNum" />
                                                <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartWhse]" Alias="[b]" Column="OnHandQty" />
                                              </OutputList>
                                              <Merge ManyToMany="false">
                                                <InnerSideJoinColumns>
                                                  <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartWhse]" Alias="[b]" Column="PartNum" />
                                                </InnerSideJoinColumns>
                                                <OuterSideJoinColumns>
                                                  <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[Part]" Column="PartNum" />
                                                </OuterSideJoinColumns>
                                                <Residual>
                                                  <ScalarOperator ScalarString="[Epicor905].[dbo].[Part].[PartNum]=[Epicor905].[dbo].[PartWhse].[PartNum] as [b].[PartNum]">
                                                    <Compare CompareOp="EQ">
                                                      <ScalarOperator>
                                                        <Identifier>
                                                          <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[Part]" Column="PartNum" />
                                                        </Identifier>
                                                      </ScalarOperator>
                                                      <ScalarOperator>
                                                        <Identifier>
                                                          <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartWhse]" Alias="[b]" Column="PartNum" />
                                                        </Identifier>
                                                      </ScalarOperator>
                                                    </Compare>
                                                  </ScalarOperator>
                                                </Residual>
                                                <RelOp AvgRowSize="20" EstimateCPU="0.0271213" EstimateIO="0.114603" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="24513" LogicalOp="Index Seek" NodeId="9" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.141724" TableCardinality="98055">
                                                  <OutputList>
                                                    <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[Part]" Column="PartNum" />
                                                  </OutputList>
                                                  <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false">
                                                    <DefinedValues>
                                                      <DefinedValue>
                                                        <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[Part]" Column="PartNum" />
                                                      </DefinedValue>
                                                    </DefinedValues>
                                                    <Object Database="[Epicor905]" Schema="[dbo]" Table="[Part]" Index="[TypePart]" TableReferenceId="1" IndexKind="NonClustered" />
                                                    <SeekPredicates>
                                                      <SeekPredicateNew>
                                                        <SeekKeys>
                                                          <Prefix ScanType="EQ">
                                                            <RangeColumns>
                                                              <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[Part]" Column="Company" />
                                                              <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[Part]" Column="TypeCode" />
                                                            </RangeColumns>
                                                            <RangeExpressions>
                                                              <ScalarOperator ScalarString="'Bruce'">
                                                                <Const ConstValue="'Bruce'" />
                                                              </ScalarOperator>
                                                              <ScalarOperator ScalarString="'P'">
                                                                <Const ConstValue="'P'" />
                                                              </ScalarOperator>
                                                            </RangeExpressions>
                                                          </Prefix>
                                                        </SeekKeys>
                                                      </SeekPredicateNew>
                                                    </SeekPredicates>
                                                  </IndexScan>
                                                </RelOp>
                                                <RelOp AvgRowSize="33" EstimateCPU="0.0519766" EstimateIO="5.48894" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="47108.7" LogicalOp="Clustered Index Seek" NodeId="10" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="5.54092" TableCardinality="135573">
                                                  <OutputList>
                                                    <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartWhse]" Alias="[b]" Column="PartNum" />
                                                    <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartWhse]" Alias="[b]" Column="OnHandQty" />
                                                  </OutputList>
                                                  <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false">
                                                    <DefinedValues>
                                                      <DefinedValue>
                                                        <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartWhse]" Alias="[b]" Column="PartNum" />
                                                      </DefinedValue>
                                                      <DefinedValue>
                                                        <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartWhse]" Alias="[b]" Column="OnHandQty" />
                                                      </DefinedValue>
                                                    </DefinedValues>
                                                    <Object Database="[Epicor905]" Schema="[dbo]" Table="[PartWhse]" Index="[PartNumWarehouseCode]" Alias="[b]" IndexKind="Clustered" />
                                                    <SeekPredicates>
                                                      <SeekPredicateNew>
                                                        <SeekKeys>
                                                          <Prefix ScanType="EQ">
                                                            <RangeColumns>
                                                              <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartWhse]" Alias="[b]" Column="Company" />
                                                            </RangeColumns>
                                                            <RangeExpressions>
                                                              <ScalarOperator ScalarString="'Bruce'">
                                                                <Const ConstValue="'Bruce'" />
                                                              </ScalarOperator>
                                                            </RangeExpressions>
                                                          </Prefix>
                                                        </SeekKeys>
                                                      </SeekPredicateNew>
                                                    </SeekPredicates>
                                                  </IndexScan>
                                                </RelOp>
                                              </Merge>
                                            </RelOp>
                                          </StreamAggregate>
                                        </RelOp>
                                      </ComputeScalar>
                                    </RelOp>
                                    <RelOp AvgRowSize="54" EstimateCPU="1.7495" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="23817.3" LogicalOp="Compute Scalar" NodeId="20" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="12.7694">
                                      <OutputList>
                                        <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[Part]" Column="PartNum" />
                                        <ColumnReference Column="Expr1011" />
                                        <ColumnReference Column="Expr1012" />
                                      </OutputList>
                                      <ComputeScalar>
                                        <DefinedValues>
                                          <DefinedValue>
                                            <ColumnReference Column="Expr1011" />
                                            <ScalarOperator ScalarString="CASE WHEN [Expr1059]=(0) THEN NULL ELSE [Expr1060] END">
                                              <IF>
                                                <Condition>
                                                  <ScalarOperator>
                                                    <Compare CompareOp="EQ">
                                                      <ScalarOperator>
                                                        <Identifier>
                                                          <ColumnReference Column="Expr1059" />
                                                        </Identifier>
                                                      </ScalarOperator>
                                                      <ScalarOperator>
                                                        <Const ConstValue="(0)" />
                                                      </ScalarOperator>
                                                    </Compare>
                                                  </ScalarOperator>
                                                </Condition>
                                                <Then>
                                                  <ScalarOperator>
                                                    <Const ConstValue="NULL" />
                                                  </ScalarOperator>
                                                </Then>
                                                <Else>
                                                  <ScalarOperator>
                                                    <Identifier>
                                                      <ColumnReference Column="Expr1060" />
                                                    </Identifier>
                                                  </ScalarOperator>
                                                </Else>
                                              </IF>
                                            </ScalarOperator>
                                          </DefinedValue>
                                          <DefinedValue>
                                            <ColumnReference Column="Expr1012" />
                                            <ScalarOperator ScalarString="CASE WHEN [Expr1061]=(0) THEN NULL ELSE [Expr1062] END">
                                              <IF>
                                                <Condition>
                                                  <ScalarOperator>
                                                    <Compare CompareOp="EQ">
                                                      <ScalarOperator>
                                                        <Identifier>
                                                          <ColumnReference Column="Expr1061" />
                                                        </Identifier>
                                                      </ScalarOperator>
                                                      <ScalarOperator>
                                                        <Const ConstValue="(0)" />
                                                      </ScalarOperator>
                                                    </Compare>
                                                  </ScalarOperator>
                                                </Condition>
                                                <Then>
                                                  <ScalarOperator>
                                                    <Const ConstValue="NULL" />
                                                  </ScalarOperator>
                                                </Then>
                                                <Else>
                                                  <ScalarOperator>
                                                    <Identifier>
                                                      <ColumnReference Column="Expr1062" />
                                                    </Identifier>
                                                  </ScalarOperator>
                                                </Else>
                                              </IF>
                                            </ScalarOperator>
                                          </DefinedValue>
                                        </DefinedValues>
                                        <RelOp AvgRowSize="54" EstimateCPU="1.7495" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="23817.3" LogicalOp="Aggregate" NodeId="21" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="12.7694">
                                          <OutputList>
                                            <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[Part]" Column="PartNum" />
                                            <ColumnReference Column="Expr1059" />
                                            <ColumnReference Column="Expr1060" />
                                            <ColumnReference Column="Expr1061" />
                                            <ColumnReference Column="Expr1062" />
                                          </OutputList>
                                          <MemoryFractions Input="0.241026" Output="0.331081" />
                                          <Hash>
                                            <DefinedValues>
                                              <DefinedValue>
                                                <ColumnReference Column="Expr1059" />
                                                <ScalarOperator ScalarString="COUNT_BIG([Expr1032])">
                                                  <Aggregate AggType="COUNT_BIG" Distinct="false">
                                                    <ScalarOperator>
                                                      <Identifier>
                                                        <ColumnReference Column="Expr1032" />
                                                      </Identifier>
                                                    </ScalarOperator>
                                                  </Aggregate>
                                                </ScalarOperator>
                                              </DefinedValue>
                                              <DefinedValue>
                                                <ColumnReference Column="Expr1060" />
                                                <ScalarOperator ScalarString="SUM([Expr1032])">
                                                  <Aggregate AggType="SUM" Distinct="false">
                                                    <ScalarOperator>
                                                      <Identifier>
                                                        <ColumnReference Column="Expr1032" />
                                                      </Identifier>
                                                    </ScalarOperator>
                                                  </Aggregate>
                                                </ScalarOperator>
                                              </DefinedValue>
                                              <DefinedValue>
                                                <ColumnReference Column="Expr1061" />
                                                <ScalarOperator ScalarString="COUNT_BIG([Expr1033])">
                                                  <Aggregate AggType="COUNT_BIG" Distinct="false">
                                                    <ScalarOperator>
                                                      <Identifier>
                                                        <ColumnReference Column="Expr1033" />
                                                      </Identifier>
                                                    </ScalarOperator>
                                                  </Aggregate>
                                                </ScalarOperator>
                                              </DefinedValue>
                                              <DefinedValue>
                                                <ColumnReference Column="Expr1062" />
                                                <ScalarOperator ScalarString="SUM([Expr1033])">
                                                  <Aggregate AggType="SUM" Distinct="false">
                                                    <ScalarOperator>
                                                      <Identifier>
                                                        <ColumnReference Column="Expr1033" />
                                                      </Identifier>
                                                    </ScalarOperator>
                                                  </Aggregate>
                                                </ScalarOperator>
                                              </DefinedValue>
                                            </DefinedValues>
                                            <HashKeysBuild>
                                              <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[Part]" Column="PartNum" />
                                            </HashKeysBuild>
                                            <BuildResidual>
                                              <ScalarOperator ScalarString="[Epicor905].[dbo].[Part].[PartNum] = [Epicor905].[dbo].[Part].[PartNum]">
                                                <Compare CompareOp="IS">
                                                  <ScalarOperator>
                                                    <Identifier>
                                                      <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[Part]" Column="PartNum" />
                                                    </Identifier>
                                                  </ScalarOperator>
                                                  <ScalarOperator>
                                                    <Identifier>
                                                      <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[Part]" Column="PartNum" />
                                                    </Identifier>
                                                  </ScalarOperator>
                                                </Compare>
                                              </ScalarOperator>
                                            </BuildResidual>
                                            <RelOp AvgRowSize="46" EstimateCPU="0.00908534" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="90853.4" LogicalOp="Compute Scalar" NodeId="22" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="11.0199">
                                              <OutputList>
                                                <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[Part]" Column="PartNum" />
                                                <ColumnReference Column="Expr1032" />
                                                <ColumnReference Column="Expr1033" />
                                              </OutputList>
                                              <ComputeScalar>
                                                <DefinedValues>
                                                  <DefinedValue>
                                                    <ColumnReference Column="Expr1032" />
                                                    <ScalarOperator ScalarString="CASE WHEN [Epicor905].[dbo].[PartDtl].[RequirementFlag] as [d].[RequirementFlag]=(0) THEN [Epicor905].[dbo].[PartDtl].[Quantity] as [d].[Quantity] ELSE (0.00000000) END">
                                                      <IF>
                                                        <Condition>
                                                          <ScalarOperator>
                                                            <Compare CompareOp="EQ">
                                                              <ScalarOperator>
                                                                <Identifier>
                                                                  <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartDtl]" Alias="[d]" Column="RequirementFlag" />
                                                                </Identifier>
                                                              </ScalarOperator>
                                                              <ScalarOperator>
                                                                <Const ConstValue="(0)" />
                                                              </ScalarOperator>
                                                            </Compare>
                                                          </ScalarOperator>
                                                        </Condition>
                                                        <Then>
                                                          <ScalarOperator>
                                                            <Identifier>
                                                              <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartDtl]" Alias="[d]" Column="Quantity" />
                                                            </Identifier>
                                                          </ScalarOperator>
                                                        </Then>
                                                        <Else>
                                                          <ScalarOperator>
                                                            <Const ConstValue="(0.00000000)" />
                                                          </ScalarOperator>
                                                        </Else>
                                                      </IF>
                                                    </ScalarOperator>
                                                  </DefinedValue>
                                                  <DefinedValue>
                                                    <ColumnReference Column="Expr1033" />
                                                    <ScalarOperator ScalarString="CASE WHEN [Epicor905].[dbo].[PartDtl].[RequirementFlag] as [d].[RequirementFlag]=(1) THEN [Epicor905].[dbo].[PartDtl].[Quantity] as [d].[Quantity] ELSE (0.00000000) END">
                                                      <IF>
                                                        <Condition>
                                                          <ScalarOperator>
                                                            <Compare CompareOp="EQ">
                                                              <ScalarOperator>
                                                                <Identifier>
                                                                  <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartDtl]" Alias="[d]" Column="RequirementFlag" />
                                                                </Identifier>
                                                              </ScalarOperator>
                                                              <ScalarOperator>
                                                                <Const ConstValue="(1)" />
                                                              </ScalarOperator>
                                                            </Compare>
                                                          </ScalarOperator>
                                                        </Condition>
                                                        <Then>
                                                          <ScalarOperator>
                                                            <Identifier>
                                                              <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartDtl]" Alias="[d]" Column="Quantity" />
                                                            </Identifier>
                                                          </ScalarOperator>
                                                        </Then>
                                                        <Else>
                                                          <ScalarOperator>
                                                            <Const ConstValue="(0.00000000)" />
                                                          </ScalarOperator>
                                                        </Else>
                                                      </IF>
                                                    </ScalarOperator>
                                                  </DefinedValue>
                                                </DefinedValues>
                                                <RelOp AvgRowSize="34" EstimateCPU="1.30515" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="90853.4" LogicalOp="Left Outer Join" NodeId="23" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="11.0108">
                                                  <OutputList>
                                                    <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[Part]" Column="PartNum" />
                                                    <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartDtl]" Alias="[d]" Column="RequirementFlag" />
                                                    <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartDtl]" Alias="[d]" Column="Quantity" />
                                                  </OutputList>
                                                  <MemoryFractions Input="0.615385" Output="0.374359" />
                                                  <Hash>
                                                    <DefinedValues />
                                                    <HashKeysBuild>
                                                      <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[Part]" Column="PartNum" />
                                                    </HashKeysBuild>
                                                    <HashKeysProbe>
                                                      <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartDtl]" Alias="[d]" Column="PartNum" />
                                                    </HashKeysProbe>
                                                    <ProbeResidual>
                                                      <ScalarOperator ScalarString="[Epicor905].[dbo].[Part].[PartNum]=[Epicor905].[dbo].[PartDtl].[PartNum] as [d].[PartNum]">
                                                        <Compare CompareOp="EQ">
                                                          <ScalarOperator>
                                                            <Identifier>
                                                              <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[Part]" Column="PartNum" />
                                                            </Identifier>
                                                          </ScalarOperator>
                                                          <ScalarOperator>
                                                            <Identifier>
                                                              <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartDtl]" Alias="[d]" Column="PartNum" />
                                                            </Identifier>
                                                          </ScalarOperator>
                                                        </Compare>
                                                      </ScalarOperator>
                                                    </ProbeResidual>
                                                    <RelOp AvgRowSize="20" EstimateCPU="0.0271213" EstimateIO="0.114603" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="24513" LogicalOp="Index Seek" NodeId="24" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.141724" TableCardinality="98055">
                                                      <OutputList>
                                                        <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[Part]" Column="PartNum" />
                                                      </OutputList>
                                                      <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false">
                                                        <DefinedValues>
                                                          <DefinedValue>
                                                            <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[Part]" Column="PartNum" />
                                                          </DefinedValue>
                                                        </DefinedValues>
                                                        <Object Database="[Epicor905]" Schema="[dbo]" Table="[Part]" Index="[TypePart]" TableReferenceId="2" IndexKind="NonClustered" />
                                                        <SeekPredicates>
                                                          <SeekPredicateNew>
                                                            <SeekKeys>
                                                              <Prefix ScanType="EQ">
                                                                <RangeColumns>
                                                                  <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[Part]" Column="Company" />
                                                                  <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[Part]" Column="TypeCode" />
                                                                </RangeColumns>
                                                                <RangeExpressions>
                                                                  <ScalarOperator ScalarString="'Bruce'">
                                                                    <Const ConstValue="'Bruce'" />
                                                                  </ScalarOperator>
                                                                  <ScalarOperator ScalarString="'P'">
                                                                    <Const ConstValue="'P'" />
                                                                  </ScalarOperator>
                                                                </RangeExpressions>
                                                              </Prefix>
                                                            </SeekKeys>
                                                          </SeekPredicateNew>
                                                        </SeekPredicates>
                                                      </IndexScan>
                                                    </RelOp>
                                                    <RelOp AvgRowSize="33" EstimateCPU="0.0750727" EstimateIO="9.48882" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="68105.2" LogicalOp="Clustered Index Seek" NodeId="25" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="9.56389" TableCardinality="243402">
                                                      <OutputList>
                                                        <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartDtl]" Alias="[d]" Column="PartNum" />
                                                        <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartDtl]" Alias="[d]" Column="RequirementFlag" />
                                                        <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartDtl]" Alias="[d]" Column="Quantity" />
                                                      </OutputList>
                                                      <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false">
                                                        <DefinedValues>
                                                          <DefinedValue>
                                                            <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartDtl]" Alias="[d]" Column="PartNum" />
                                                          </DefinedValue>
                                                          <DefinedValue>
                                                            <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartDtl]" Alias="[d]" Column="RequirementFlag" />
                                                          </DefinedValue>
                                                          <DefinedValue>
                                                            <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartDtl]" Alias="[d]" Column="Quantity" />
                                                          </DefinedValue>
                                                        </DefinedValues>
                                                        <Object Database="[Epicor905]" Schema="[dbo]" Table="[PartDtl]" Index="[TypPartDate]" Alias="[d]" IndexKind="Clustered" />
                                                        <SeekPredicates>
                                                          <SeekPredicateNew>
                                                            <SeekKeys>
                                                              <Prefix ScanType="EQ">
                                                                <RangeColumns>
                                                                  <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartDtl]" Alias="[d]" Column="Company" />
                                                                </RangeColumns>
                                                                <RangeExpressions>
                                                                  <ScalarOperator ScalarString="'Bruce'">
                                                                    <Const ConstValue="'Bruce'" />
                                                                  </ScalarOperator>
                                                                </RangeExpressions>
                                                              </Prefix>
                                                            </SeekKeys>
                                                          </SeekPredicateNew>
                                                        </SeekPredicates>
                                                      </IndexScan>
                                                    </RelOp>
                                                  </Hash>
                                                </RelOp>
                                              </ComputeScalar>
                                            </RelOp>
                                          </Hash>
                                        </RelOp>
                                      </ComputeScalar>
                                    </RelOp>
                                  </Hash>
                                </RelOp>
                                <RelOp AvgRowSize="36" EstimateCPU="0.191065" EstimateIO="1.13259" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="173553" LogicalOp="Index Seek" NodeId="47" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="1.32366" TableCardinality="384010">
                                  <OutputList>
                                    <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartMtl]" Alias="[h]" Column="Company" />
                                    <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartMtl]" Alias="[h]" Column="PartNum" />
                                    <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartMtl]" Alias="[h]" Column="MtlPartNum" />
                                  </OutputList>
                                  <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false">
                                    <DefinedValues>
                                      <DefinedValue>
                                        <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartMtl]" Alias="[h]" Column="Company" />
                                      </DefinedValue>
                                      <DefinedValue>
                                        <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartMtl]" Alias="[h]" Column="PartNum" />
                                      </DefinedValue>
                                      <DefinedValue>
                                        <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartMtl]" Alias="[h]" Column="MtlPartNum" />
                                      </DefinedValue>
                                    </DefinedValues>
                                    <Object Database="[Epicor905]" Schema="[dbo]" Table="[PartMtl]" Index="[WhereUsed]" Alias="[h]" IndexKind="NonClustered" />
                                    <SeekPredicates>
                                      <SeekPredicateNew>
                                        <SeekKeys>
                                          <Prefix ScanType="EQ">
                                            <RangeColumns>
                                              <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartMtl]" Alias="[h]" Column="Company" />
                                            </RangeColumns>
                                            <RangeExpressions>
                                              <ScalarOperator ScalarString="'Bruce'">
                                                <Const ConstValue="'Bruce'" />
                                              </ScalarOperator>
                                            </RangeExpressions>
                                          </Prefix>
                                        </SeekKeys>
                                      </SeekPredicateNew>
                                    </SeekPredicates>
                                  </IndexScan>
                                </RelOp>
                              </Hash>
                            </RelOp>
                          </ComputeScalar>
                        </RelOp>
                      </ComputeScalar>
                    </RelOp>
                    <RelOp AvgRowSize="73" EstimateCPU="0.00367094" EstimateIO="0" EstimateRebinds="43701.6" EstimateRewinds="0" EstimateRows="1.00182" LogicalOp="Assert" NodeId="55" Parallel="false" PhysicalOp="Assert" EstimatedTotalSubtreeCost="39.4443">
                      <OutputList>
                        <ColumnReference Column="Expr1065" />
                        <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartMtl]" Alias="[i]" Column="Company" />
                        <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartMtl]" Alias="[i]" Column="PartNum" />
                        <ColumnReference Column="Expr1027" />
                        <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartMtl]" Alias="[i]" Column="MtlPartNum" />
                      </OutputList>
                      <Assert StartupExpression="false">
                        <RelOp AvgRowSize="73" EstimateCPU="0.00367094" EstimateIO="0" EstimateRebinds="43701.6" EstimateRewinds="0" EstimateRows="1.00182" LogicalOp="Inner Join" NodeId="56" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="39.4443">
                          <OutputList>
                            <ColumnReference Column="Expr1065" />
                            <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartMtl]" Alias="[i]" Column="Company" />
                            <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartMtl]" Alias="[i]" Column="PartNum" />
                            <ColumnReference Column="Expr1027" />
                            <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartMtl]" Alias="[i]" Column="MtlPartNum" />
                          </OutputList>
                          <NestedLoops Optimized="false">
                            <OuterReferences>
                              <ColumnReference Column="Expr1065" />
                              <ColumnReference Column="Recr1023" />
                              <ColumnReference Column="Recr1024" />
                              <ColumnReference Column="Recr1025" />
                              <ColumnReference Column="Recr1026" />
                            </OuterReferences>
                            <RelOp AvgRowSize="73" EstimateCPU="0.000437016" EstimateIO="0" EstimateRebinds="43701.6" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="57" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.000437016">
                              <OutputList>
                                <ColumnReference Column="Expr1065" />
                                <ColumnReference Column="Recr1023" />
                                <ColumnReference Column="Recr1024" />
                                <ColumnReference Column="Recr1025" />
                                <ColumnReference Column="Recr1026" />
                              </OutputList>
                              <ComputeScalar>
                                <DefinedValues>
                                  <DefinedValue>
                                    <ColumnReference Column="Expr1065" />
                                    <ScalarOperator ScalarString="[Expr1064]+(1)">
                                      <Arithmetic Operation="ADD">
                                        <ScalarOperator>
                                          <Identifier>
                                            <ColumnReference Column="Expr1064" />
                                          </Identifier>
                                        </ScalarOperator>
                                        <ScalarOperator>
                                          <Const ConstValue="(1)" />
                                        </ScalarOperator>
                                      </Arithmetic>
                                    </ScalarOperator>
                                  </DefinedValue>
                                </DefinedValues>
                                <RelOp AvgRowSize="73" EstimateCPU="0.000437016" EstimateIO="0" EstimateRebinds="43701.6" EstimateRewinds="0" EstimateRows="1" LogicalOp="Lazy Spool" NodeId="58" Parallel="false" PhysicalOp="Table Spool" EstimatedTotalSubtreeCost="0.000437016">
                                  <OutputList>
                                    <ColumnReference Column="Expr1064" />
                                    <ColumnReference Column="Recr1023" />
                                    <ColumnReference Column="Recr1024" />
                                    <ColumnReference Column="Recr1025" />
                                    <ColumnReference Column="Recr1026" />
                                  </OutputList>
                                  <Spool Stack="true" PrimaryNodeId="0" />
                                </RelOp>
                              </ComputeScalar>
                            </RelOp>
                            <RelOp AvgRowSize="40" EstimateCPU="7.92129E-06" EstimateIO="0" EstimateRebinds="43700.6" EstimateRewinds="0" EstimateRows="79.2129" LogicalOp="Compute Scalar" NodeId="62" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="39.4401">
                              <OutputList>
                                <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartMtl]" Alias="[i]" Column="Company" />
                                <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartMtl]" Alias="[i]" Column="PartNum" />
                                <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartMtl]" Alias="[i]" Column="MtlPartNum" />
                                <ColumnReference Column="Expr1027" />
                              </OutputList>
                              <ComputeScalar>
                                <DefinedValues>
                                  <DefinedValue>
                                    <ColumnReference Column="Expr1027" />
                                    <ScalarOperator ScalarString="[Recr1025]-(1)">
                                      <Arithmetic Operation="SUB">
                                        <ScalarOperator>
                                          <Identifier>
                                            <ColumnReference Column="Recr1025" />
                                          </Identifier>
                                        </ScalarOperator>
                                        <ScalarOperator>
                                          <Const ConstValue="(1)" />
                                        </ScalarOperator>
                                      </Arithmetic>
                                    </ScalarOperator>
                                  </DefinedValue>
                                </DefinedValues>
                                <RelOp AvgRowSize="36" EstimateCPU="0.384354" EstimateIO="14.1888" EstimateRebinds="43700.6" EstimateRewinds="0" EstimateRows="79.2129" LogicalOp="Eager Spool" NodeId="63" Parallel="false" PhysicalOp="Index Spool" EstimatedTotalSubtreeCost="39.094">
                                  <OutputList>
                                    <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartMtl]" Alias="[i]" Column="Company" />
                                    <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartMtl]" Alias="[i]" Column="PartNum" />
                                    <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartMtl]" Alias="[i]" Column="MtlPartNum" />
                                  </OutputList>
                                  <Spool>
                                    <SeekPredicateNew>
                                      <SeekKeys>
                                        <Prefix ScanType="EQ">
                                          <RangeColumns>
                                            <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartMtl]" Alias="[i]" Column="MtlPartNum" />
                                          </RangeColumns>
                                          <RangeExpressions>
                                            <ScalarOperator ScalarString="[Recr1024]">
                                              <Identifier>
                                                <ColumnReference Column="Recr1024" />
                                              </Identifier>
                                            </ScalarOperator>
                                          </RangeExpressions>
                                        </Prefix>
                                      </SeekKeys>
                                    </SeekPredicateNew>
                                    <RelOp AvgRowSize="36" EstimateCPU="0.422568" EstimateIO="2.50312" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="384010" LogicalOp="Index Scan" NodeId="64" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="2.92569" TableCardinality="384010">
                                      <OutputList>
                                        <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartMtl]" Alias="[i]" Column="Company" />
                                        <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartMtl]" Alias="[i]" Column="PartNum" />
                                        <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartMtl]" Alias="[i]" Column="MtlPartNum" />
                                      </OutputList>
                                      <IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false">
                                        <DefinedValues>
                                          <DefinedValue>
                                            <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartMtl]" Alias="[i]" Column="Company" />
                                          </DefinedValue>
                                          <DefinedValue>
                                            <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartMtl]" Alias="[i]" Column="PartNum" />
                                          </DefinedValue>
                                          <DefinedValue>
                                            <ColumnReference Database="[Epicor905]" Schema="[dbo]" Table="[PartMtl]" Alias="[i]" Column="MtlPartNum" />
                                          </DefinedValue>
                                        </DefinedValues>
                                        <Object Database="[Epicor905]" Schema="[dbo]" Table="[PartMtl]" Index="[WhereUsed]" Alias="[i]" IndexKind="NonClustered" />
                                      </IndexScan>
                                    </RelOp>
                                  </Spool>
                                </RelOp>
                              </ComputeScalar>
                            </RelOp>
                          </NestedLoops>
                        </RelOp>
                        <Predicate>
                          <ScalarOperator ScalarString="CASE WHEN [Expr1065]&gt;(100) THEN (0) ELSE NULL END">
                            <IF>
                              <Condition>
                                <ScalarOperator>
                                  <Compare CompareOp="GT">
                                    <ScalarOperator>
                                      <Identifier>
                                        <ColumnReference Column="Expr1065" />
                                      </Identifier>
                                    </ScalarOperator>
                                    <ScalarOperator>
                                      <Const ConstValue="(100)" />
                                    </ScalarOperator>
                                  </Compare>
                                </ScalarOperator>
                              </Condition>
                              <Then>
                                <ScalarOperator>
                                  <Const ConstValue="(0)" />
                                </ScalarOperator>
                              </Then>
                              <Else>
                                <ScalarOperator>
                                  <Const ConstValue="NULL" />
                                </ScalarOperator>
                              </Else>
                            </IF>
                          </ScalarOperator>
                        </Predicate>
                      </Assert>
                    </RelOp>
                  </Concat>
                </RelOp>
              </Spool>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

Schema

\$\endgroup\$
  • 2
    \$\begingroup\$ Welcome to Code Review. This is currently a code dump with no explanation of the goal of the query, and with no information about the schema. Please provide the necessary contextual information so that we can advise you properly. See How to Ask. \$\endgroup\$ – 200_success Aug 15 '18 at 16:24
  • 2
    \$\begingroup\$ Is this sql-server? Please add that tag, if appropriate. Also, if you are having a performance problem, please include the query plan (the output of EXPLAIN). \$\endgroup\$ – 200_success Aug 15 '18 at 16:26
  • \$\begingroup\$ Thanks @200_success. I had no idea what the How to Ask was saying, and did not think it was needed. I'm looking up the stuff that's needed as I have never done them before. I'll add that in once I am able to do it. \$\endgroup\$ – jkane Aug 15 '18 at 17:55
  • \$\begingroup\$ Is there anywhere on the site that explains how to get the information that is needed to ask a good question? I believe I got the schema by running select * from Information_Schema.Tables . But I'm struggling with Explain . Is File Menu->Display->Display Estimated Execution Plan the same thing? @200_success \$\endgroup\$ – jkane Aug 15 '18 at 18:36
  • \$\begingroup\$ Sure, the execution plan would be helpful (and is equivalent to EXPLAIN in other databases). \$\endgroup\$ – 200_success Aug 15 '18 at 19:07
0
\$\begingroup\$

Finally figured it out. Because I did a recursive CTE starting with component materials, I created an infinite loop (at least I think it is infinite, or pretty darn close).

Material Part   Parent Part
a                b
a                b
a                c
b                c

As a quick example, I keep running through the above situation where a Material Part is in Parent Part b & c. So it runs through both of those Parent Parts and then will loop back with b as the Material Part which is in Parent Part c so it will loop through that again unnecessarily. This is why I say I'm not sure if it is infinite. It's doing a ton of redundancy but not sure if there is an end.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.