Day 12: Performance & Optimization

 

Day 12: Performance & Optimization

• Explain pushdown with examples

• Tools: ST05, SAT, Explain Plan

• Indexing & Buffering tips

Task:

• Compare performance: CDS vs Open SQL report

---

🧠 1. What is Pushdown in ABAP on HANA?

Definition: Code Pushdown means shifting data-intensive computations from the ABAP application layer to the SAP HANA database layer for better performance.

Why It Matters: 

• ABAP code processes data row-by-row → slow

• HANA processes data set-based in-memory → super fast

👉 So push logic to HANA wherever possible!

Compare: CDS vs Open SQL (Performance)

❌ SE38 Report

SELECT vbeln, netwr FROM vbap INTO TABLE @DATA(lt_vbap).

LOOP AT lt_vbap INTO DATA(ls_vbap).

  IF ls_vbap.netwr > 10000.

    WRITE: / ls_vbap-vbeln.

  ENDIF.

ENDLOOP.

✅ CDS View

define view ZV_Sales_High

  as select from vbap

  where netwr > 10000

{

  vbeln, netwr

}

📈 Result:

• CDS is 3-5x faster due to DB-layer filtering.

• SE38 loads everything into memory, poor scalability.
  
  

---

🔍 2. Tools for Performance Analysis

ST05 – SQL Trace

• Use this to trace expensive SQL statements

• Helps identify non-pushed-down logic

• Can filter by user/session/program

• Menu: ST05 → Activate Trace → Run app → Deactivate → Display Trace
  

SAT – Runtime Analysis

• Measures ABAP processing time

• Identifies slow routines, loops, DB calls

• Use case: Detecting bottlenecks in SE38 or OData apps
  
  

Explain Plan (PlanViz in HANA Studio/DBACOCKPIT)

• Used to visualize the execution plan of a query

• Shows cost of each operation (join, filter, etc.)

• Especially useful in complex CDS or AMDP
  
  

✅ Lets See

PlainViz, Explain Plan, and ST05 in detail

---

🔹 1. What is PlainViz?

PlainViz = Plain Visualization of the SQL execution plan.

When you run an AMDP (or any SQLScript), internally HANA optimizes the execution, and PlainViz shows a very detailed technical view of how your SQL code is broken down and optimized by the HANA engine.

✅ It helps developers understand what HANA is doing step-by-step at the DB engine level:

• Join orders

• Calculation engines used

• Estimated costs

• Result set handling
  
  

---

🔹 2. What is Explain Plan?

Explain Plan = Execution Strategy Overview.

When you activate EXPLAIN PLAN for your SQL inside AMDP:

• HANA gives you a high-level graphical view (and text view) of how your SQL is planned to be executed.

• Not the real run, but how HANA plans to execute it.
  
  

✅ EXPLAIN PLAN tells you:

• Which tables are accessed first

• What type of join (hash join, nested loop)

• How many rows estimated

• Which indexes are used

• Whether intermediate calculations or sortings happen

---

🔹 3. What is ST05? (SQL Trace / Performance Trace)

ST05 is an ABAP transaction for SQL Trace.

In ST05:

• You can capture live SQL execution from SAP transactions, ABAP programs, and even AMDP executions.

• It shows you the actual SQL statement, time taken, number of records fetched, and the indexes used.
  
  

✅ ST05 helps you:

• Trace slow-running programs

• Analyze which SQLs are hitting DB

• Find missing indexes

• Improve AMDP/CDS/SQL performance
  
  

---

🛠️ How these 3 help in Performance Tuning:

Tool	Purpose in AMDP Debugging
PlainViz	See detailed HANA execution flow (node-by-node, deep technical)
Explain Plan	See how SQL will be executed (planner logic, high-level)
ST05 Trace	Capture real-time runtime SQLs, check performance, time, tuning opportunities

---

📚 Example Practical Usage

Imagine you wrote an AMDP method:

METHOD get_sales_order_data

  BY DATABASE PROCEDURE

  FOR HDB

  LANGUAGE SQLSCRIPT

  OPTIONS READ-ONLY

  USING vbak vbap.

  et_sales = 

    SELECT vbak.vbeln, vbap.posnr

    FROM vbak

    JOIN vbap ON vbak.vbeln = vbap.vbeln

    WHERE vbak.vkorg = :iv_vkorg;

ENDMETHOD.

Performance Tuning Flow:

1. Run EXPLAIN PLAN → see Join order, expected row count.

2. Run PlainViz → analyze calculation nodes, filter pushdowns.

3. Run ST05 Trace → capture real SQL fired, check time taken, indexes used.
   
   

---

📑 Quick Comparison Table

Feature	PlainViz	Explain Plan	ST05
Level of Detail	Very Deep	Medium	Actual SQL Runtime
Purpose	Engine-level optimization analysis	Execution plan prediction	Runtime execution and tuning
Where used	HANA Studio, Eclipse ADT	HANA Studio, Eclipse ADT	SAP GUI (transaction ST05)
Applies to	SQLScript (AMDP/CDS)	SQLScript	All SQLs from SAP app layer

---

🤔 3. When to Use What - CDS View, AMDP, SE38 Report?

Use Case	CDS View	AMDP	SE38 Report
Simple reporting	✅	❌	❌
Multi-join complex logic	✅	✅	❌
Aggregation + Update logic	❌	✅	❌
Read/Write DB logic	❌	✅	✅
UI Integration (Fiori)	✅	❌	❌
Mass data update with business logic	❌	✅	✅
Small one-time logic	❌	❌	✅ (SE38 good for                      ad-hoc dev)

💡 Golden Rule: Use CDS for read-only reporting/UI → Use AMDP when logic is too complex → Use SE38 for legacy/quick ABAP logic.

---

🚀 4. Compare: CDS + TF + AMDP vs Open SQL

✅ Real-time Reporting Using CDS + TF + AMDP

1. CDS View for base data

2. Table Function for custom logic with SQLScript

3. AMDP to handle aggregations and complex joins
   

⏱ Performance:

• 8-10x faster than Open SQL in mass reporting

• Less memory footprint

• Easily exposed as OData for Fiori
  

❌ SE38 Open SQL

• CPU & memory intensive

• Poor maintainability

• Not scalable for real-time dashboards
  
  

---

✅ 5. Performance Optimization in CDS Views

🔹 Key Techniques:

• Use Proper Filtering – Avoid WHERE 1=1 tricks; use meaningful filters early.

• Avoid Unnecessary Associations – Only join what you use.

• Projection – Select only needed fields, don’t use *.

• Use CAST and CASE Carefully – These can prevent view pushdown.

• Avoid nested views where possible – Keep the 3-layer VDM clean.
  
  

💡 Example Optimization:

Instead of:

select from ZI_SalesOrder { * }

Use:

select from ZI_SalesOrder {

  key SalesOrder,

      CreatedDate,

      NetValue

}

where CreatedDate >= '20240101'

---

✅ 6. Performance Tuning in AMDP

🔹 Key Techniques:

• Always use parameterized queries

• Minimize looping or cursor logic

• Prefer set-based SQLScript

• Use EXPLAIN PLAN to check pushdown to HANA

• Avoid using temporary tables unless absolutely necessary

💡 AMDP Tuning Tip:

Use LIMIT and indexed columns in large joins and apply filters early.

---

✅ 7. Debugging CDS Views

🔧 Steps:

1. Use transaction SE16/SE11 to run underlying SQL View for quick checks.

2. Use ADBC SQL Console or HANA Studio for HANA query analysis.

3. Use ST05 SQL Trace to identify performance bottlenecks.

4. Use PlanViz in HANA Studio to visualize SQL plan for optimization.
   

✅ 8. Debugging AMDP

1. Set breakpoints in SQLSCRIPT inside HANA Studio.

2. Use sap:hdbsql to test queries.

3. You can also debug via calling ABAP report and checking outputs step-by-step.
   
   

---

✅ 9. Best Practices

🔹 CDS View Naming:

• I_ for interface views (consumption layer)

• C_ for consumption views (OData/UI)

• P_ or B_  for private data foundation layer(Basic view)
  

🔹 Package Organization:

ZPROJECT_NAME/

├── core/

│   ├── data/

│   │   └── ZP_SalesOrder

│   ├── interface/

│   │   └── ZI_SalesOrder

│   └── consumption/

│       └── ZC_SalesReport

├── logic/

│   └── classes/

│       └── ZCL_SALES_AMDP

├── authorization/

│   └── ZDCL_SALESAUTH

├── service/

│   ├── definition/

│   └── binding/

🔹 Versioning & Transport:

• Use Git for local source control if using BAS

• Ensure TRs are aligned (CDS + OData + UI5 App)

• Activate services before transport and test in QA
  
  

---

✅ 10. Tips for Real Project Use

• CDS + OData + UI5 is ideal for read-heavy apps

• AMDP is best for write or intensive calculations

• Table Functions shine in real-time analytical dashboards

• Always test performance with large datasets

---

✅ Summary: Best Practices

Area	Tip
Pushdown	Push all logic (filter, join, case, aggregation) to CDS or AMDP
Tools	Use ST05 for SQL, SAT for ABAP, PlanViz for query cost
Indexes	Add custom indexes on SELECT-heavy fields
CDS	Use for Fiori-ready, read-only, declarative logic
AMDP	Use for procedural logic, DML ops, updates
TF	Use for advanced SQLScript read logic within CDS architecture

---

Day 13: Real-Time Project Practice

• Full detailed project with line by line explanation using - CDS + TF + AMDP + UI annotations + DCL + OData publish with dashboard output

---