Recursive CTE vs UNION ALL in a VIEW

  • Mauricio_

    SSCrazy

    Points: 2931

    Current view is using your Aggreg_NR CTE. Beside that, I've changed the indexes, that helped a little. I don't like the index spool in current execution plan.

    IndexSpool

    I know that in theory this shouldn't be a problem because it's the way that SQL is optimizing the query. However, we start with 54K rows and end with just 47. I wonder if there is a better way.

  • frederico_fonseca

    SSChampion

    Points: 14632

    not necessarily bad - it depends on how many times that spool is created - if just once its fine - if once per each loop then not so good. hence the explain plan - not just an image.

    assuming that that spool is because of the aggreg_nr we could try changing it to be one of the below - it may or not improve

    as (select NR, sum(AMT) as SUMAMNT from dbo.DBDOPSK group by NR
    union all
    select null, null)

    or
    as (select top (20000000) NR, sum(AMT) as SUMAMNT from dbo.DBDOPSK group by NR
    order by nr)
  • Mauricio_

    SSCrazy

    Points: 2931

    Here is the new plan with first option (UNION ALL SELECT NULL, NULL). What's the purpose of the UNION ALL in this case?

    https://www.brentozar.com/pastetheplan/?id=S1B7FkdXI

  • Jonathan AC Roberts

    SSCoach

    Points: 17269

    Mauricio, Can you post the current version of the SQL code you are using?

  • Mauricio_

    SSCrazy

    Points: 2931

    Sure, here is:

    USE [DBMCS_BK]
    GO

    /****** Object: View [dbo].[vRecipe] Script Date: 2/16/2020 9:04:31 PM ******/
    DROP VIEW IF EXISTS [dbo].[vRecipe]
    GO

    /****** Object: View [dbo].[vRecipe] Script Date: 2/16/2020 9:04:31 PM ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO


    CREATE VIEW [dbo].[vRecipe] AS

    WITH SUMANT_NR (NR, SUMANT)
    as (select NR, sum(ANT) as SUMANT from dbo.DBDOPSK group by NR
    union all
    select null, null),
    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 SUM(ANT) OVER(PARTITION BY NR) SUMANT, ROW_NUMBER() OVER(ORDER BY NR) RN FROM dbo.DBDOPSK DOP WHERE DOP.NR = RECIPE.RVNR ) OA
    OUTER APPLY (SELECT SUMANT FROM SUMANT_NR OA WHERE OA.NR = RECIPE.RVNR) OA
    WHERE RECIPE.NIVEAU <= 5
    --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 SUM(ANT) OVER(PARTITION BY NR) SUMANT, ROW_NUMBER() OVER(ORDER BY NR) RN FROM dbo.DBDOPSK DOP WHERE DOP.NR = OPS.NR ) OA
    OUTER APPLY (SELECT SUMANT FROM SUMANT_NR OA WHERE OA.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 SUM(ANT) OVER(PARTITION BY NR) SUMANT, ROW_NUMBER() OVER(ORDER BY NR) RN FROM dbo.DBDOPSK DOP WHERE DOP.NR = RECIPE.DOPNR ) OA
    OUTER APPLY (SELECT SUMANT FROM SUMANT_NR OA WHERE OA.NR = RECIPE.DOPNR) OA
    WHERE RECIPE.NIVEAU <= 5
    --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


    ---- END OF VIEW -----------------

    GO


  • Jonathan AC Roberts

    SSCoach

    Points: 17269

    Why is it that you are not using:

    OUTER APPLY (SELECT SUM(ANT) as SUMANT
    FROM dbo.DBDOPSK DOP
    WHERE DOP.NR = RECIPE.DOPNR
    ) OA

    but instead, having it in a different part of the CTE?

     

     

  • Mauricio_

    SSCrazy

    Points: 2931

    I need it, otherwise I'm getting an aritmethic overflow error, even defined as decimal(38,6)

  • Jonathan AC Roberts

    SSCoach

    Points: 17269

    I don't understand why you are getting an overflow error when selecting from the table but not from the CTE?

    Surely they add up to the same value wherever you do it?

  • Mauricio_

    SSCrazy

    Points: 2931

    Yes, maybe I didn't understand you completely. I thought you were suggesting to have that outside CTE but as LEFT JOIN in the SELECT FROM CTE.

    So, what do you suggest? Where to put it?

  • frederico_fonseca

    SSChampion

    Points: 14632

    Mauricio_ wrote:

    Here is the new plan with first option (UNION ALL SELECT NULL, NULL). What's the purpose of the UNION ALL in this case?

    https://www.brentozar.com/pastetheplan/?id=S1B7FkdXI

    purpose was to try and "materialize" that query - didn't work but it also is not the one with the spool so you can remove it.

    spool is from "INNER JOIN dbo.DBDOPSK DOP" and this will be harder to eliminate

  • Jonathan AC Roberts

    SSCoach

    Points: 17269

    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 SUM(ANT) as SUMANT
    FROM dbo.DBDOPSK DOP
    WHERE DOP.NR = RECIPE.DOPNR
    ) OA
    WHERE RECIPE.NIVEAU <= 5
    --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 SUM(ANT) OVER(PARTITION BY NR) SUMANT, ROW_NUMBER() OVER(ORDER BY NR) RN FROM dbo.DBDOPSK DOP WHERE DOP.NR = OPS.NR ) OA
    OUTER APPLY (SELECT SUM(ANT) as SUMANT
    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 SUM(ANT) as SUMANT
    FROM dbo.DBDOPSK DOP
    WHERE DOP.NR = RECIPE.DOPNR) OA
    WHERE RECIPE.NIVEAU <= 5
    --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

    • This reply was modified 3 months, 1 week ago by  Jonathan AC Roberts. Reason: missed an outer apply
  • Mauricio_

    SSCrazy

    Points: 2931

    No, this won't work. As I said before, you can't have aggregate functions in the recursive part of a recursive CTE.

  • Jonathan AC Roberts

    SSCoach

    Points: 17269

    Mauricio_ wrote:

    No, this won't work. As I said before, you can't have aggregate functions in the recursive part of a recursive CTE.

    Ok, I see and agree.

    So did your initial query work with the outer apply and SUM(ANT) OVER(PARTITION BY NR) SUMANT? Because wasn't that also an aggregate function in the recursive part of a recursive common table expression?

  • Mauricio_

    SSCrazy

    Points: 2931

    No, it's not, because it's not part of a recursive table expression. Somehow it seems that SQL solves that query before and then you can use it.

    By the way, this is the result in SQL Sentry Explorer...

    SQLSentryExplorerValuesV3

    And this is the execution plan: https://www.brentozar.com/pastetheplan/?id=B1SsFzdmI

    As you can see, no more index spool. The trick was to change some existing queries, including some extra fields on them.

  • Jonathan AC Roberts

    SSCoach

    Points: 17269

    Another option would be to create a view or user-defined function or table-valued function. I'm not sure if this would be any more efficient without you testing.

    User defined function:

    CREATE FUNCTION [dbo].[GetSumant] (@NR INT )
    RETURNS INT
    AS
    BEGIN
    RETURN (SELECT SUM(ANT)
    FROM dbo.DBDOPSK DOP
    WHERE DOP.NR = @NR)
    END
    GO

    Then in the SQL:

    UNION ALL
    SELECT 0,
    RECIPE.DOPNR,
    0 [ANTL],
    CAST(RECIPE.ANT / ISNULL(dbo.[GetSumant](RECIPE.DOPNR),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
    WHERE RECIPE.NIVEAU <= 5

    Or a View

    CREATE VIEW [dbo].[vwDBDOPSKSumant]
    AS
    SELECT DOP.NR,
    SUM(DOP.ANT) SUMANT
    FROM dbo.DBDOPSK DOP
    GROUP BY DOP.NR
    GO

    Then in the SQL:

    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 SUMANT FROM vwDBDOPSKSumant OA WHERE OA.NR = RECIPE.DOPNR) OA
    WHERE RECIPE.NIVEAU <= 5

    Or table-valued function:

    CREATE FUNCTION [dbo].[tvfDBDOPSKSumant](@NR INT)
    RETURNS TABLE WITH SCHEMABINDING AS RETURN
    SELECT SUM(DOP.ANT) SUMANT
    FROM dbo.DBDOPSK DOP
    WHERE DOP.NR=@NR

    Then in the SQL:

    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 dbo.tvfDBDOPSKSumant(RECIPE.DOPNR) OA
    WHERE RECIPE.NIVEAU <= 5

     

Viewing 15 posts - 16 through 30 (of 61 total)

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