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.
