Belajar ABAP Part 14: Performance Tuning & HANA Optimization — Tips, Tools, dan Contoh

Belajar ABAP Part 14: Performance Tuning & HANA Optimization

Ringkasan: Fokus part ini: teknik tuning ABAP + HANA untuk aplikasi S/4HANA. Intinya: push logic ke DB (code pushdown) bila relevan, hindari loop di ABAP untuk operasi data besar, gunakan CDS/AMDP dengan bijak, dan pakai alat profiling (PlanViz, ST05, SAT) untuk menemukan bottleneck. Disertai contoh before/after dan checklist tuning.

1. Prinsip Utama Tuning

Measure first: jangan guesstimate — profil untuk lihat hotspot.
Pushdown where possible: gunakan CDS/AMDP agar DB melakukan kerja berat.
Avoid row-by-row: minimalisasi nested loops yang baca DB di tiap iterasi.
Right data, right place: pilih column-store, partitioning, atau caching sesuai pola akses.

2. Tools untuk Diagnosis

Tool	Fungsi	Kapan pakai
ST05 (SQL Trace)	Melacak SQL yang dijalankan oleh ABAP	Cari query lambat / banyak eksekusi
SAT / SE30	Runtime analysis (time breakdown)	Profiling performa prosedural ABAP
ST12	Gabungan trace SQL+ABAP untuk root-cause	Investigasi mendalam
PlanViz (HANA)	Analisa execution plan HANA	Optimasi query/AMDP/SQLScript
HANA Studio / Eclipse (HANA tools)	Profiling, index, partition, CDS activation	DB-side tuning
ST10 / DBACOCKPIT (ST04)	Analisa buffer & statistik DB	Ops DB/DBA checks

3. ABAP-level Best Practices

3.1 Open SQL yang efisien

Pakai field list explicit — SELECT matnr, mtart FROM mara bukan SELECT *.
Gunakan UP TO n ROWS untuk preview atau sampling.
Tambahkan kondisi WHERE yang mengurangi rows sebanyak mungkin (filter by key/partitions)
Gunakan INTO TABLE ketimbang loop + SELECT satu per satu.

3.2 FOR ALL ENTRIES vs JOIN

FOR ALL ENTRIES berguna tapi sering disalahpakai. Prinsip:

Gunakan hanya jika internal table tidak kosong.
Jangan pakai jika internal table sangat besar (ribuan baris) — lebih baik create temporary table in DB (CDS/AMDP) atau gunakan joins.


" risky: many entries => big IN-list
SELECT * FROM ekpo INTO TABLE lt_items
  FOR ALL ENTRIES IN lt_po
  WHERE ebeln = lt_po-ebeln.

Jika lt_po besar, buat CDS/AMDP atau insert ke Z-temp table dan JOIN di DB.

3.3 Buffering & Caching

Gunakan table buffering untuk small master tables (SE11 → Technical Settings → Buffering).
Gunakan ABAP application buffers (SET/GET) bila data jarang berubah dan dibaca sering.
Hati-hati: cache stale issues — set TTL/invalidasi sesuai kebutuhan.

3.4 Reduce Network Round-trips

Ambil data dalam bulk (SELECT ... INTO TABLE) daripada banyak CALL FUNCTION/remote roundtrip.

4. CDS / AMDP / Pushdown Best Practices

4.1 Gunakan CDS untuk:

Joins/associations dan basic aggregasi
Expose OData / Fiori (consumption view + annotations)
Parameterize queries (consumption view parameters untuk pruning)

4.2 Gunakan AMDP ketika:

Logika procedural/iteratif yang sulit di-express di CDS
Transformasi/ETL/komputasi berat di DB
Anda perlu kontrol SQLScript (CE functions, window functions, temporary tables)

Note: selalu cek PlanViz untuk melihat apakah CDS/AMDP benar-benar melakukan pushdown dan tidak memaksa row-by-row transfer ke ABAP layer.

5. HANA-specific Optimization

5.1 Column Store vs Row Store

HANA column-store unggul untuk scanning, aggregation, compression. Pastikan tabel besar berada di column store (default di HANA) — hindari row-store untuk analytic workloads.

5.2 Compression & Dictionary Encoding

Column-store compresses data; gunakan low-cardinality columns as dictionary keys. Compression mengurangi I/O & memory footprint.

5.3 Partitioning

Partitioning memudahkan pruning: range partition by date, hash partition by company, dll. Partitioning membantu query hanya scan partition relevan.

5.4 Use CE Functions & Window Functions

HANA menyediakan Calculation Engine (CE) functions dan window functions (ROW_NUMBER, RANK) yang sering jauh lebih cepat dibandingkan SQL naif. Gunakan SQLScript/AMDP untuk memanfaatkan fungsi ini.

5.5 Avoid UDFs / Scalar UDFs where possible

Scalar UDFs can cause performance penalties (row-context). Prefer built-in CE functions or inline expressions.

6. Profiling HANA Queries (PlanViz)

Ambil SQL dari ST05 atau gunakan CDS explain plan.
Load SQL ke PlanViz (HANA Studio / Eclipse) — lihat execution steps: CE joins, table scan, column access.
Perhatikan expensive operators: CE_MULTI_JOIN, CE_JOIN, CE_AGGREGATE, CE_CALCULATION.
Optimisasi: add predicate pushdown, change join order, add partition pruning filters.

7. Before / After Example: Replace Loop + Select with Single SQL (ABAP)

Before (slow — many DB roundtrips):


LOOP AT lt_po INTO ls_po.
  SELECT * FROM ekpo INTO TABLE lt_items WHERE ebeln = ls_po-ebeln.
  " process lt_items
ENDLOOP.

After (fast — single DB call using JOIN or FOR ALL ENTRIES carefully):


" Option A: single join (preferred)
SELECT e~ebeln, e~ebelp, e~matnr, h~lifnr
  FROM ekpo AS e
  INNER JOIN ekko AS h ON e~ebeln = h~ebeln
  INTO TABLE lt_result
  FOR ALL ENTRIES IN lt_po
  WHERE e~ebeln = lt_po-ebeln.

" Option B: single select with IN-list (if lt_po small)
SELECT * FROM ekpo INTO TABLE lt_items WHERE ebeln IN @lt_po_ebeln.

Tip: prefer JOIN or CDS view that returns already-joined data. If using FOR ALL ENTRIES, ensure lt_po is not initial and de-duplicated.

8. Example: CDS Pushdown vs ABAP Aggregation

Prefer CDS aggregation:


-- CDS
@AbapCatalog.sqlViewName: 'ZV_SALES_SUM'
define view ZCDS_SALES_SUM as select from vbak {
  key kunnr,
  sum( netwr ) as total_sales
}
group by kunnr;

Instead of pulling rows then aggregate in ABAP (slow).

9. Index & Statistics

HANA tidak bergantung heavy pada secondary indexes seperti DB row-stores — but still check if specific secondary indexes help for point queries.
Pastikan table statistics up-to-date (DBA job) agar optimizer bisa memilih plan terbaik.

10. Locking & Concurrency

- Minimalkan long-running transactions. - Untuk batch jobs, gunakan smaller commit intervals (COMMIT WORK) tapi hati-hati dengan partial state. - Pelajari isolation levels jika reading while writing heavy workloads (consistent snapshot behavior in HANA).

11. Operational Checks (Checklist cepat)

Profiling: jalankan ST05 / SAT untuk mendeteksi hotspot.
Ambil SQL & buka PlanViz — cek apakah pushdown terjadi.
Pastikan CDS/AMDP menggunakan parameters untuk pruning.
Ganti nested loops yang men-trigger DB call dengan single SELECT / JOIN / CDS.
Gunakan AMDP untuk heavy aggregations / CE functions.
Periksa partitioning, compression, dan column-store placement.
Perbarui statistik DB; cek HANA alerts/caches.
Uji di environment yang mirip production (volume testing).

12. Common Anti-Patterns

SELECT * di aplikasi produksi.
Loop { SELECT ... } — row-by-row DB call (N+1 problem).
FOR ALL ENTRIES dengan tabel kosong (causes full scan) atau dengan very large table.
Scalar UDFs that execute per-row on DB side.
Long-running transactions holding locks during heavy writes + reads.

13. Quick Reference: Commands & Tools

ST05 — start/stop SQL trace, analyze slow statements
SAT / SE30 — runtime analysis for ABAP
ST12 — combined ABAP + SQL trace
PlanViz — analyze HANA execution plans
/HANA Studio / Eclipse — HANA trace, index, partition management
DBACOCKPIT / ST04 — DB level stats and space usage

14. Contoh Kasus Nyata (Before / After) — PO Aging

Masalah: report PO aging awalnya membaca semua EKPO & menghitung di ABAP → sangat lambat.

Before (ABAP aggregation):


SELECT * FROM ekko INTO TABLE lt_ekko WHERE bukrs = '1000'.

LOOP AT lt_ekko INTO ls_ekko.
  SELECT SUM( netwr ) INTO lv_sum FROM ekpo WHERE ebeln = ls_ekko-ebeln.
  " calculate aging in ABAP
ENDLOOP.

After (AMDP pushdown): gunakan AMDP/SQLScript to compute sums and buckets in DB then return small result set:


" AMDP returns aggregated buckets (small table)
DATA(lt_buckets) = zcl_amdp_po_age=>calc_po_aging( iv_days1 = 30 iv_days2 = 60 ).

Hasil: transfer data jauh lebih sedikit, CPU kerja HANA optimized for aggregations, report 10x+ faster tergantung volume.

15. Penutup & Praktik Rutin

Performance tuning adalah siklus: measure → optimize → verify → monitor. Buat baseline sebelum optimasi, dokumentasikan perubahan, dan lakukan regresi/performa test setelah deploy. Kolaborasi tim dev + DBA + BASIS + infra sangat penting buat hasil optimal.

👉 Lanjut ke: Belajar ABAP Part 15: Performance Tuning Lanjutan

Catatan: contoh-contoh di sini disederhanakan supaya mudah paham. Untuk optimasi production, selalu lakukan load testing di environment yang mirip production dan libatkan DBA HANA untuk rekomendasi level DB.

All About Programming

Tuesday, 23 September 2025