The optimal performance of SAP HANA is crucial for efficient data processing and analysis. It’s about monitoring resources like memory, analyzing workloads and evaluating costly SQL statements. This article provides a comprehensive guide to optimizing HANA performance through schema design, SQL query optimization, SQL script performance and calculation views.
Optimal SAP HANA performance requires careful planning and optimization at various levels, from schema design to SQL queries and calculation views. By implementing the best practices described, SAP HANA specialists can maximize the efficiency and speed of their data processing.
We are happy to support you in the following areas:
Schema Design
| Table Type | Choosing the right table type is crucial. SAP HANA supports both row-based and column-based storage, with column-based storage being preferred for most scenarios due to its excellent compression and fast query performance. Row-based tables are better suited for small data sets or high-frequency transactional updates. |
| Index | Indexes play an important role in speeding up queries. Primary key indexes and secondary indexes, including multi-column indexes, help to reduce access times and increase query efficiency. However, it is important to find the balance between the number of indexes and the associated storage and maintenance overhead. |
| Partitioning | The partitioning of tables enables the horizontal distribution of data, which is particularly useful in distributed HANA landscapes. Proper partitioning can speed up queries by searching only relevant data partitions. However, it is important that the partitioning criteria correspond to the most frequent and expensive queries. |
| Cache | The use of cache mechanisms can significantly improve query performance. SAP HANA offers various cache options to accelerate data access and shorten response times. |
| Denormalization | Denormalization can improve performance by reducing the number of joins required. By merging data into a single table, query times can be reduced, although this can lead to increased redundancy and potential data inconsistencies. |
SQL Query Performance
| SQL Processing Components | SQL processing in HANA comprises various components, including the parser, optimizer and execution engine. Each of these components plays a key role in the efficient processing of SQL queries. |
| HANA SQL Optimizer | The HANA SQL Optimizer consists of a rule-based and a cost-based optimizer. The rule-based optimizer applies predefined rules, while the cost-based optimizer compares the execution costs of different query plans to select the most efficient plan. |
| Tools | Various tools are available for the analysis and optimization of SQL queries. The SQL Plan Cache saves past query plans, while the Explain Plan Tool visualizes the execution strategy of a query. The Plan Visualizer and SQL Traces provide detailed insights into query execution. Debug traces such as the Step & Time Debug Trace help with detailed troubleshooting. |
| SQL Tuning Guidelines | To improve SQL query performance, implicit type conversions should be avoided, inefficient predicates in joins and EXISTS/IN queries should be optimized and set operations should be reduced. Multi-column joins can be optimized by using hints to adjust the query plan. |
SQL Script Performance
| SQLScript Optimizer | The SQL Script Optimizer analyses and optimizes SQLScript procedures in order to minimize execution times. |
| SQLScript Performance Analysis | The performance analysis of SQLScript includes checking SQL traces and plan profiles as well as the use of monitoring views such as M_ACTIVE_PROCEDURES. |
| SQLScript Performance Guidelines | To improve SQLScript performance, the complexity of SQL statements should be reduced, dependencies between statements minimized and expensive queries avoided. The use of NO_INLINE hints can influence the inlining strategy and the use of cursors should be avoided. |
Calculation Views
| Setting Join Cardinality | Defining the join cardinality helps the optimizer to choose the best execution strategy. |
| Optimizing Join Columns | Optimizing join columns can improve query performance by avoiding unnecessary data accesses. |
| Using Dynamic Joins | Dynamic joins enable flexible query optimization based on the data available at runtime. |
| Union Node Pruning | Trimming union nodes reduces the amount of data that needs to be processed and thus improves query performance. |
| Push Down Filters in Rank Nodes | Pushing down filters in rank nodes enables more efficient data processing by moving filter operations as close as possible to the data source. |
