SAP HANA Developer Performance Tuning: Practical SQLScript and Calculation View Techniques

SAP HANA developer performance tuning is most effective when it begins with evidence: the executed statement, its data volume, its execution plan, and the operator consuming the most time or memory. Optimize the part of the data flow that limits the result, then verify the change with the same workload and representative data.

This guide focuses on application SQL, SQLScript, calculation views, and the operational checks needed to validate improvements on SAP HANA on-premise systems.

01Start with a measurable performance problem

Begin with a reproducible statement or application request. Record the input parameters, row counts, execution time, returned data volume, and the time of day. A statement that is fast with a small development dataset can behave differently when joins, filters, and aggregations process production-scale data.

Use SAP HANA database explorer to run a controlled test and inspect the execution plan. SAP HANA cockpit is useful for correlating a slow request with system activity, service memory, and workload conditions. Keep the original version of the statement so that the before-and-after comparison uses the same inputs.

A useful performance record contains:

• The complete SQL statement or SQLScript procedure call
• Parameter values and the expected result set
• Execution time, processed rows, and returned rows
• The relevant calculation view or table function
• The execution plan before the change
• The execution plan after the change

The goal is an evidence-based baseline, not a change based only on intuition.

02Reduce data before expensive operations

Push selective filters as close to the source as the model permits. Filtering early reduces the rows that later joins, projections, aggregations, and calculations must process. Apply predicates to source columns rather than filtering a derived result when the business logic allows it.

Select only the columns required by the next operation or by the final consumer. Wide intermediate results increase memory traffic and can make joins and aggregations more expensive. Avoid carrying descriptive attributes through a long calculation view when they are not needed for the final output.

Join on compatible keys with clear cardinality. Validate that the join does not multiply rows unexpectedly. A duplicated result can increase both processing time and memory consumption, even when the final query later removes duplicates.

When a query needs only to determine whether related data exists, use an existence-oriented design where appropriate instead of returning and aggregating all matching rows. Keep the predicate logic visible so that the optimizer can evaluate the restriction as early as possible.

03Tune SQLScript execution

SQLScript performance optimization starts with the data flow between statements. Prefer declarative SQL operations for set-based work. Use imperative control flow for orchestration or logic that genuinely depends on sequential decisions.

Avoid row-by-row processing for large datasets. A loop that reads or changes one record at a time creates repeated statement execution and prevents the database from optimizing the complete set operation. Replace it with a set-based insert, update, delete, join, or aggregation when the business rule permits.

Keep intermediate table variables purposeful. Materialize a result when it improves reuse, isolates a costly operation, or makes diagnostics clearer. Do not create several large intermediate results merely to break a simple data flow into additional statements.

For procedures and table functions, define the smallest useful input and output contract. Remove unused output columns, avoid unnecessary conversions, and keep filtering conditions close to the source data. The related guide to SAP HANA SQLScript procedures provides a focused reference for procedure design and execution behavior.

04Improve calculation view execution

For hana calculation view performance, inspect the complete model rather than tuning only the final consuming query. In a graphical calculation view, review projections, joins, unions, aggregations, calculated columns, filters, and semantics in execution order.

Place filters at the earliest node where they preserve the intended result. Keep projections narrow. Use aggregation only when the required grain is clear, and verify that measures and attributes have appropriate semantics before moving aggregation earlier in the model.

Review joins for correct cardinality and join type. A wrong cardinality declaration can affect both result correctness and optimization. A join that is logically optional may require different handling from a join that must find a matching record for every input row.

Use SQL-based calculation views or table functions when the required logic is easier to express and validate in SQLScript. Use graphical models when their structure improves reuse, lineage, and maintainability. The comparison in SAP HANA graphical vs. SQL calculation views helps map the modeling choice to the workload.

05Use execution plans to find the bottleneck

Read an execution plan from the expensive operator outward. Look for large intermediate row counts, broad scans, expensive joins, late filters, repeated calculations, and aggregations that process more data than the result requires.

Compare estimated and actual behavior where the tool exposes both. A major difference can indicate stale or incomplete optimizer statistics, skewed data distribution, an unexpected predicate, or a cardinality problem in the model. Treat that difference as a diagnostic signal and verify it with the underlying data.

Change one meaningful factor at a time. For example, move a filter, narrow a projection, revise a join, or replace row-by-row logic. Re-run the same workload after each change so the measured effect remains attributable.

Use SAP HANA calculation views as the model-level reference when the slow statement originates in a reusable analytical model.

06Measure memory and data volume

Performance problems can be CPU-bound, memory-bound, I/O-bound, or caused by concurrency. Inspect the host and service metrics before assigning the cause to SQL alone. Host memory information is available through SYS.M_HOST_RESOURCE_UTILIZATION; useful columns include HOST, USED_PHYSICAL_MEMORY, FREE_PHYSICAL_MEMORY, ALLOCATION_LIMIT, INSTANCE_TOTAL_MEMORY_USED_SIZE, and INSTANCE_TOTAL_MEMORY_ALLOCATED_SIZE.

For service-level analysis, SYS.M_SERVICE_MEMORY provides service memory information, including TOTAL_MEMORY_USED_SIZE. Use the service context to determine whether the pressure is concentrated in a particular database service or distributed across the system.

For column-store table volume, M_CS_TABLES provides MEMORY_SIZE_IN_TOTAL and RECORD_COUNT. These values help identify large tables and high-volume objects that deserve focused analysis. Compare table size with the rows actually required by the workload; a large table is not automatically the cause of a slow request.

Avoid treating a memory reduction as a complete optimization if the query still scans unnecessary data or performs an expensive join. The durable improvement is usually a smaller data flow combined with a plan that matches the access pattern.

07Design predicates for column-store access

Use predicates that match the stored data types and avoid wrapping filtered columns in functions when an equivalent direct predicate is available. Explicitly convert input parameters at the boundary of the procedure or application so comparisons remain type-consistent.

Use range predicates carefully for dates, timestamps, and numeric keys. Define the lower and upper boundary deliberately, especially when a time interval crosses days or includes fractional seconds. Test boundary values as part of the performance check because an incorrect range can produce both wrong results and misleading measurements.

Avoid applying a broad OR condition when separate, simpler branches or a semantically equivalent set operation can reduce the scanned data. Confirm the result equivalence before changing the structure.

08Handle aggregation and joins deliberately

Choose the aggregation grain before writing the query. Grouping by an unnecessary attribute increases the number of groups and may prevent useful reduction. Aggregating too early can also change the result when later joins need detail-level rows, so validate the business grain at every stage.

For joins, check key uniqueness on both sides and estimate the expected row multiplication. Resolve duplicate source keys before a join when the business rule requires one row per key. Use a pre-aggregation or a carefully defined existence check when returning every detail row is unnecessary.

Keep expressions deterministic and simple where possible. Repeated complex expressions in join or filter logic can increase work and make plans harder to interpret. Calculate a value once when reuse is clear and the intermediate result remains appropriately small.

09Verify the change safely

A tuning change is complete only after functional and performance verification. Run the same test inputs against the original and revised implementations, then compare result counts, key values, null handling, totals, and execution time.

Test cold and warm conditions when the workload is sensitive to caching. Test realistic concurrency when the issue occurs in an application environment rather than in an isolated database explorer session. Record the plan and relevant memory observations for both versions.

Deploy the smallest change that solves the measured bottleneck. Keep a rollback path for calculation view revisions, SQLScript changes, and application query changes. Monitor the production workload after deployment and confirm that the improvement did not shift the cost to another service or downstream consumer.

10A repeatable tuning workflow

Use this sequence for a slow SQL statement, procedure, table function, or calculation view:

1. Capture the exact workload and establish a baseline.
2. Inspect the execution plan and identify the dominant operator.
3. Check row counts and data volume at the relevant model stages.
4. Move selective filters and projections closer to the source.
5. Review join keys, cardinality, aggregation grain, and calculated expressions.
6. Replace row-by-row SQLScript with set-based logic where possible.
7. Re-run the same workload and compare correctness, time, rows, and memory.
8. Test representative concurrency before production deployment.
9. Document the cause, change, measurement, and rollback path.

This workflow keeps tuning focused on a verified bottleneck and makes later regression analysis much easier.

11Common performance symptoms and actions

12Operational checklist

Before closing a performance incident or change request, confirm the following:

• The baseline includes the actual statement, parameters, and workload conditions.
• The bottleneck is supported by an execution plan or measured resource data.
• Filters, projections, joins, and aggregations match the intended data grain.
• SQLScript uses set-based processing for large data volumes where appropriate.
• Calculation view changes preserve semantics and expected result cardinality.
• Host and service memory observations use the relevant system views and columns.
• Functional regression tests cover nulls, duplicates, boundaries, and representative totals.
• Production monitoring confirms the improvement after deployment.

The most reliable SAP HANA developer performance tuning combines model clarity, measured execution behavior, and controlled verification. Optimize the data flow first, then validate the result under the workload that matters.