Optimizations can be added at different levels: database, connectivity, universe, and report. Improvements and additional tunings can be done during
the whole lifecycle of the solution with a minimal or even null impact on the final user workflows.
For SAP HANA universe to utilize in-memory technology, modifying few key parameters in the relational connection and data foundation
we can optimize performances by increasing processing speed.
To configure an HANA universe is in the relational connection. Double click the HANA.cnx connection and edit, enter the appropriate credentials for the HANA database is entered.
- 1. Array fetches size
- The array fetch size parameters controls how many lines are retrieved each time the client tool connects to the database. Increasing the value
enables the system to make fewer trips to retrieve the full data sets.
- The default is 10 but it can be increased to a maximum of 1000 for better performance. Higher values run faster queries, but require more memory on the client tool that is running the query.
- 2. JOIN_BY_SQL parameter – Yes
- A query may contain multiple SQL statements, when the JOIN_BY_SQL parameter is set to “NO” These multiple SQL statements are processed one at a time, and the results are aggregated in the client tool.
- When this parameter is set to “YES” the SQL statements are re-written into a single statement and the aggregation take place in the Database.
- This parameter to be configured is in the data foundation, Double click the HANA DF
View the properties for the data foundation à click properties à click the handle to re-size the window
à View the parameters for the data foundation à Click parameters
This makes the query faster and requires less memory from the client tools. Set the parameters to YES, save the changes.
It’s applicable both on tables and information models building a universe on HANA.