Recursive CTE vs UNION ALL in a VIEW
-
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.
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.
-
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) -
Here is the new plan with first option (UNION ALL SELECT NULL, NULL). What's the purpose of the UNION ALL in this case?
-
Mauricio, Can you post the current version of the SQL code you are using?
-
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 -
Why is it that you are not using:
OUTER APPLY (SELECT SUM(ANT) as SUMANT
FROM dbo.DBDOPSK DOP
WHERE DOP.NR = RECIPE.DOPNR
) OAbut instead, having it in a different part of the CTE?
-
I need it, otherwise I'm getting an aritmethic overflow error, even defined as decimal(38,6)
-
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?
-
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?
-
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?
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
-
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 -
No, this won't work. As I said before, you can't have aggregate functions in the recursive part of a recursive CTE.
-
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?
-
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...
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.
-
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
GOThen 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 <= 5Or a View
CREATE VIEW [dbo].[vwDBDOPSKSumant]
AS
SELECT DOP.NR,
SUM(DOP.ANT) SUMANT
FROM dbo.DBDOPSK DOP
GROUP BY DOP.NR
GOThen 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 <= 5Or 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=@NRThen 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 60 total)
You must be logged in to reply to this topic. Login to reply