RAP / CDS / ODataadvanced

CDS Table Functions Part 4: Performance with HANA Plan Analysis

Key takeaway

Make table functions fast: why consumer filters may not be pushed down, how to design parameters and set-based SQLScript, how to measure with ST05, SQL trace and HANA PlanViz / explain plan, and how to compare against a CDS alternative.

Explanation

📌 KEY POINTS
✅ "Runs on HANA" ≠ "runs fast" — measure with the real consumer query.
✅ Put selective filters into parameters; don't rely on consumer WHERE being pushed down.
✅ Write set-based SQLScript: one SELECT, no cursors or row-by-row loops.
💡 Tools: ST05 (statement + time), explain plan, HANA PlanViz (operator-level analysis).

━━━━━━━━━━━━━━━━━━━━
▶ WHERE WE ARE
Parts 1–3 built a correct table function. This part makes it fast.

━━━━━━━━━━━━━━━━━━━━
▶ 1. WHY TABLE FUNCTIONS CAN BE SLOW

The function computes its full result, then the consumer's WHERE, ORDER BY and paging apply — if the optimizer cannot push those conditions into the SQLScript body.
Window functions (ROW_NUMBER) over all rows of a large table are expensive.
Imperative SQLScript (cursors, loops, scalar calls per row) prevents parallel set-based execution.
Joining the function result with large views can create big intermediate results.

━━━━━━━━━━━━━━━━━━━━
▶ 2. DESIGN RULES

Parameters for selective filters (company code, date range, supplier) — applied early inside SQLScript.
Filter before ranking/aggregation: WHERE first, then ROW_NUMBER over the reduced set.
Select only needed columns.
Avoid SELECT DISTINCT as a fix for duplicates — find the join causing them.
Prefer one declarative statement; if you need steps, use table variables (lt_open = SELECT …; RETURN SELECT … FROM :lt_open), which HANA can still optimize as a whole.

━━━━━━━━━━━━━━━━━━━━
▶ 3. MEASURING

ST05 SQL trace: which statement the consumer sends, execution time, number of rows.
Explain plan (from ST05 or the SQL console): join order and operators.
HANA PlanViz (via SAP HANA tools / DBA Cockpit, depending on your access): time and row counts per operator — shows where the cost is.
Test with production-like data volumes; small test systems hide problems.

━━━━━━━━━━━━━━━━━━━━
▶ 4. COMPARE WITH THE ALTERNATIVE
If the function exists for performance, build the CDS view entity version too and compare in the same system with the same parameters. Keep the faster, simpler one.

━━━━━━━━━━━━━━━━━━━━
▶ 5. CONSUMER SIDE

OData/Fiori: make sure mandatory parameters are supplied (defaults), and lists page with $top.
Analytics: avoid wrapping the function in views that immediately join large to-many data.

━━━━━━━━━━━━━━━━━━━━
▶ STEP BY STEP

Trace the consumer query with ST05; note time and rows.
Move the most selective consumer filter into a parameter; filter before ROW_NUMBER.
Re-trace; compare. Check PlanViz for the dominant operator.
Record before/after figures in the object documentation.

✅ CHECKPOINT: you can analyze and improve table function performance with evidence.

Code example

ABAP Code

" ===== SLOW: rank ALL requests, consumer filters afterwards =====" RETURN SELECT client, request_id, supplier, company_code,"               ROW_NUMBER() OVER ( PARTITION BY supplier ORDER BY created_on ) AS agerank"          FROM zpr_head"         WHERE client = SESSION_CONTEXT('CDS_CLIENT');" consumer: ... WHERE company_code = '1010' AND agerank <= 3"   -> window function over millions of rows, filter applied late " ===== FAST: selective parameter, filter first, then rank =====METHOD get_aging_rank BY DATABASE FUNCTION FOR HDB LANGUAGE SQLSCRIPT                      OPTIONS READ-ONLY                      USING zpr_head.  -- step 1: reduce the data early (table variable)  lt_open = SELECT client, request_uuid, request_id, supplier, created_on              FROM zpr_head             WHERE client       = SESSION_CONTEXT( 'CDS_CLIENT' )               AND company_code = :p_bukrs            -- parameter, not consumer WHERE               AND status       = 'S';   -- step 2: rank only the reduced set, return only needed columns  RETURN SELECT client,                request_uuid AS "REQUESTUUID",                request_id   AS "REQUESTID",                supplier     AS "SUPPLIER",                DAYS_BETWEEN( created_on, :p_keydate ) AS "DAYSOPEN",                ROW_NUMBER() OVER ( PARTITION BY supplier ORDER BY created_on ) AS "AGERANK"           FROM :lt_open;ENDMETHOD. " ===== AVOID: row-by-row processing =====" DECLARE CURSOR c FOR SELECT ... ;  FOR r AS c DO ... END FOR;   -> slow, not parallel " ===== Measurement log (keep with the object) =====" Scenario: Fiori list, company 1010, 2.4M rows in ZPR_HEAD" before: 4.8 s, 2.4M rows ranked     after: 0.35 s, 41k rows ranked" dominant operator before (PlanViz): window function over full table

Real project scenario

A dashboard based on a table function needed five seconds per call. The function ranked all 2.4 million requests, and the Fiori app then filtered by company code. PlanViz showed 90% of the time in the window function over the full table.
The team added a company-code parameter with a default from the user's settings, filtered before ranking using a table variable, and returned only the needed columns. Runtime dropped to about 0.3 seconds. The before/after figures were documented in the class header for future reviewers.

Common mistakes

Assuming SQLScript is automatically fast.
Relying on consumer WHERE for selective filters.
Ranking or aggregating before filtering.
Cursors and loops instead of set-based SQL.
Measuring with small test data only.
Hiding duplicates with DISTINCT.

Best practices

Measure with the real consumer query and realistic volumes.
Use parameters for selective filters; filter early.
Use table variables for readable, still set-based steps.
Analyze with ST05, explain plan and PlanViz.
Document measurements and compare with a CDS alternative.

Interview angle

Questions: "Why can a CDS table function be slow?", "How do you analyze its performance?", "How do you design parameters for performance?"
Strong answer: consumer filters apply to the computed result and may not be pushed into the function, so selective filters should be parameters applied early; avoid window functions over unfiltered data, cursors and loops; use set-based SQLScript with table variables. Measure with ST05, explain plan and PlanViz on realistic data; compare with a CDS view alternative.

Practice this lesson

Quick check

1. A table function returns 50 rows but processes 8 million because the consumer filters by company code afterwards. What is the best fix?

2. Which limitation distinguishes a CDS table function from a CDS view entity in terms of data manipulation?

3. Which statement correctly describes the performance characteristic of CDS table functions?

Interview questions

1. A colleague reports that a CDS Table Function is performing poorly. They tell you, 'It runs on the HANA database, so it should be fast, but it's not. We probably need to add a secondary index.' How would you respond, and what concrete steps would you recommend to investigate and resolve this performance issue?

Answer it in your own words first, then compare.

2. A table function behind a dashboard takes five seconds. How do you improve it?