# 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 PLINEfrom DBDOPSK nivdop1inner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv1 on sumniv1.NR = nivdop1.NRinner join DBRVARE nivrv1 on nivrv1.NR = nivdop1.RVARENRunion allSELECT 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 PLINEfrom DBDOPSK nivdop1inner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv1 on sumniv1.NR = nivdop1.NRinner join DBRVARE nivrv1 on nivrv1.NR = nivdop1.RVARENRinner join DBDOPSK nivdop2 on nivdop2.NR = nivrv1.NRinner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv2 on sumniv2.NR = nivdop2.NRinner 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).

Mauricio

• Jonathan AC Roberts

SSCoach

Points: 17272

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] ASSELECT 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 PLINEfrom DBDOPSK nivdop1inner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv1 on sumniv1.NR = nivdop1.NRinner join DBRVARE nivrv1 on nivrv1.NR = nivdop1.RVARENRunion allSELECT 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 PLINEfrom DBDOPSK nivdop1inner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv1 on sumniv1.NR = nivdop1.NRinner join DBRVARE nivrv1 on nivrv1.NR = nivdop1.RVARENRinner join DBDOPSK nivdop2 on nivdop2.NR = nivrv1.NRinner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv2 on sumniv2.NR = nivdop2.NRinner join DBRVARE nivrv2 on nivrv2.NR = nivdop2.RVARENRunion allSELECT 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 PLINEfrom DBDOPSK nivdop1inner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv1 on sumniv1.NR = nivdop1.NRinner join DBRVARE nivrv1 on nivrv1.NR = nivdop1.RVARENRinner join DBDOPSK nivdop2 on nivdop2.NR = nivrv1.NRinner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv2 on sumniv2.NR = nivdop2.NRinner join DBRVARE nivrv2 on nivrv2.NR = nivdop2.RVARENRinner join DBDOPSK nivdop3 on nivdop3.NR = nivrv2.NRinner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv3 on sumniv3.NR = nivdop3.NRinner join DBRVARE nivrv3 on nivrv3.NR = nivdop3.RVARENRunion allSELECT 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 PLINEfrom DBDOPSK nivdop1inner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv1 on sumniv1.NR = nivdop1.NRinner join DBRVARE nivrv1 on nivrv1.NR = nivdop1.RVARENRinner join DBDOPSK nivdop2 on nivdop2.NR = nivrv1.NRinner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv2 on sumniv2.NR = nivdop2.NRinner join DBRVARE nivrv2 on nivrv2.NR = nivdop2.RVARENRinner join DBDOPSK nivdop3 on nivdop3.NR = nivrv2.NRinner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv3 on sumniv3.NR = nivdop3.NRinner join DBRVARE nivrv3 on nivrv3.NR = nivdop3.RVARENRinner join DBDOPSK nivdop4 on nivdop4.NR = nivrv3.NRinner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv4 on sumniv4.NR = nivdop4.NRinner join DBRVARE nivrv4 on nivrv4.NR = nivdop4.RVARENRunion allSELECT 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 PLINEfrom DBDOPSK nivdop1inner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv1 on sumniv1.NR = nivdop1.NRinner join DBRVARE nivrv1 on nivrv1.NR = nivdop1.RVARENRinner join DBDOPSK nivdop2 on nivdop2.NR = nivrv1.NRinner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv2 on sumniv2.NR = nivdop2.NRinner join DBRVARE nivrv2 on nivrv2.NR = nivdop2.RVARENRinner join DBDOPSK nivdop3 on nivdop3.NR = nivrv2.NRinner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv3 on sumniv3.NR = nivdop3.NRinner join DBRVARE nivrv3 on nivrv3.NR = nivdop3.RVARENRinner join DBDOPSK nivdop4 on nivdop4.NR = nivrv3.NRinner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv4 on sumniv4.NR = nivdop4.NRinner join DBRVARE nivrv4 on nivrv4.NR = nivdop4.RVARENRinner join DBDOPSK nivdop5 on nivdop5.NR = nivrv4.NRinner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv5 on sumniv5.NR = nivdop5.NRinner join DBRVARE nivrv5 on nivrv5.NR = nivdop5.RVARENRunion allSELECT 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 PLINEfrom DBDOPSK nivdop1inner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv1 on sumniv1.NR = nivdop1.NRinner join DBRVARE nivrv1 on nivrv1.NR = nivdop1.RVARENRinner join DBDOPSK nivdop2 on nivdop2.NR = nivrv1.NRinner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv2 on sumniv2.NR = nivdop2.NRinner join DBRVARE nivrv2 on nivrv2.NR = nivdop2.RVARENRinner join DBDOPSK nivdop3 on nivdop3.NR = nivrv2.NRinner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv3 on sumniv3.NR = nivdop3.NRinner join DBRVARE nivrv3 on nivrv3.NR = nivdop3.RVARENRinner join DBDOPSK nivdop4 on nivdop4.NR = nivrv3.NRinner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv4 on sumniv4.NR = nivdop4.NRinner join DBRVARE nivrv4 on nivrv4.NR = nivdop4.RVARENRinner join DBDOPSK nivdop5 on nivdop5.NR = nivrv4.NRinner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv5 on sumniv5.NR = nivdop5.NRinner join DBRVARE nivrv5 on nivrv5.NR = nivdop5.RVARENRinner join DBDOPSK nivdop6 on nivdop6.NR = nivrv5.NRinner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv6 on sumniv6.NR = nivdop6.NRinner join DBRVARE nivrv6 on nivrv6.NR = nivdop6.RVARENRunion allSELECT 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 PLINEfrom DBOPSK OPinner join DBRVARE nivrv1 on nivrv1.NR = OP.RVARENRunion allSELECT 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 PLINEfrom DBOPSK OPinner join DBRVARE nivrv1 on nivrv1.NR = OP.RVARENRinner join DBDOPSK nivdop2 on nivdop2.NR = nivrv1.NRinner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv2 on sumniv2.NR = nivdop2.NRinner join DBRVARE nivrv2 on nivrv2.NR = nivdop2.RVARENRunion allSELECT 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 PLINEfrom DBOPSK OPinner join DBRVARE nivrv1 on nivrv1.NR = OP.RVARENRinner join DBDOPSK nivdop2 on nivdop2.NR = nivrv1.NRinner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv2 on sumniv2.NR = nivdop2.NRinner join DBRVARE nivrv2 on nivrv2.NR = nivdop2.RVARENRinner join DBDOPSK nivdop3 on nivdop3.NR = nivrv2.NRinner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv3 on sumniv3.NR = nivdop3.NRinner join DBRVARE nivrv3 on nivrv3.NR = nivdop3.RVARENRunion allSELECT 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 PLINEfrom DBOPSK OPinner join DBRVARE nivrv1 on nivrv1.NR = OP.RVARENRinner join DBDOPSK nivdop2 on nivdop2.NR = nivrv1.NRinner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv2 on sumniv2.NR = nivdop2.NRinner join DBRVARE nivrv2 on nivrv2.NR = nivdop2.RVARENRinner join DBDOPSK nivdop3 on nivdop3.NR = nivrv2.NRinner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv3 on sumniv3.NR = nivdop3.NRinner join DBRVARE nivrv3 on nivrv3.NR = nivdop3.RVARENRinner join DBDOPSK nivdop4 on nivdop4.NR = nivrv3.NRinner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv4 on sumniv4.NR = nivdop4.NRinner join DBRVARE nivrv4 on nivrv4.NR = nivdop4.RVARENRunion allSELECT 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 PLINEfrom DBOPSK OPinner join DBRVARE nivrv1 on nivrv1.NR = OP.RVARENRinner join DBDOPSK nivdop2 on nivdop2.NR = nivrv1.NRinner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv2 on sumniv2.NR = nivdop2.NRinner join DBRVARE nivrv2 on nivrv2.NR = nivdop2.RVARENRinner join DBDOPSK nivdop3 on nivdop3.NR = nivrv2.NRinner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv3 on sumniv3.NR = nivdop3.NRinner join DBRVARE nivrv3 on nivrv3.NR = nivdop3.RVARENRinner join DBDOPSK nivdop4 on nivdop4.NR = nivrv3.NRinner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv4 on sumniv4.NR = nivdop4.NRinner join DBRVARE nivrv4 on nivrv4.NR = nivdop4.RVARENRinner join DBDOPSK nivdop5 on nivdop5.NR = nivrv4.NRinner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv5 on sumniv5.NR = nivdop5.NRinner join DBRVARE nivrv5 on nivrv5.NR = nivdop5.RVARENRunion allSELECT 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 PLINEfrom DBOPSK OPinner join DBRVARE nivrv1 on nivrv1.NR = OP.RVARENRinner join DBDOPSK nivdop2 on nivdop2.NR = nivrv1.NRinner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv2 on sumniv2.NR = nivdop2.NRinner join DBRVARE nivrv2 on nivrv2.NR = nivdop2.RVARENRinner join DBDOPSK nivdop3 on nivdop3.NR = nivrv2.NRinner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv3 on sumniv3.NR = nivdop3.NRinner join DBRVARE nivrv3 on nivrv3.NR = nivdop3.RVARENRinner join DBDOPSK nivdop4 on nivdop4.NR = nivrv3.NRinner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv4 on sumniv4.NR = nivdop4.NRinner join DBRVARE nivrv4 on nivrv4.NR = nivdop4.RVARENRinner join DBDOPSK nivdop5 on nivdop5.NR = nivrv4.NRinner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv5 on sumniv5.NR = nivdop5.NRinner join DBRVARE nivrv5 on nivrv5.NR = nivdop5.RVARENRinner join DBDOPSK nivdop6 on nivdop6.NR = nivrv5.NRinner join (select NR, SUM(ANT) SUMANT from DBDOPSK group by NR) sumniv6 on sumniv6.NR = nivdop6.NRinner join DBRVARE nivrv6 on nivrv6.NR = nivdop6.RVARENRGO`

This is the original.

`CREATE VIEW [dbo].[vRecipe] ASWITH 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_DBVAREUNION ALLSELECT 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: 17272

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: 14636

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: 17272

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: 14636

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 1CREATE VIEW [dbo].[vRecipe] ASWITH 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 2or if the aggreg_nr still does not work then replace all the outer applies withouter 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.

• 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: 14636

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:

Still wondering if this can be improve even more.

• frederico_fonseca

SSChampion

Points: 14636

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)