7

This question is continuation of optimizer behavior exploration in regard to VALUES construct started here and here. I want to ask about VALUES and APPLY this time.

Using CROSS APPLY to alias an expression that needs to be referenced in various parts of a query is the common pattern. Such as, for example

CREATE TABLE #data (N int);
INSERT INTO #data VALUES (5), (4), (3), (2), (1);

SELECT d.N, c.[Square]
FROM #data d
    CROSS APPLY (VALUES (d.N * d.N)) c([Square])
WHERE c.[Square] BETWEEN 1 AND 10
ORDER BY c.[Square];

I myself do always use CROSS APPLY in such cases, but sometimes I encounter such expressions wrapped into inline-TVF and OUTER APPLY-ed. So, out of curiosity I changed CROSS to OUTER in the exampling query

SELECT d.N, c.[Square]
FROM #data d
    OUTER APPLY (VALUES (d.N * d.N)) c([Square])
WHERE c.[Square] BETWEEN 1 AND 10
ORDER BY c.[Square];

to check if it matters.

As I understand, semantics of CROSS APPLY and OUTER APPLY equal in this case, since VALUES (d.N * d.N) always returns exactly one row for each row of d. My expectation was that optimizer will produce identical execution plans for both (likewise it changes left join to inner sometimes). This is not the case however.

The OUTER modification has execution plan looking close to its written form. It has APPLY implemented as Nested Loops with expression computed in Constant Scan on the inner side, then Filter is there to address WHERE predicate and Sort to address ORDER BY finally.

OUTER APPLY plan

Whereas execution plan of the CROSS modification looks like if there were no APPLY at all and expression has been substituted instead of its alias. It has WHERE predicate pushed to Table Scan, then there is the Compute Scalar performing expression computation and finally Sort as well.

CROSS APPLY plan

Why don't optimizer want to get rid of APPLY in case of OUTER like it did it with CROSS? It has all information in hands to conclude that VALUES produces exactly one row per each table row.

I understand that answer can be short like "It could in theory, but that's what current implementation is". So, to inspire for more than that, I have additional questions (going along the way) regarding optimization paths of these two queries.

In the rest part of the question shortened queries are used

SELECT d.N, c.[Square]
FROM #data d
    CROSS APPLY (VALUES (d.N * d.N)) c([Square]);

SELECT d.N, c.[Square]
FROM #data d
    OUTER APPLY (VALUES (d.N * d.N)) c([Square]);

They lack WHERE and ORDER BY. Despite that, they have all the necessary attributes and have different execution plans as well.

CROSS APPLY

Let's start with the Input Tree (trace flag 8606). At this point logical tree well correspond to written form of the query.

*** Input Tree: ***
LogOp_Project QCOL: [d].N COL: Expr1002 
    LogOp_Apply (x_jtInner)
        LogOp_Get TBL: #data(alias TBL: d)
        LogOp_Project
            LogOp_ConstTableGet (1) [empty]
            AncOp_PrjList 
                AncOp_PrjEl COL: Expr1002 
                    ScaOp_Arithmetic x_aopMult
                        ScaOp_Identifier QCOL: [d].N
                        ScaOp_Identifier QCOL: [d].N
    AncOp_PrjList 
*******************

Then, before entering simplification stage, thing named projection pulling happens. Optimizer pulls scalar expression computation above Apply

*** Before Simplification Tree (not visible with TF 8606) ***
LogOp_Project QCOL: [d].N COL: Expr1002 
    LogOp_Apply (x_jtInner)
        LogOp_Get TBL: #data(alias TBL: d)
        LogOp_ConstTableGet (1) [empty]
    AncOp_PrjList 
        AncOp_PrjEl COL: Expr1002 
            ScaOp_Arithmetic x_aopMult
                ScaOp_Identifier QCOL: [d].N
                ScaOp_Identifier QCOL: [d].N
*******************

What is the aim of pulling scalar expression above Apply? What are the things that inhibit pulling naturally, and is there a way to suppress it by force?

After that simplification stage begins and among the first rules being applied (trace flag 8621) PrjApplyHandler changes Apply to Join

***** Rule applied: Prj APPLY stack -> Prj Join stack
LogOp_Project
    LogOp_Join
        LogOp_Get TBL: #data(alias TBL: d)
        LogOp_ConstTableGet (1) [empty]
        ScaOp_Const TI(bit,ML=1) XVAR(bit,Not Owned,Value=1)
    AncOp_PrjList 
        ...

If being implemented at this stage the execution plan of the query would look as

CROSS APPLY scalar pulled

Then SimplifyJoinWithCTG rule gets into action. It removes cross join to empty single row LogOp_ConstTableGet leaving the LogOp_Get only

***** Rule applied: Join/LSJ(ConstTableGet(1),x0,True) --> x0
    LogOp_Get TBL: #data(alias TBL: d)

So, after all simplifications the logical tree becomes

*** Simplified Tree: ***
LogOp_Project
    LogOp_Get TBL: #data(alias TBL: d)
    AncOp_PrjList 
        AncOp_PrjEl COL: Expr1002 
            ScaOp_Arithmetic x_aopMult
                ScaOp_Identifier QCOL: [d].N
                ScaOp_Identifier QCOL: [d].N
*******************

And then it flows unchanged up until being implemented trivially as

CROSS APPLY final

Disabling SimplifyJoinWithCTG rule leads to the execution plan with scalar expression computation pushed below the join, but to its outer side

CROSS APPLY scalar pushed

(this plan has JoinCommute disabled also).

What is the aim to push scalar expression below the join after it has been pulled above before?

OUTER APPLY

The Input Tree for OUTER APPLY modification is almost identical to that of the CROSS APPLY. The only difference is that LogOp_Apply has x_jtLeftOuter type

*** Input Tree: ***
LogOp_Project QCOL: [d].N COL: Expr1002 
    LogOp_Apply (x_jtLeftOuter)
        LogOp_Get TBL: #data(alias TBL: d)
        LogOp_Project
            LogOp_ConstTableGet (1) [empty]
            AncOp_PrjList 
                AncOp_PrjEl COL: Expr1002 
                    ScaOp_Arithmetic x_aopMult
                        ScaOp_Identifier QCOL: [d].N
                        ScaOp_Identifier QCOL: [d].N
    AncOp_PrjList 
*******************

Starting from this point things are very different for OUTER APPLY. The first difference is that projection pulling not happens for scalar expression in this case. Why optimizer does not pull scalar expression for OUTER APPLY?

The second difference is that optimizer does not change Apply to Join at simplification stage. Actually even in the Output Tree (trace flag 8607) it is presented as PhyOp_Apply and join appears as post-optimization only.

Despite that RedundantApplyOJ and ApplyHandler simplification rules are applied

RedundantApplyOJ applied ApplyHandler applied

there are no signs of it in the Simplified Tree

*** Simplified Tree: ***
LogOp_Apply (x_jtLeftOuter)
    LogOp_Get TBL: #data(alias TBL: d)
    LogOp_ConstTableGet (1) COL: Expr1002 
        ScaOp_Arithmetic x_aopMult
            ScaOp_Identifier QCOL: [d].N
            ScaOp_Identifier QCOL: [d].N
*******************

Also SimplifyJoinWithCTG rule is not even considered for OUTER APPLY (meaning that it has no chance to match or not match).

Then, after all simplifications done, the logical tree flows unchanged up until being implemented at Quick Plan stage as

OUTER APPLY final

Why optimizer didn't tried to change Apply to Join at simplification stage for OUTER APPLY like it did it for CROSS APPLY? What RedundantApplyOJ and ApplyHandler rules do during simplification in this case? Are they something like A*1=A (meaning that nothing is changed effectively), or are they just change some properties of the logical tree nodes without affecting tree shape?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.