Query execution time doesn't make sense: Individual CTEs run (the second with pre-generated dat-CodePudding

I have a series of CTE's that each on their own (when using pre-generated data) execute in under a second, that when chained as follows execute in over an hour:

Segments->UnstructuredJobsPre->UnstructuredJobs->Jobs

Running everything up to Jobs, without Jobs itself, is fine; running Jobs on its own, with exactly the data generated from the previous, is fine; and these two permutations run in under a second each; but together they're well over an hour.

Additionally, running [pre-generated]->UnstructuredJobs->Jobs also takes less than a second or two; but running [pregenerated]->UnstructuredJobsPre->UnstructuredJobs->Jobs takes a long time as well, leading me to believe it's some kind of interaction between UJP and Jobs.

Finally, I have a parallel query beginning from the same point and following analogous logic that runs perfectly, start to finish.

The main query relies on an API to get the data and I'm limited in what I can post, so what follows is, in order, the whole query, the parallel query and the pre-generated Jobs query. I'm happy to post the query plans as well, but both are well over the character count in and of themselves. If there's any easy way to do this, I'll put them up right away.

Let me know if you have any ideas! I am at a complete loss...

Whole query:

DECLARE @Dwg TABLE (Num VARCHAR(MAX))
INSERT INTO @Dwg (Num) VALUES
    ('I03SOV3E215921C')

; WITH Segments AS (
    SELECT DISTINCT
        Dwg.Num 'Segment'
        , MI.Item 'Assembly'
        , Mrp.Job
    FROM @Dwg Dwg
    INNER JOIN vgMfiItems MI ON Dwg.Num = MI.DrawingNumber
    INNER JOIN Mrpres Mrp ON MI.Item = Mrp.Item
    INNER JOIN vgJcoWorkOrder WO ON Mrp.Job = WO.Job
    WHERE
        WO.WorkOrderStatus <> 'CLOSED'
        AND WO.WorkOrderStatus <> 'CANCELLED'
)
, UnstructuredJobsPre AS (
    SELECT DISTINCT
        S.Segment
        , S.Job 'Root'
        , WOT.TreeviewOrder
        , WOT.TreeViewOrderParent
        , WOT.ChildValue 'Child'
        , WOT.ChildId
        , WOT.LineLevel
    FROM Segments S
    CROSS APPLY fnJcoWorkOrderTreeView(S.Job) WOT
)
, UnstructuredJobs AS (
    SELECT DISTINCT
        UJ.Segment
        , UJ.Root
        , UJ.TreeviewOrder
        , UJ.Child
        , MrpR.Qte_Ass 'Qty'
        , CASE WHEN UJP.LineLevel > 5
            THEN UJP.TreeViewOrderParent
            ELSE UJ.TreeViewOrderParent
        END 'TreeViewOrderParent'
        , UJ.LineLevel
        , UJ.WOFlag
    FROM (
        SELECT DISTINCT
            UJP.Segment
            , UJP.Root
            , UJP.TreeviewOrder
            , UJP.TreeViewOrderParent
            , CASE WHEN UJP.Child LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'
                THEN UJP.Child
                ELSE WO.Item
            END 'Child'
            , UJP.ChildId
            , UJP.LineLevel
            , CASE WHEN UJP.Child LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'
                THEN 0
                ELSE 1
            END 'WOFlag'
        FROM UnstructuredJobsPre UJP
        LEFT JOIN vgJcoWorkOrder WO ON UJP.Child = WO.WorkOrder
        WHERE
            UJP.LineLevel = 5 OR UJP.LineLevel > 5 AND (UJP.LineLevel   1) % 3 = 0
            AND (
                UJP.Child LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'
                OR UJP.Child LIKE '[0-9][0-9][0-9][0-9][0-9]' 
                OR UJP.Child LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]'
            )
    ) UJ
    LEFT JOIN (
        SELECT DISTINCT
            Pre.Root
            , Pre.TreeviewOrder
            , Pre.TreeViewOrderParent
            , Pre.Child
            , Pre.ChildId
            , Pre.LineLevel
        FROM UnstructuredJobsPre Pre
        WHERE Pre.LineLevel > 5 AND (Pre.LineLevel   2) % 3 = 0
    ) UJP
        ON UJ.Root = UJP.Root
        AND UJ.TreeViewOrderParent = UJP.TreeviewOrder
    INNER JOIN vgMRPAllocations MrpA
        ON UJ.Root = MrpA.JobCode
        AND UJ.ChildId = MrpA.Link
    INNER JOIN Mrpres MrpR
        ON UJ.Root = MrpR.Job
        AND UJ.Child = MrpR.Item
    WHERE
        MrpA.IsActive = 1
)
, Jobs AS (
    SELECT DISTINCT
        UJ.Segment
        , UJ.Root
        , UJ.TreeviewOrder
        , CAST(NULL AS VARCHAR(MAX)) 'Branch'
        , UJ.Child
        , UJ.Qty
        , UJ.WOFlag
    FROM UnstructuredJobs UJ
    WHERE
        UJ.LineLevel = 5
    UNION ALL
    SELECT
        J.Segment
        , J.Root
        , UJ.TreeviewOrder
        , CASE WHEN J.Branch IS NULL
            THEN J.Child
            ELSE J.Branch   '>'   J.Child
        END 'Branch'
        , UJ.Child
        , UJ.Qty
        , UJ.WOFlag
    FROM Jobs J
    INNER JOIN UnstructuredJobs UJ
        ON J.Root = UJ.Root
        AND J.TreeviewOrder = UJ.TreeViewOrderParent
)
SELECT * FROM Jobs

Jobs query:

DECLARE @Temp TABLE (Segment VARCHAR(MAX), [Root] VARCHAR(MAX), Child VARCHAR(MAX), Qty FLOAT, TreeviewOrder VARCHAR(MAX), TreeViewOrderParent VARCHAR(MAX), LineLevel INTEGER, WOFlag INTEGER)
INSERT INTO @Temp (Segment, [Root], Child, Qty, TreeviewOrder, TreeViewOrderParent, LineLevel, WOFlag) VALUES
    ('I03SOV3E215921C', '41114', '318-0088', '2', '001.002.003.001.005.007', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41114', '352-0051', '1', '001.002.003.001.005.008', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41114', '174-0332', '2', '001.002.003.001.005.009', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41114', '172-0178', '1', '001.002.003.001.005.010', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41114', '164-0252', '1', '001.002.003.001.005.011', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41114', '164-0251', '2', '001.002.003.001.005.012', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41114', '267-0061', '1', '001.002.003.001.005.013', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41114', '148-0454', '1', '001.002.003.001.005.014', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41114', '171-0095', '1', '001.002.003.001.005.015', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41114', '197-0105', '1', '001.002.003.001.005.016', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41114', '355-0002', '1', '001.002.003.001.005.017', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41114', '354-0002', '1', '001.002.003.001.005.018', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41114', '180-0052', '1', '001.002.003.001.005.019', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41114', '162-0269', '1', '001.002.003.001.005.020', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41114', '287-0009', '10', '001.002.003.001.005.021', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41114', '108-1095', '1', '001.002.003.001.005.022', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41114', '254-0002', '1', '001.002.003.001.005.023', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41114', '254-0003', '1', '001.002.003.001.005.024', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41114', '171-0092', '1', '001.002.003.001.005.025', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41114', '164-0239', '1', '001.002.003.001.005.026', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41114', '165-0614', '1', '001.002.003.001.005.027', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41114', '358-0005', '1', '001.002.003.001.005.028', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41114', '328-0100', '1', '001.002.003.001.005.029', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41114', '153-0599', '0.5', '001.002.003.001.005.030', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41114', '116-0030', '1', '001.002.003.001.005.031', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41114', '197-0099', '1', '001.002.003.001.005.032', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41114', '165-0607', '1', '001.002.003.001.005.033', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41114', '328-0098', '1', '001.002.003.001.005.034', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41114', '182-0700', '1', '001.002.003.001.005.035', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41114', '155-0005', '1', '001.002.003.001.005.036', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41118', '318-0088', '2', '001.002.003.001.005.001', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41118', '352-0051', '1', '001.002.003.001.005.002', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41118', '174-0332', '2', '001.002.003.001.005.003', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41118', '172-0178', '1', '001.002.003.001.005.004', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41118', '164-0252', '1', '001.002.003.001.005.005', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41118', '164-0251', '2', '001.002.003.001.005.006', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41118', '153-0599', '0.5', '001.002.003.001.005.007', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41118', '267-0061', '1', '001.002.003.001.005.008', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41118', '148-0454', '1', '001.002.003.001.005.009', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41118', '171-0095', '1', '001.002.003.001.005.010', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41118', '197-0105', '1', '001.002.003.001.005.011', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41118', '355-0002', '1', '001.002.003.001.005.012', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41118', '354-0002', '1', '001.002.003.001.005.013', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41118', '180-0052', '1', '001.002.003.001.005.014', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41118', '162-0269', '1', '001.002.003.001.005.015', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41118', '287-0009', '10', '001.002.003.001.005.016', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41118', '108-1095', '1', '001.002.003.001.005.017', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41118', '254-0002', '1', '001.002.003.001.005.018', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41118', '254-0003', '1', '001.002.003.001.005.019', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41118', '171-0092', '1', '001.002.003.001.005.020', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41118', '164-0239', '1', '001.002.003.001.005.021', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41118', '165-0614', '1', '001.002.003.001.005.022', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41118', '328-0100', '1', '001.002.003.001.005.023', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41118', '116-0030', '1', '001.002.003.001.005.025', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41118', '197-0099', '1', '001.002.003.001.005.026', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41118', '165-0607', '1', '001.002.003.001.005.027', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41118', '328-0098', '1', '001.002.003.001.005.028', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41118', '182-0700', '1', '001.002.003.001.005.029', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41118', '155-0005', '1', '001.002.003.001.005.030', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41118', '358-0005', '1', '001.002.003.001.005.031', '001.002.003.001', '5', '1')
    , ('I03SOV3E215921C', '41118', '153-0702', '2.375', '001.002.003.001.005.031.006.001.008.006', '001.002.003.001.005.031', '8', '0')
    , ('I03SOV3E215921C', '41119', '318-0088', '2', '001.002.003.001.005.001', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41119', '352-0051', '1', '001.002.003.001.005.002', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41119', '174-0332', '2', '001.002.003.001.005.003', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41119', '172-0178', '1', '001.002.003.001.005.004', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41119', '164-0252', '1', '001.002.003.001.005.005', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41119', '164-0251', '2', '001.002.003.001.005.006', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41119', '153-0599', '0.5', '001.002.003.001.005.007', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41119', '267-0061', '1', '001.002.003.001.005.008', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41119', '197-0105', '1', '001.002.003.001.005.009', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41119', '355-0002', '1', '001.002.003.001.005.010', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41119', '354-0002', '1', '001.002.003.001.005.011', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41119', '287-0009', '10', '001.002.003.001.005.012', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41119', '254-0002', '1', '001.002.003.001.005.013', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41119', '254-0003', '1', '001.002.003.001.005.014', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41119', '171-0092', '1', '001.002.003.001.005.015', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41119', '164-0239', '1', '001.002.003.001.005.016', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41119', '165-0614', '1', '001.002.003.001.005.017', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41119', '328-0100', '1', '001.002.003.001.005.018', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41119', '116-0030', '1', '001.002.003.001.005.020', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41119', '197-0099', '1', '001.002.003.001.005.021', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41119', '165-0607', '1', '001.002.003.001.005.022', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41119', '328-0098', '1', '001.002.003.001.005.023', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41119', '182-0700', '1', '001.002.003.001.005.024', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41119', '155-0005', '1', '001.002.003.001.005.025', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41119', '148-0454', '1', '001.002.003.001.005.026', '001.002.003.001', '5', '1')
    , ('I03SOV3E215921C', '41119', '153-0718', '2.5', '001.002.003.001.005.026.006.001.008.004', '001.002.003.001.005.026', '8', '0')
    , ('I03SOV3E215921C', '41119', '153-0718', '7', '001.002.003.001.005.026.006.001.008.004', '001.002.003.001.005.026', '8', '0')
    , ('I03SOV3E215921C', '41119', '171-0095', '1', '001.002.003.001.005.027', '001.002.003.001', '5', '1')
    , ('I03SOV3E215921C', '41119', '153-0718', '2.5', '001.002.003.001.005.027.006.001.008.005', '001.002.003.001.005.027', '8', '0')
    , ('I03SOV3E215921C', '41119', '153-0718', '7', '001.002.003.001.005.027.006.001.008.005', '001.002.003.001.005.027', '8', '0')
    , ('I03SOV3E215921C', '41119', '180-0052', '1', '001.002.003.001.005.028', '001.002.003.001', '5', '1')
    , ('I03SOV3E215921C', '41119', '153-0469', '2', '001.002.003.001.005.028.006.001.008.005', '001.002.003.001.005.028', '8', '0')
    , ('I03SOV3E215921C', '41119', '162-0269', '1', '001.002.003.001.005.029', '001.002.003.001', '5', '1')
    , ('I03SOV3E215921C', '41119', '153-0705', '2', '001.002.003.001.005.029.006.001.008.004', '001.002.003.001.005.029', '8', '0')
    , ('I03SOV3E215921C', '41119', '108-1095', '1', '001.002.003.001.005.030', '001.002.003.001', '5', '1')
    , ('I03SOV3E215921C', '41119', '169-0228', '1', '001.002.003.001.005.030.006.001.008.010', '001.002.003.001.005.030', '8', '0')
    , ('I03SOV3E215921C', '41119', '358-0005', '1', '001.002.003.001.005.031', '001.002.003.001', '5', '1')
    , ('I03SOV3E215921C', '41119', '153-0702', '2.375', '001.002.003.001.005.031.006.001.008.006', '001.002.003.001.005.031', '8', '0')
    , ('I03SOV3E215921C', '41121', '318-0088', '2', '001.002.003.001.005.001', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41121', '352-0051', '1', '001.002.003.001.005.002', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41121', '174-0332', '2', '001.002.003.001.005.003', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41121', '172-0178', '1', '001.002.003.001.005.004', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41121', '164-0252', '1', '001.002.003.001.005.005', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41121', '164-0251', '2', '001.002.003.001.005.006', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41121', '153-0599', '0.5', '001.002.003.001.005.007', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41121', '197-0099', '1', '001.002.003.001.005.008', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41121', '267-0061', '1', '001.002.003.001.005.009', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41121', '148-0454', '1', '001.002.003.001.005.010', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41121', '171-0095', '1', '001.002.003.001.005.011', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41121', '197-0105', '1', '001.002.003.001.005.012', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41121', '355-0002', '1', '001.002.003.001.005.013', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41121', '354-0002', '1', '001.002.003.001.005.014', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41121', '180-0052', '1', '001.002.003.001.005.015', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41121', '287-0009', '10', '001.002.003.001.005.016', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41121', '108-1095', '1', '001.002.003.001.005.017', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41121', '254-0002', '1', '001.002.003.001.005.018', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41121', '254-0003', '1', '001.002.003.001.005.019', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41121', '171-0092', '1', '001.002.003.001.005.020', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41121', '164-0239', '1', '001.002.003.001.005.021', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41121', '165-0614', '1', '001.002.003.001.005.022', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41121', '358-0005', '1', '001.002.003.001.005.023', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41121', '328-0100', '1', '001.002.003.001.005.024', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41121', '116-0030', '1', '001.002.003.001.005.026', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41121', '165-0607', '1', '001.002.003.001.005.028', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41121', '328-0098', '1', '001.002.003.001.005.029', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41121', '182-0700', '1', '001.002.003.001.005.030', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41121', '155-0005', '1', '001.002.003.001.005.031', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41121', '162-0269', '1', '001.002.003.001.005.032', '001.002.003.001', '5', '1')
    , ('I03SOV3E215921C', '41121', '153-0705', '2', '001.002.003.001.005.032.006.001.008.004', '001.002.003.001.005.032', '8', '0')
    , ('I03SOV3E215921C', '41123', '174-0332', '2', '001.002.003.001.005.007', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41123', '172-0178', '1', '001.002.003.001.005.008', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41123', '164-0252', '1', '001.002.003.001.005.009', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41123', '164-0251', '2', '001.002.003.001.005.010', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41123', '318-0088', '2', '001.002.003.001.005.012', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41123', '352-0051', '1', '001.002.003.001.005.013', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41123', '267-0061', '1', '001.002.003.001.005.014', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41123', '148-0454', '1', '001.002.003.001.005.015', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41123', '171-0095', '1', '001.002.003.001.005.016', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41123', '197-0105', '1', '001.002.003.001.005.017', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41123', '355-0002', '1', '001.002.003.001.005.018', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41123', '354-0002', '1', '001.002.003.001.005.019', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41123', '180-0052', '1', '001.002.003.001.005.020', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41123', '162-0269', '1', '001.002.003.001.005.021', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41123', '287-0009', '10', '001.002.003.001.005.022', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41123', '108-1095', '1', '001.002.003.001.005.023', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41123', '254-0002', '1', '001.002.003.001.005.024', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41123', '254-0003', '1', '001.002.003.001.005.025', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41123', '171-0092', '1', '001.002.003.001.005.026', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41123', '164-0239', '1', '001.002.003.001.005.027', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41123', '165-0614', '1', '001.002.003.001.005.028', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41123', '358-0005', '1', '001.002.003.001.005.029', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41123', '328-0100', '1', '001.002.003.001.005.030', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41123', '153-0599', '0.5', '001.002.003.001.005.031', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41123', '116-0030', '1', '001.002.003.001.005.032', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41123', '197-0099', '1', '001.002.003.001.005.033', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41123', '165-0607', '1', '001.002.003.001.005.034', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41123', '328-0098', '1', '001.002.003.001.005.035', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41123', '182-0700', '1', '001.002.003.001.005.036', '001.002.003.001', '5', '0')
    , ('I03SOV3E215921C', '41123', '155-0005', '1', '001.002.003.001.005.037', '001.002.003.001', '5', '0')

; WITH Jobs AS (
    SELECT DISTINCT
        UJ.Segment
        , UJ.Root
        , UJ.TreeviewOrder
        , CAST(NULL AS VARCHAR(MAX)) 'Branch'
        , UJ.Child
        , UJ.Qty
        , UJ.WOFlag
    FROM @Temp UJ
    WHERE
        UJ.LineLevel = 5
    UNION ALL
    SELECT
        J.Segment
        , J.Root
        , UJ.TreeviewOrder
        , CASE WHEN J.Branch IS NULL
            THEN J.Child
            ELSE J.Branch   '>'   J.Child
        END 'Branch'
        , UJ.Child
        , UJ.Qty
        , UJ.WOFlag
    FROM Jobs J
    INNER JOIN @Temp UJ
        ON J.Root = UJ.Root
        AND J.TreeviewOrder = UJ.TreeViewOrderParent
)
SELECT * FROM Jobs

Parallel query:

DECLARE @Dwg TABLE (Num VARCHAR(MAX))
INSERT INTO @Dwg (Num) VALUES
    ('I03SOV3E215921C')


; WITH Segments AS (
    SELECT DISTINCT
        Dwg.Num 'Segment'
        , MI.Item 'Assembly'
        , Mrp.Job
    FROM @Dwg Dwg
    INNER JOIN vgMfiItems MI ON Dwg.Num = MI.DrawingNumber
    INNER JOIN Mrpres Mrp ON MI.Item = Mrp.Item
    INNER JOIN vgJcoWorkOrder WO ON Mrp.Job = WO.Job
    WHERE
        WO.WorkOrderStatus <> 'CLOSED'
        AND WO.WorkOrderStatus <> 'CANCELLED'
)
, UnstructuredBom AS (
    SELECT DISTINCT
        S.Segment
        , S.Assembly
        , Bom.TreeViewLevel
        , REVERSE(SUBSTRING(REVERSE(Bom.TreeViewLevel), 6, LEN(Bom.TreeViewLevel))) 'TreeViewLevelParent'
        , Bom.Child
        , Bom.ItemQuantityPerAssembly 'Qty'
        , Bom.ComponentLevel
    FROM Segments S
    CROSS APPLY fnGetBomExplodeTable(S.Assembly, NULL, default, default) Bom
    WHERE
        Bom.ComponentLevel > 0
        AND Bom.Child LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'
)
, Assemblies AS (
    SELECT DISTINCT
        UBom.Segment
        , UBom.Assembly 'Root'
        , UBom.TreeViewLevel
        , CAST(NULL AS VARCHAR(MAX)) 'Branch'
        , UBom.Child
        , UBom.Qty
    FROM UnstructuredBom UBom
    WHERE
        UBom.ComponentLevel = 1
    UNION ALL
    SELECT
        A.Segment
        , A.Root
        , UBom.TreeViewLevel
        , CASE WHEN A.Branch IS NULL
            THEN A.Child
            ELSE A.Branch   '>'   A.Child
        END 'Branch'
        , UBom.Child
        , UBom.Qty
    FROM Assemblies A
    INNER JOIN UnstructuredBom UBom
        ON A.Root = UBom.Assembly
        AND A.TreeViewLevel = UBom.TreeViewLevelParent
)
SELECT * FROM Assemblies

CodePudding user response：

CTEs and subqueries are compiled into one big plan, sometimes not a good one. So either

optimize the query to make the plan better. The obvious first step here is to replace

    INSERT INTO @Dwg (Num) VALUES
        ('I03SOV3E215921C')

with a temp table with a clustered index on Num, as table variables don't have statistics.

Materialize the partial result by loading temp tables, and referencing them in your larger query. EG

    INSERT INTO #UnstructuredBOM (...)
    SELECT DISTINCT
        S.Segment
        , S.Assembly
        , Bom.TreeViewLevel
        , REVERSE(SUBSTRING(REVERSE(Bom.TreeViewLevel), 6, LEN(Bom.TreeViewLevel))) 'TreeViewLevelParent'
        , Bom.Child
        , Bom.ItemQuantityPerAssembly 'Qty'
        , Bom.ComponentLevel
    FROM Segments S
    CROSS APPLY fnGetBomExplodeTable(S.Assembly, NULL, default, default) Bom
    WHERE
        Bom.ComponentLevel > 0
        AND Bom.Child LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'

Then

, UnstructuredBom AS ( select * from #UnstructuredBOM )