Recursive CTE vs UNION ALL in a VIEW
-
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.RVARENRThat'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
-
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?
-
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
GOThis 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_DBRVAREAnd this one is the one I wrote thinking that was going to be faster 🙂
-
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?
- 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 herereason 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_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 herereason 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"
-
You cannot use SELECT TOP in a CTE, that's why.
-
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.
-
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) -
Trying first option with OUTER APPLY. LEFT JOIN is not allowed in the recursive part of a recursive common table expresion.
-
Performance is slighty better, however is still slower that UNION ALL version.
Look at SQL Sentry Explorer.
-
-- 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.
-
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
-
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:
Still wondering if this can be improve even more.
- 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 60 total)
You must be logged in to reply to this topic. Login to reply