Recursive CTE vs UNION ALL in a VIEW

  • Mauricio_

    SSCrazy

    Points: 2931

    Hi,

    I'm trying to improve a VIEW which is made of several UNION ALLs, something like this:

    SELECT 0 as OPNR, nivdop1.nr as DOPNR, 0 as ANTL, (nivdop1.ANT) / sumniv1.SUMANT as ANT, 0 as ANTS, nivdop1.SVIND, nivrv1.nr as RVNR,
    nivrv1.NAVN as RVNAVN, nivrv1.TYPE, 1 as NIVEAU, 0 as PARENT, nivdop1.LINE, 0 as PLINE
    from DBDOPSK nivdop1
    inner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv1 on sumniv1.NR = nivdop1.NR
    inner join DBRVARE nivrv1 on nivrv1.NR = nivdop1.RVARENR
    union all
    SELECT 0 as OPNR, nivdop1.nr as DOPNR, 0 as ANTL, nivdop1.ANT / sumniv1.SUMANT * (nivdop2.ANT) / sumniv2.SUMANT as ANT, 0 as ANTS,
    nivdop2.SVIND, nivrv2.nr as RVNR, nivrv2.NAVN as RVNAVN, nivrv2.TYPE, 2 as NIVEAU, nivdop2.NR as PARENT, nivdop2.LINE, nivdop1.LINE as PLINE
    from DBDOPSK nivdop1
    inner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv1 on sumniv1.NR = nivdop1.NR
    inner join DBRVARE nivrv1 on nivrv1.NR = nivdop1.RVARENR
    inner join DBDOPSK nivdop2 on nivdop2.NR = nivrv1.NR
    inner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv2 on sumniv2.NR = nivdop2.NR
    inner join DBRVARE nivrv2 on nivrv2.NR = nivdop2.RVARENR

    That's just a part of the current view. So I wrote it as a recursive CTE. So far, so good, my view is more readable and when you compare both executions plans, mine has a cost of 28% comparing with the other view. However... in terms of execution time, mine is slower, mainly, I guess, because the use of a worktable with 108643 logical reads.

    This is the execution plan of the view with CTE: https://www.brentozar.com/pastetheplan/?id=BJOpMMP7L

    While this one is the other one: https://www.brentozar.com/pastetheplan/?id=rkGoQMPXU

    Question is, obviously, how to improve the first query (if you think this is the best alternative).

    Thanks in advance.

     

    Mauricio

  • Jonathan AC Roberts

    SSCoach

    Points: 17208

    You can't rely on the percentage figures given in an execution plan. I rely on SET STATISTICS IO, TIME ON and try to minimise the figures given in that.

    Can you paste in the original view and your updated version?

  • Mauricio_

    SSCrazy

    Points: 2931

    I know I can't rely on those percentages, I used STATISTICS and I was confident with that results.

    Here are the views:


    CREATE VIEW [dbo].[RECIPE] AS
    SELECT 0 as OPNR, nivdop1.nr as DOPNR, 0 as ANTL, (nivdop1.ANT) / sumniv1.SUMANT as ANT, 0 as ANTS, nivdop1.SVIND, nivrv1.nr as RVNR,
    nivrv1.NAVN as RVNAVN, nivrv1.TYPE, 1 as NIVEAU, 0 as PARENT, nivdop1.LINE, 0 as PLINE
    from DBDOPSK nivdop1
    inner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv1 on sumniv1.NR = nivdop1.NR
    inner join DBRVARE nivrv1 on nivrv1.NR = nivdop1.RVARENR
    union all
    SELECT 0 as OPNR, nivdop1.nr as DOPNR, 0 as ANTL, nivdop1.ANT / sumniv1.SUMANT * (nivdop2.ANT) / sumniv2.SUMANT as ANT, 0 as ANTS,
    nivdop2.SVIND, nivrv2.nr as RVNR, nivrv2.NAVN as RVNAVN, nivrv2.TYPE, 2 as NIVEAU, nivdop2.NR as PARENT, nivdop2.LINE, nivdop1.LINE as PLINE
    from DBDOPSK nivdop1
    inner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv1 on sumniv1.NR = nivdop1.NR
    inner join DBRVARE nivrv1 on nivrv1.NR = nivdop1.RVARENR
    inner join DBDOPSK nivdop2 on nivdop2.NR = nivrv1.NR
    inner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv2 on sumniv2.NR = nivdop2.NR
    inner join DBRVARE nivrv2 on nivrv2.NR = nivdop2.RVARENR
    union all
    SELECT 0 as OPNR, nivdop1.nr as DOPNR, 0 as ANTL,
    nivdop1.ANT / sumniv1.SUMANT * nivdop2.ANT / sumniv2.SUMANT * (nivdop3.ANT) / sumniv3.SUMANT as ANT, 0 as ANTS,
    nivdop3.SVIND, nivrv3.nr as RVNR, nivrv3.NAVN as RVNAVN, nivrv3.TYPE, 3 as NIVEAU, nivdop3.NR as PARENT, nivdop3.LINE, nivdop2.LINE as PLINE
    from DBDOPSK nivdop1
    inner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv1 on sumniv1.NR = nivdop1.NR
    inner join DBRVARE nivrv1 on nivrv1.NR = nivdop1.RVARENR
    inner join DBDOPSK nivdop2 on nivdop2.NR = nivrv1.NR
    inner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv2 on sumniv2.NR = nivdop2.NR
    inner join DBRVARE nivrv2 on nivrv2.NR = nivdop2.RVARENR
    inner join DBDOPSK nivdop3 on nivdop3.NR = nivrv2.NR
    inner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv3 on sumniv3.NR = nivdop3.NR
    inner join DBRVARE nivrv3 on nivrv3.NR = nivdop3.RVARENR
    union all
    SELECT 0 as OPNR, nivdop1.nr as DOPNR, 0 as ANTL,
    nivdop1.ANT / sumniv1.SUMANT * nivdop2.ANT / sumniv2.SUMANT * nivdop3.ANT / sumniv3.SUMANT * (nivdop4.ANT) / sumniv4.SUMANT as ANT, 0 as ANTS,
    nivdop4.SVIND, nivrv4.nr as RVNR, nivrv4.NAVN as RVNAVN, nivrv4.TYPE, 4 as NIVEAU, nivdop4.NR as PARENT, nivdop4.LINE, nivdop3.LINE as PLINE
    from DBDOPSK nivdop1
    inner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv1 on sumniv1.NR = nivdop1.NR
    inner join DBRVARE nivrv1 on nivrv1.NR = nivdop1.RVARENR
    inner join DBDOPSK nivdop2 on nivdop2.NR = nivrv1.NR
    inner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv2 on sumniv2.NR = nivdop2.NR
    inner join DBRVARE nivrv2 on nivrv2.NR = nivdop2.RVARENR
    inner join DBDOPSK nivdop3 on nivdop3.NR = nivrv2.NR
    inner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv3 on sumniv3.NR = nivdop3.NR
    inner join DBRVARE nivrv3 on nivrv3.NR = nivdop3.RVARENR
    inner join DBDOPSK nivdop4 on nivdop4.NR = nivrv3.NR
    inner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv4 on sumniv4.NR = nivdop4.NR
    inner join DBRVARE nivrv4 on nivrv4.NR = nivdop4.RVARENR
    union all
    SELECT 0 as OPNR, nivdop1.nr as DOPNR, 0 as ANTL,
    nivdop1.ANT / sumniv1.SUMANT * nivdop2.ANT / sumniv2.SUMANT * nivdop3.ANT / sumniv3.SUMANT * nivdop4.ANT / sumniv4.SUMANT *
    (nivdop5.ANT) / sumniv5.SUMANT as ANT, 0 as ANTS,
    nivdop5.SVIND, nivrv5.nr as RVNR, nivrv5.NAVN as RVNAVN, nivrv5.TYPE, 5 as NIVEAU, nivdop5.NR as PARENT, nivdop5.LINE, nivdop4.LINE as PLINE
    from DBDOPSK nivdop1
    inner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv1 on sumniv1.NR = nivdop1.NR
    inner join DBRVARE nivrv1 on nivrv1.NR = nivdop1.RVARENR
    inner join DBDOPSK nivdop2 on nivdop2.NR = nivrv1.NR
    inner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv2 on sumniv2.NR = nivdop2.NR
    inner join DBRVARE nivrv2 on nivrv2.NR = nivdop2.RVARENR
    inner join DBDOPSK nivdop3 on nivdop3.NR = nivrv2.NR
    inner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv3 on sumniv3.NR = nivdop3.NR
    inner join DBRVARE nivrv3 on nivrv3.NR = nivdop3.RVARENR
    inner join DBDOPSK nivdop4 on nivdop4.NR = nivrv3.NR
    inner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv4 on sumniv4.NR = nivdop4.NR
    inner join DBRVARE nivrv4 on nivrv4.NR = nivdop4.RVARENR
    inner join DBDOPSK nivdop5 on nivdop5.NR = nivrv4.NR
    inner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv5 on sumniv5.NR = nivdop5.NR
    inner join DBRVARE nivrv5 on nivrv5.NR = nivdop5.RVARENR
    union all
    SELECT 0 as OPNR, nivdop1.nr as DOPNR, 0 as ANTL,
    nivdop1.ANT / sumniv1.SUMANT * nivdop2.ANT / sumniv2.SUMANT * nivdop3.ANT / sumniv3.SUMANT * nivdop4.ANT / sumniv4.SUMANT * nivdop5.ANT / sumniv5.SUMANT *
    (nivdop6.ANT) / sumniv6.SUMANT as ANT, 0 as ANTS,
    nivdop6.SVIND, nivrv6.nr as RVNR, nivrv6.NAVN as RVNAVN, nivrv6.TYPE, 6 as NIVEAU, nivdop6.NR as PARENT, nivdop6.LINE, nivdop5.LINE as PLINE
    from DBDOPSK nivdop1
    inner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv1 on sumniv1.NR = nivdop1.NR
    inner join DBRVARE nivrv1 on nivrv1.NR = nivdop1.RVARENR
    inner join DBDOPSK nivdop2 on nivdop2.NR = nivrv1.NR
    inner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv2 on sumniv2.NR = nivdop2.NR
    inner join DBRVARE nivrv2 on nivrv2.NR = nivdop2.RVARENR
    inner join DBDOPSK nivdop3 on nivdop3.NR = nivrv2.NR
    inner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv3 on sumniv3.NR = nivdop3.NR
    inner join DBRVARE nivrv3 on nivrv3.NR = nivdop3.RVARENR
    inner join DBDOPSK nivdop4 on nivdop4.NR = nivrv3.NR
    inner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv4 on sumniv4.NR = nivdop4.NR
    inner join DBRVARE nivrv4 on nivrv4.NR = nivdop4.RVARENR
    inner join DBDOPSK nivdop5 on nivdop5.NR = nivrv4.NR
    inner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv5 on sumniv5.NR = nivdop5.NR
    inner join DBRVARE nivrv5 on nivrv5.NR = nivdop5.RVARENR
    inner join DBDOPSK nivdop6 on nivdop6.NR = nivrv5.NR
    inner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv6 on sumniv6.NR = nivdop6.NR
    inner join DBRVARE nivrv6 on nivrv6.NR = nivdop6.RVARENR
    union all
    SELECT OP.VARENR as OPNR, 0 as DOPNR, (OP.ANTL) as ANTL, (OP.ANTN) as ANT,
    (OP.ANTS) as ANTS, OP.SVIND, nivrv1.nr as RVNR, nivrv1.NAVN as RVNAVN, nivrv1.TYPE, 1 as NIVEAU, 0 as PARENT, OP.LINE, 0 as PLINE
    from DBOPSK OP
    inner join DBRVARE nivrv1 on nivrv1.NR = OP.RVARENR
    union all
    SELECT OP.VARENR as OPNR, 0 as DOPNR, OP.ANTL * (nivdop2.ANT) / sumniv2.SUMANT as ANTL,
    OP.ANTN * (nivdop2.ANT) / sumniv2.SUMANT as ANT,
    OP.ANTS * (nivdop2.ANT) / sumniv2.SUMANT as ANTS,
    nivdop2.SVIND, nivrv2.nr as RVNR, nivrv2.NAVN as RVNAVN,
    nivrv2.TYPE, 2 as NIVEAU, nivdop2.NR as PARENT, nivdop2.LINE, op.LINE as PLINE
    from DBOPSK OP
    inner join DBRVARE nivrv1 on nivrv1.NR = OP.RVARENR
    inner join DBDOPSK nivdop2 on nivdop2.NR = nivrv1.NR
    inner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv2 on sumniv2.NR = nivdop2.NR
    inner join DBRVARE nivrv2 on nivrv2.NR = nivdop2.RVARENR
    union all
    SELECT OP.VARENR as OPNR, 0 as DOPNR, OP.ANTL * nivdop2.ANT / sumniv2.SUMANT * (nivdop3.ANT) / sumniv3.SUMANT as ANTL,
    OP.ANTN * nivdop2.ANT / sumniv2.SUMANT * (nivdop3.ANT) / sumniv3.SUMANT as ANT,
    OP.ANTN * nivdop2.ANT / sumniv2.SUMANT * (nivdop3.ANT) / sumniv3.SUMANT as ANTS,
    nivdop3.SVIND, nivrv3.nr as RVNR, nivrv3.NAVN as RVNAVN, nivrv3.TYPE, 3 as NIVEAU, nivdop3.NR as PARENT, nivdop3.LINE, nivdop2.LINE as PLINE
    from DBOPSK OP
    inner join DBRVARE nivrv1 on nivrv1.NR = OP.RVARENR
    inner join DBDOPSK nivdop2 on nivdop2.NR = nivrv1.NR
    inner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv2 on sumniv2.NR = nivdop2.NR
    inner join DBRVARE nivrv2 on nivrv2.NR = nivdop2.RVARENR
    inner join DBDOPSK nivdop3 on nivdop3.NR = nivrv2.NR
    inner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv3 on sumniv3.NR = nivdop3.NR
    inner join DBRVARE nivrv3 on nivrv3.NR = nivdop3.RVARENR
    union all
    SELECT OP.VARENR as OPNR, 0 as DOPNR,
    OP.ANTL * nivdop2.ANT / sumniv2.SUMANT * nivdop3.ANT / sumniv3.SUMANT * (nivdop4.ANT) / sumniv4.SUMANT as ANTL,
    OP.ANTN * nivdop2.ANT / sumniv2.SUMANT * nivdop3.ANT / sumniv3.SUMANT * (nivdop4.ANT) / sumniv4.SUMANT as ANT,
    OP.ANTS * nivdop2.ANT / sumniv2.SUMANT * nivdop3.ANT / sumniv3.SUMANT * (nivdop4.ANT) / sumniv4.SUMANT as ANTS,
    nivdop4.SVIND, nivrv4.nr as RVNR, nivrv4.NAVN as RVNAVN, nivrv4.TYPE, 4 as NIVEAU, nivdop4.NR as PARENT, nivdop4.LINE, nivdop3.LINE as PLINE
    from DBOPSK OP
    inner join DBRVARE nivrv1 on nivrv1.NR = OP.RVARENR
    inner join DBDOPSK nivdop2 on nivdop2.NR = nivrv1.NR
    inner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv2 on sumniv2.NR = nivdop2.NR
    inner join DBRVARE nivrv2 on nivrv2.NR = nivdop2.RVARENR
    inner join DBDOPSK nivdop3 on nivdop3.NR = nivrv2.NR
    inner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv3 on sumniv3.NR = nivdop3.NR
    inner join DBRVARE nivrv3 on nivrv3.NR = nivdop3.RVARENR
    inner join DBDOPSK nivdop4 on nivdop4.NR = nivrv3.NR
    inner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv4 on sumniv4.NR = nivdop4.NR
    inner join DBRVARE nivrv4 on nivrv4.NR = nivdop4.RVARENR
    union all
    SELECT OP.VARENR as OPNR, 0 as DOPNR,
    OP.ANTL * nivdop2.ANT / sumniv2.SUMANT * nivdop3.ANT / sumniv3.SUMANT * nivdop4.ANT / sumniv4.SUMANT * (nivdop5.ANT) / sumniv5.SUMANT as ANTL,
    OP.ANTN * nivdop2.ANT / sumniv2.SUMANT * nivdop3.ANT / sumniv3.SUMANT * nivdop4.ANT / sumniv4.SUMANT * (nivdop5.ANT) / sumniv5.SUMANT as ANT,
    OP.ANTS * nivdop2.ANT / sumniv2.SUMANT * nivdop3.ANT / sumniv3.SUMANT * nivdop4.ANT / sumniv4.SUMANT * (nivdop5.ANT) / sumniv5.SUMANT as ANTS,
    nivdop5.SVIND, nivrv5.nr as RVNR, nivrv5.
    NAVN as RVNAVN, nivrv5.TYPE, 5 as NIVEAU, nivdop5.NR as PARENT, nivdop5.LINE, nivdop4.LINE as PLINE
    from DBOPSK OP
    inner join DBRVARE nivrv1 on nivrv1.NR = OP.RVARENR
    inner join DBDOPSK nivdop2 on nivdop2.NR = nivrv1.NR
    inner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv2 on sumniv2.NR = nivdop2.NR
    inner join DBRVARE nivrv2 on nivrv2.NR = nivdop2.RVARENR
    inner join DBDOPSK nivdop3 on nivdop3.NR = nivrv2.NR
    inner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv3 on sumniv3.NR = nivdop3.NR
    inner join DBRVARE nivrv3 on nivrv3.NR = nivdop3.RVARENR
    inner join DBDOPSK nivdop4 on nivdop4.NR = nivrv3.NR
    inner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv4 on sumniv4.NR = nivdop4.NR
    inner join DBRVARE nivrv4 on nivrv4.NR = nivdop4.RVARENR
    inner join DBDOPSK nivdop5 on nivdop5.NR = nivrv4.NR
    inner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv5 on sumniv5.NR = nivdop5.NR
    inner join DBRVARE nivrv5 on nivrv5.NR = nivdop5.RVARENR
    union all
    SELECT OP.VARENR as OPNR, 0 as DOPNR,
    OP.ANTL * nivdop2.ANT / sumniv2.SUMANT * nivdop3.ANT / sumniv3.SUMANT * nivdop4.ANT / sumniv4.SUMANT * nivdop5.ANT / sumniv5.SUMANT *
    (nivdop6.ANT) / sumniv6.SUMANT as ANTL,
    OP.ANTN * nivdop2.ANT / sumniv2.SUMANT * nivdop3.ANT / sumniv3.SUMANT * nivdop4.ANT / sumniv4.SUMANT * nivdop5.ANT / sumniv5.SUMANT *
    (nivdop6.ANT) / sumniv6.SUMANT as ANT,
    OP.ANTS * nivdop2.ANT / sumniv2.SUMANT * nivdop3.ANT / sumniv3.SUMANT * nivdop4.ANT / sumniv4.SUMANT * nivdop5.ANT / sumniv5.SUMANT *
    (nivdop6.ANT) / sumniv6.SUMANT as ANTS,
    nivdop6.SVIND, nivrv6.nr as RVNR, nivrv6.NAVN as RVNAVN, nivrv6.TYPE, 6 as NIVEAU, nivdop6.NR as PARENT, nivdop6.LINE, nivdop5.LINE as PLINE
    from DBOPSK OP
    inner join DBRVARE nivrv1 on nivrv1.NR = OP.RVARENR
    inner join DBDOPSK nivdop2 on nivdop2.NR = nivrv1.NR
    inner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv2 on sumniv2.NR = nivdop2.NR
    inner join DBRVARE nivrv2 on nivrv2.NR = nivdop2.RVARENR
    inner join DBDOPSK nivdop3 on nivdop3.NR = nivrv2.NR
    inner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv3 on sumniv3.NR = nivdop3.NR
    inner join DBRVARE nivrv3 on nivrv3.NR = nivdop3.RVARENR
    inner join DBDOPSK nivdop4 on nivdop4.NR = nivrv3.NR
    inner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv4 on sumniv4.NR = nivdop4.NR
    inner join DBRVARE nivrv4 on nivrv4.NR = nivdop4.RVARENR
    inner join DBDOPSK nivdop5 on nivdop5.NR = nivrv4.NR
    inner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv5 on sumniv5.NR = nivdop5.NR
    inner join DBRVARE nivrv5 on nivrv5.NR = nivdop5.RVARENR
    inner join DBDOPSK nivdop6 on nivdop6.NR = nivrv5.NR
    inner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv6 on sumniv6.NR = nivdop6.NR
    inner join DBRVARE nivrv6 on nivrv6.NR = nivdop6.RVARENR
    GO

    This is the original.

    CREATE VIEW [dbo].[vRecipe] AS


    WITH RECIPE_DBVARE (OPNR, DOPNR, ANTL, ANT, ANTS, SVIND, RVNR, RVNAVN, TYPE, NIVEAU, PARENT, LINE, PLINE)
    AS
    (
    SELECT VR.NR [OPNR],
    0 [DOPNR],
    CAST(OPS.ANTL AS DECIMAL(28,6)) [ANTL],
    CAST(OPS.ANTN AS DECIMAL(28,6)) [ANT],
    CAST(OPS.ANTS AS DECIMAL(28,6)) [ANTS],
    OPS.SVIND [SVIND],
    RV.NR [RVNR],
    RV.NAVN [RVNAVN],
    RV.TYPE [TYPE],
    CAST(1 AS INT) [NIVEAU],
    0 [PARENT],
    OPS.LINE [LINE],
    CAST(0 AS SMALLINT) [PLINE]
    FROM dbo.DBVARE VR
    INNER JOIN dbo.DBOPSK OPS ON OPS.VARENR = VR.NR
    INNER JOIN dbo.DBRVARE RV ON RV.NR = OPS.RVARENR
    WHERE 1 = 1
    UNION ALL
    SELECT RECIPE.OPNR,
    0,
    CAST(RECIPE.ANTL * DOP.ANT / ISNULL(OA.SUMANT,1) AS DECIMAL(28,6)) [ANTL],
    CAST(RECIPE.ANT * DOP.ANT / ISNULL(OA.SUMANT,1) AS DECIMAL(28,6)) [ANT],
    CAST(RECIPE.ANTS * DOP.ANT / ISNULL(OA.SUMANT,1) AS DECIMAL(28,6)) [ANTS],
    DOP.SVIND,
    RV.NR [RVNR],
    RV.NAVN [RVNAVN],
    RV.TYPE [TYPE],
    RECIPE.NIVEAU +1 [NIVEAU],
    RECIPE.RVNR [PARENT],
    DOP.LINE [LINE],
    DOP.LINE [PLINE]
    FROM RECIPE_DBVARE RECIPE
    INNER JOIN dbo.DBDOPSK DOP ON DOP.NR = RECIPE.RVNR
    INNER JOIN dbo.DBRVARE RV ON RV.NR = DOP.RVARENR
    OUTER APPLY (SELECT NR, SUM(ANT) OVER(PARTITION BY NR) SUMANT, ROW_NUMBER() OVER(ORDER BY NR) RN FROM dbo.DBDOPSK DOP WHERE DOP.NR = RECIPE.RVNR ) OA
    WHERE CAST(RECIPE.NIVEAU +1 AS SMALLINT) <= 6
    AND (OA.RN IS NULL OR OA.RN = 1)

    ),
    RECIPE_DBRVARE (OPNR, DOPNR, ANTL, ANT, ANTS, SVIND, RVNR, RVNAVN, TYPE, NIVEAU, PARENT, LINE, PLINE) AS
    (
    SELECT 0 [OPNR],
    OPS.NR [DOPNR],
    0 [ANTL],
    CAST((OPS.ANT) / ISNULL(OA.SUMANT,1) AS DECIMAL(28,6)) [ANT],
    0 [ANTS],
    OPS.SVIND [SVIND],
    RV.NR [RVNR],
    RV.NAVN [RVNAVN],
    RV.TYPE [TYPE],
    CAST(1 AS SMALLINT) [NIVEAU],
    CAST(0 AS INT) [PARENT],
    OPS.LINE [LINE],
    CAST(0 AS SMALLINT) [PLINE]
    FROM dbo.DBDOPSK OPS
    INNER JOIN dbo.DBRVARE RV ON RV.NR = OPS.RVARENR
    OUTER APPLY (SELECT NR, SUM(ANT) OVER(PARTITION BY NR) SUMANT, ROW_NUMBER() OVER(ORDER BY NR) RN FROM dbo.DBDOPSK DOP WHERE DOP.NR = OPS.NR ) OA
    WHERE 1 = 1
    AND (OA.RN IS NULL OR OA.RN = 1)
    UNION ALL
    SELECT 0,
    RECIPE.DOPNR,
    0 [ANTL],
    CAST(RECIPE.ANT / ISNULL(OA.SUMANT,1) AS DECIMAL(28,6)) [ANT],
    0 [ANTS],
    DOP.SVIND,
    RV.NR [RVNR],
    RV.NAVN [RVNAVN],
    RV.TYPE [TYPE],
    CAST(RECIPE.NIVEAU +1 AS SMALLINT) [NIVEAU],
    RECIPE.RVNR [PARENT],
    DOP.LINE [LINE],
    RECIPE.LINE [PLINE]
    FROM RECIPE_DBRVARE RECIPE
    INNER JOIN dbo.DBDOPSK DOP ON DOP.NR = RECIPE.RVNR
    INNER JOIN dbo.DBRVARE RV ON RV.NR = DOP.RVARENR
    OUTER APPLY (SELECT NR, SUM(ANT) OVER(PARTITION BY NR) SUMANT, ROW_NUMBER() OVER(ORDER BY NR) RN FROM dbo.DBDOPSK DOP WHERE DOP.NR = RECIPE.DOPNR ) OA
    WHERE CAST(RECIPE.NIVEAU +1 AS SMALLINT) <= 6
    AND (OA.RN IS NULL OR OA.RN = 1)
    )

    SELECT RECIPE_DBVARE.OPNR ,
    RECIPE_DBVARE.DOPNR ,
    CAST(RECIPE_DBVARE.ANTL AS DECIMAL(15,6)) ANTL,
    CAST(RECIPE_DBVARE.ANT AS DECIMAL(15,6)) ANT,
    CAST(RECIPE_DBVARE.ANTS AS DECIMAL(15,6)) ANTS,
    RECIPE_DBVARE.SVIND ,
    RECIPE_DBVARE.RVNR ,
    RECIPE_DBVARE.RVNAVN ,
    RECIPE_DBVARE.TYPE ,
    RECIPE_DBVARE.NIVEAU ,
    RECIPE_DBVARE.PARENT ,
    RECIPE_DBVARE.LINE ,
    RECIPE_DBVARE.PLINE
    FROM RECIPE_DBVARE
    UNION ALL
    SELECT RECIPE_DBRVARE.OPNR ,
    RECIPE_DBRVARE.DOPNR ,
    RECIPE_DBRVARE.ANTL ,
    RECIPE_DBRVARE.ANT ,
    RECIPE_DBRVARE.ANTS ,
    RECIPE_DBRVARE.SVIND ,
    RECIPE_DBRVARE.RVNR ,
    RECIPE_DBRVARE.RVNAVN ,
    RECIPE_DBRVARE.TYPE ,
    RECIPE_DBRVARE.NIVEAU ,
    RECIPE_DBRVARE.PARENT ,
    RECIPE_DBRVARE.LINE ,
    RECIPE_DBRVARE.PLINE
    FROM RECIPE_DBRVARE

    And this one is the one I wrote thinking that was going to be faster 🙂

     

  • Jonathan AC Roberts

    SSCoach

    Points: 17208

    I don't quite understand the purpose of this code:

     OUTER APPLY (SELECT NR, 
    SUM(ANT) OVER(PARTITION BY NR) SUMANT,
    ROW_NUMBER() OVER(ORDER BY NR) RN
    FROM dbo.DBDOPSK DOP
    WHERE DOP.NR = RECIPE.DOPNR) OA
    WHERE CAST(RECIPE.NIVEAU +1 AS SMALLINT) <= 6
    AND (OA.RN IS NULL OR OA.RN = 1)

    Why do you need to specify "OA.RN IS NULL OR OA.RN = 1"?

    What are the values going to be for SUMANT where RN>1?

    Would SELECT TOP(1) do the same job?

     

     

  • frederico_fonseca

    SSChampion

    Points: 14471

     

    Jonathan AC Roberts wrote:

    I don't quite understand the purpose of this code:

     OUTER APPLY (SELECT NR, 
    SUM(ANT) OVER(PARTITION BY NR) SUMANT,
    ROW_NUMBER() OVER(ORDER BY NR) RN
    FROM dbo.DBDOPSK DOP
    WHERE DOP.NR = RECIPE.DOPNR) OA
    WHERE CAST(RECIPE.NIVEAU +1 AS SMALLINT) <= 6
    AND (OA.RN IS NULL OR OA.RN = 1)

    Why do you need to specify "OA.RN IS NULL OR OA.RN = 1"?

    What are the values going to be for SUMANT where RN>1?

    Would SELECT TOP(1) do the same job?

    I think that code could be replaced with

    OUTER APPLY (SELECT  SUM(ANT) as SUMANT
    FROM dbo.DBDOPSK DOP
    WHERE DOP.NR = RECIPE.DOPNR
    ) OA
    WHERE RECIPE.NIVEAU +1 <= 6 -- no need for the cast here

    reason is that only a single value of NR is retrieved so a single sum is enough without row number and partition clauses

    and this is on other places on the code so it may make a difference (for better or worst!! so test!!!)

  • Jonathan AC Roberts

    SSCoach

    Points: 17208

    frederico_fonseca wrote:

    Jonathan AC Roberts wrote:

    I don't quite understand the purpose of this code:

     OUTER APPLY (SELECT NR, 
    SUM(ANT) OVER(PARTITION BY NR) SUMANT,
    ROW_NUMBER() OVER(ORDER BY NR) RN
    FROM dbo.DBDOPSK DOP
    WHERE DOP.NR = RECIPE.DOPNR) OA
    WHERE CAST(RECIPE.NIVEAU +1 AS SMALLINT) <= 6
    AND (OA.RN IS NULL OR OA.RN = 1)

    Why do you need to specify "OA.RN IS NULL OR OA.RN = 1"?

    What are the values going to be for SUMANT where RN>1?

    Would SELECT TOP(1) do the same job?

    I think that code could be replaced with

    OUTER APPLY (SELECT  SUM(ANT) as SUMANT
    FROM dbo.DBDOPSK DOP
    WHERE DOP.NR = RECIPE.DOPNR
    ) OA
    WHERE RECIPE.NIVEAU +1 <= 6 -- no need for the cast here

    reason is that only a single value of NR is retrieved so a single sum is enough without row number and partition clauses

    and this is on other places on the code so it may make a difference (for better or worst!! so test!!!)

    Frederico, Yes, that looks better to me.

    Also, just for simplification "RECIPE.NIVEAU +1 <= 6" is the same as "RECIPE.NIVEAU <= 5"

  • Mauricio_

    SSCrazy

    Points: 2931

    You cannot use SELECT TOP in a CTE, that's why.

  • Mauricio_

    SSCrazy

    Points: 2931

    You cannot use GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part of a recursive common table expresion. In theory that query looks better, but I cannot use it.

  • frederico_fonseca

    SSChampion

    Points: 14471

    ah well.. we just looking at it and thinking of what can be better and not testing  as we don't have ddl for the tables.

    try the following 2 options

    -- option 1
    CREATE VIEW [dbo].[vRecipe] AS


    WITH aggreg_nr (NR, SUMAMNT)
    as (select NR, sum(AMT) as SUMAMNT from dbo.DBDOPSK group by NR)
    ,
    RECIPE_DBVARE (OPNR, DOPNR, ANTL, ANT, ANTS, SVIND, RVNR, RVNAVN, TYPE, NIVEAU, PARENT, LINE, PLINE)
    AS
    (
    ....
    --OUTER APPLY (SELECT NR, SUM(ANT) OVER(PARTITION BY NR) SUMANT, ROW_NUMBER() OVER(ORDER BY NR) RN FROM dbo.DBDOPSK DOP WHERE DOP.NR = RECIPE.RVNR ) OA
    -- replace with
    left outer join aggreg_nr nr on nr.nr = recipe.rvnr
    WHERE CAST(RECIPE.NIVEAU +1 AS SMALLINT) <= 6 -- replace with RECIPE.NIVEAU <= 5
    -- AND (OA.RN IS NULL OR OA.RN = 1) -- not needed

    ),
    RECIPE_DBRVARE (OPNR, DOPNR, ANTL, ANT, ANTS, SVIND, RVNR, RVNAVN, TYPE, NIVEAU, PARENT, LINE, PLINE) AS
    (
    .....
    --OUTER APPLY (SELECT NR, SUM(ANT) OVER(PARTITION BY NR) SUMANT, ROW_NUMBER() OVER(ORDER BY NR) RN FROM dbo.DBDOPSK DOP WHERE DOP.NR = OPS.NR ) OA
    -- replace with
    left outer join aggreg_nr nr on nr.nr = ops.nr
    WHERE 1 = 1
    -- AND (OA.RN IS NULL OR OA.RN = 1) -- not needed
    UNION ALL
    SELECT 0,
    RECIPE.DOPNR,
    0 [ANTL],
    CAST(RECIPE.ANT / ISNULL(OA.SUMANT,1) AS DECIMAL(28,6)) [ANT],
    0 [ANTS],
    DOP.SVIND,
    RV.NR [RVNR],
    RV.NAVN [RVNAVN],
    RV.TYPE [TYPE],
    CAST(RECIPE.NIVEAU +1 AS SMALLINT) [NIVEAU],
    RECIPE.RVNR [PARENT],
    DOP.LINE [LINE],
    RECIPE.LINE [PLINE]
    FROM RECIPE_DBRVARE RECIPE
    INNER JOIN dbo.DBDOPSK DOP ON DOP.NR = RECIPE.RVNR
    INNER JOIN dbo.DBRVARE RV ON RV.NR = DOP.RVARENR
    --OUTER APPLY (SELECT NR, SUM(ANT) OVER(PARTITION BY NR) SUMANT, ROW_NUMBER() OVER(ORDER BY NR) RN FROM dbo.DBDOPSK DOP WHERE DOP.NR = RECIPE.DOPNR ) OA
    -- replace with
    left outer join aggreg_nr nr on nr.nr = recipe.dopnr
    WHERE CAST(RECIPE.NIVEAU +1 AS SMALLINT) <= 6 -- replace with RECIPE.NIVEAU <= 5
    -- AND (OA.RN IS NULL OR OA.RN = 1) -- not needed
    )

    -- option 2
    or if the aggreg_nr still does not work then replace all the outer applies with
    outer apply (select sum(amt) over (order by nr) from dbo.dbdopsk dop where dop.nr = ... (3 different fields)
  • Mauricio_

    SSCrazy

    Points: 2931

    Trying first option with OUTER APPLY. LEFT JOIN is not allowed in the recursive part of a recursive common table expresion.

  • Mauricio_

    SSCrazy

    Points: 2931

    Performance is slighty better, however is still slower that UNION ALL version.

    Look at SQL Sentry Explorer.

    SQLSentryExplorerValues

  • Mauricio_

    SSCrazy

    Points: 2931

    -- option 2

    or if the aggreg_nr still does not work then replace all the outer applies with

    outer apply (select sum(amt) over (order by nr) from dbo.dbdopsk dop where dop.nr = ... (3 different fields)

    This won't work because for a given NR could have more than 1 row as result. That's why I was using row_number before. And since you cannot use GROUP BY, I couldn't find any other solution.

  • frederico_fonseca

    SSChampion

    Points: 14471

    can you also try the second option - and post plans for both first option (with outer apply) and my second - this second should be better than your first attempt as it has less window functions

  • Mauricio_

    SSCrazy

    Points: 2931

    I've replaced a couple of indexes, including some fields in order to avoid key lookups. Now performance looks a bit better, as you can see here:

    SQLSentryExplorerValuesV2

    Still wondering if this can be improve even more.

  • frederico_fonseca

    SSChampion

    Points: 14471

    Mauricio_ wrote:

    -- option 2

    or if the aggreg_nr still does not work then replace all the outer applies with

    outer apply (select sum(amt) over (order by nr) from dbo.dbdopsk dop where dop.nr = ... (3 different fields)

    This won't work because for a given NR could have more than 1 row as result. That's why I was using row_number before. And since you cannot use GROUP BY, I couldn't find any other solution.

    apologies - forgot that on this particular case although the sum is correct you would still get multiple records - one per each nr.

     

    regarding your latest stats - which one is what and can you post the plans - and is that using the aggreg_nr option I take it.

    both plans now as you changed the indexes.

Viewing 15 posts - 1 through 15 (of 61 total)

You must be logged in to reply to this topic. Login to reply