Pushdown-First Modernization: Engineering Execution-Plan Stability in SAP HANA Migrations

Most SAP HANA migration failures are not correctness failures. They are plan stability failures that surface only under concurrency. A query that executes in 900 milliseconds in isolation begins to oscillate between 800 milliseconds and 14 seconds under load, with no code change and no data skew obvious enough to blame. The root cause is rarely hardware or memory configuration. In most cases, PlanViz shows large intermediate row counts forming before reduction, with estimated cardinality significantly below actual. The instability originates from translating legacy EDW logic into SAP HANA artifacts without redesigning execution boundaries for a columnar, operator-driven engine.

Pushdown-first modernization is often interpreted as "move everything into SQL." That interpretation is incomplete. The actual problem is not about moving logic downward; it is about controlling how the calculation engine constructs and reuses execution graphs under varying runtime conditions. When SQLScript procedures and calculation views are designed without regard to grain stabilization, operator ordering, and cardinality propagation, the resulting plans remain syntactically valid but produce workload-sensitive operator graphs whose memory footprint shifts with parameter selectivity.

This article dissects the mechanics behind execution-plan stability in SAP HANA migrations, focusing on SQLScript procedures and Calculation Views as first-class architectural units.

The Architectural Shift: From Staged ETL to Operator Graph Execution

Traditional EDW pipelines relied on staged transformations. Each step materialized an intermediate state, often writing into persistent tables between transformations. That staging introduced natural grain boundaries. Joins were resolved, aggregations were completed, and the next transformation consumed stable, reduced datasets.

In SAP HANA, Calculation Views and SQLScript table functions remove those materialization barriers. Logical transformations are fused into a single operator graph. PlanViz reveals this as a directed acyclic graph of projection, join, aggregation, and calculation nodes. The optimizer is free to reorder joins, push predicates downward, and defer aggregations.

That freedom improves latency in well-designed models. It amplifies instability in poorly designed ones.

Consider a common migration pattern:

SELECT
    h.MATERIAL_ID,
    SUM(l.QUANTITY) AS TOTAL_QTY
FROM HEADER h
JOIN LINE_ITEM l
    ON h.DOC_ID = l.DOC_ID
WHERE h.POSTING_DATE BETWEEN :p_from AND :p_to
GROUP BY h.MATERIAL_ID;

Translated directly into a Calculation View, the join and aggregation nodes are placed without enforcing a grain reduction before high-cardinality joins. Under small parameter windows, the plan performs adequately. Under wide date ranges, the join produces a large intermediate result before aggregation collapses it. Memory amplification becomes workload-dependent.

In PlanViz, the join node frequently shows actual row counts an order of magnitude higher than estimated. For example, a date window spanning a quarter can produce 38 million intermediate rows before aggregation collapses the result to fewer than 300000 grouped records. The aggregation node is inexpensive. The join node is not. Memory allocation occurs before reduction.

The legacy system relied on pre-aggregated staging tables to constrain that explosion. The HANA translation removed the staging but did not redesign the grain boundary.

Why Preserving Batch Semantics Breaks Under Concurrency

In staged ETL systems, concurrency was limited. Batch windows were serialized. Execution plans operated with predictable resource envelopes. HANA environments operate with interactive workloads, overlapping parameter combinations, and mixed analytic demands.

An SQLScript procedure frequently encapsulates logic like this:

lt_filtered =
    SELECT *
    FROM SALES
    WHERE REGION = :p_region;

lt_enriched =
    SELECT f.*, d.CATEGORY
    FROM :lt_filtered AS f
    JOIN DIM_PRODUCT d
        ON f.PRODUCT_ID = d.PRODUCT_ID;

lt_aggregated =
    SELECT CATEGORY,
           SUM(AMOUNT) AS TOTAL
    FROM :lt_enriched
    GROUP BY CATEGORY;

SELECT * FROM :lt_aggregated;

Syntactically, the intermediate variables imply sequencing. In practice, the optimizer inlines these operations. If REGION is not highly selective, the join with DIM_PRODUCT expands cardinality before aggregation. Under multiple concurrent sessions with varying region selectivity, the same operator graph is reused while actual cardinality diverges across sessions. One session may process 2 million rows, another 40 million. Each constructs its own hash structures while the plan shape remains identical.

Plan instability emerges from estimation drift, not code defects.

Batch semantics assumed a stable data distribution. Interactive concurrency invalidates that assumption.

Grain Stabilization as a First-Class Design Constraint

Execution-plan stability in HANA depends on reducing cardinality before high-cost joins. That principle is mechanical, not stylistic.

Instead of joining at the transaction grain and aggregating afterward, redesign the model to collapse the grain first:

lt_reduced =
    SELECT PRODUCT_ID,
           SUM(AMOUNT) AS TOTAL_AMOUNT
    FROM SALES
    WHERE REGION = :p_region
    GROUP BY PRODUCT_ID;

SELECT
    r.PRODUCT_ID,
    d.CATEGORY,
    r.TOTAL_AMOUNT
FROM :lt_reduced AS r
JOIN DIM_PRODUCT d
    ON r.PRODUCT_ID = d.PRODUCT_ID;

This change enforces aggregation before dimensional enrichment. The intermediate dataset shrinks before the join. In PlanViz, the aggregation node now executes before dimensional enrichment, reducing the intermediate row count from tens of millions to low single-digit millions before the join. Hash table size contracts accordingly, and runtime variance narrows under concurrency.

Within calculation views, this requires explicit modeling:

• Aggregation nodes placed before join nodes
• Join cardinality correctly annotated
• Star-join semantics avoided for high-variance fact tables

Without explicit grain control, the optimizer may defer aggregation for cost-based reasons that are correct for one parameter distribution and catastrophic for another.

Pushdown-first modernization must include grain-first redesign.

Calculation Views: Join Cardinality and Engine Transitions

Graphical Calculation Views introduce another source of instability: cardinality metadata and engine transitions.

When join cardinality is left as "n..m," the optimizer assumes worst-case explosion. When incorrectly set as "1..1," it may reorder joins aggressively and defer filtering. Both mistakes alter the plan's shape.

A frequent migration pattern is to replicate legacy multi-join views into a single Calculation View with multiple projection nodes feeding a central join node. Under load, the join engine allocates hash tables proportional to pre-aggregation cardinality. If aggregation nodes sit above that join, each concurrent session constructs its own large intermediate state before reduction, multiplying memory pressure across sessions.

Execution-plan stability requires:

• Accurate cardinality annotation
• Projection pruning enabled
• Calculated columns minimized before aggregation
• Table functions are used sparingly and only when logic cannot be expressed declaratively

Table functions introduce optimization boundaries. When overused, they prevent join reordering and predicate pushdown across function boundaries, fragmenting the operator graph.

SQLScript Procedures and Optimization Boundaries

SQLScript introduces imperative constructs that can fragment optimization. For example:

IF :p_flag = 'Y' THEN
    SELECT ...
ELSE
    SELECT ...
END IF;

Branching logic produces separate subplans. Under concurrency, plan cache fragmentation increases. Each branch may generate a distinct plan variant, multiplying the memory footprint.

Similarly, cursor-based loops imported from legacy logic disable set-based optimization. Even when pushdown is nominally achieved, the presence of row-by-row constructs forces materialization.

Execution stability improves when:

• Set-based transformations replace procedural loops
• Conditional logic is expressed via predicates rather than branches
• Intermediate variables are minimized to avoid implicit materialization

The goal is a single coherent operator graph with predictable cardinality flow.

Observability: PlanViz as a Stability Instrument

PlanViz is not a tuning tool alone. It is a stability diagnostic instrument. Stable models show:

• Early aggregation nodes
• Reduced intermediate row counts after each operator
• Limited engine transitions between OLAP and Join engines
• Consistent estimated vs actual row counts

Unstable models show:

• Large intermediate nodes before aggregation
• High variance between estimated and actual cardinalities
• Multiple hash join operators with spill risk
• Repeated plan variants under similar parameter shapes

Stability is observed by running parameter sweeps under controlled concurrency and comparing plan shapes, not just runtimes.

State Amplification Under Concurrent Workloads

When intermediate result sets scale with the input window size, concurrent sessions amplify state multiplicatively. If one session produces 200 million intermediate rows before aggregation and five sessions overlap, each constructs its own intermediate state, causing cumulative memory allocation that triggers throttling or spill behavior despite acceptable single-session performance.

Stabilized models collapse grain early, producing intermediate datasets proportional to grouped dimensions rather than raw transaction volume. Concurrency then scales linearly instead of exponentially.

This distinction is architectural. It cannot be solved with indexes, hints, or hardware.

Engineering Stability Instead of Translating Logic

Most unstable migrations are not slow because SAP HANA is inefficient. They are unstable because the reduction was deferred.

When aggregation happens after cardinality amplification, the intermediate state scales with raw transaction volume. Under concurrency, that decision multiplies memory pressure across sessions. The system behaves exactly as modeled.

Pushdown-first modernization succeeds when reduction precedes enrichment and when the operator graph is engineered for concurrency, not just correctness.