Optimizing Complex Queries in SAP HANA for Real-Time Analytics

SAP HANA is a high-performance in-memory relational database. However, even with the power of in-memory processing, poorly structured queries containing heavy Joins, nested sub-selections, or inefficient calculations can degrade the performance of corporate ERP instances and freeze Power BI dashboards.

In this technical article, I detail some key SQL syntax and tuning strategies in SAP HANA that I applied to optimize analytical reports and data pipelines.

1. Avoid Joining Row and Column Store Tables

SAP HANA stores tables physically in two formats: Row Store and Column Store. Performing heavy Joins between tables of opposite types requires the database engine to perform in-memory conversions at runtime. * Best practice: Keep analytical queries operating preferentially on Column Store tables.

2. Prefer Native HANA Functions

Instead of performing string manipulations and complex date conversions using generic SQL logic, use optimized HANA kernel functions, such as DAYS_BETWEEN or UTCTOLOCAL.

-- Optimized invoice aging calculation example
SELECT 
    "DocEntry",
    "DocDate",
    DAYS_BETWEEN("DocDueDate", CURRENT_DATE) AS "DaysOverdue"
FROM "OINV"
WHERE "DocStatus" = 'O';

Read the plan before blaming Power BI

DAYS_BETWEEN only helps if the optimizer does not materialize all of OINV. In hdbsql, ask for the plan and look for a column scan instead of a row/column conversion in the middle of the join.

hdbsql -n hana-prod:30015 -u ANALYTICS -p "$HANA_PASSWORD" <<'SQL'
EXPLAIN PLAN FOR
SELECT "DocEntry",
       DAYS_BETWEEN("DocDueDate", CURRENT_DATE) AS "DaysOverdue"
FROM "OINV"
WHERE "DocStatus" = 'O';
SQL

If the plan shows COLUMN SEARCH on OINV and the DocStatus predicate applied early, the Power BI refresh stops waiting on a mixed-store join. That is the cut that took the load from 15 minutes to under 45 seconds.

---

Conclusion

The query optimization reduced the Power BI load refresh time from over 15 minutes to less than 45 seconds, relieving stress on production ERP CPUs.