February 17, 2020 at 9:43 am
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.
February 17, 2020 at 10:12 am
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)
February 17, 2020 at 10:22 am
Here is the new plan with first option (UNION ALL SELECT NULL, NULL). What's the purpose of the UNION ALL in this case?
February 17, 2020 at 11:02 am
Mauricio, Can you post the current version of the SQL code you are using?
February 17, 2020 at 11:03 am
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
February 17, 2020 at 11:28 am
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?
February 17, 2020 at 11:30 am
I need it, otherwise I'm getting an aritmethic overflow error, even defined as decimal(38,6)
February 17, 2020 at 11:41 am
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?
February 17, 2020 at 11:43 am
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?
February 17, 2020 at 12:13 pm
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
February 17, 2020 at 12:16 pm
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
February 17, 2020 at 1:11 pm
No, this won't work. As I said before, you can't have aggregate functions in the recursive part of a recursive CTE.
February 17, 2020 at 1:41 pm
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?
February 17, 2020 at 1:49 pm
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.
February 17, 2020 at 2:04 pm
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 60 total)
You must be logged in to reply to this topic. Login to reply