last update: 19th of November 2020
Increasing the SAP-NLS Performance
Table of Content
With the Introduction of smart data access (SDA) especially between SAP HANA and IQ, the data provisioning process can be optimized. Never the less, some additional Parameters have to be introduced on the ABAP and HANA Backend as well.
The Implementation of the SDA between SAP HANA and IQ is already discussed in the – SAP First Guidance – SAP-NLS Solution with SAP IQ This would be a mandatory task first.
for General Information about the SAP-NLS Solution please visit – SAP-NLS Solution for SAP BW
Recommended SAP Corrections (SAP BW 7.40/7.50/BW4H)
You can use the SAP Launchpad – https://launchpad.support.sap.com/#/mynotes?tab=Search to select the necessary SAP Notes for your SAP System and SP Level:
As a general reference please consider the following knowledge base article on SAP NLS IQ performance:
Note 2371160 – FAQ: BW archiving to SAP IQ performance considerations
Performance in writing Archives – see also NLS load parallelization
Note 2109015 – Continuation of Archiving Requests for Copy, Verification, and Deletion Phase in parallel Note 2525213 – NLS archiving performance with SAPIQDB_DIAG.log showing batch insert entries
Settings for SAP IQ for SAP-NLS
SQL Anywhere Server 17.0 – DB start options IQ PAGE SIZE Parameter Guidelines Catalog Page Size Option IQ Performance Options – on DB start additional settings for the SQL anywhere interface: -gss: This parameter sets the catalog (Sql Anywhere) stack size for catalog threads. For all 64-bit UNIX platforms: Default=Min = 1 MB. Max = 8MB. Considering some large or complex queries ,please set this value to 8MB -iqtss: Specifies the stack size, in KB, for server execution threads running either in the background or as part of a thread team assisting the main server connection thread. The default value is 512KB on 64-bit platforms. However, some very complex queries may return an error indicating that the depth of the stack exceeded this limit, so you may need increase this value to 2048. SAP IQ calculates the stack size of server threads using the formula: ( -gss + -iqtss) -iqmc: Specifies main buffer cache size in MB, you may set this value to 8192.
Finally, please consider the following SAP Note for introducing a new dbspace-oriented partitioning mode for SAP NLS IQ for optimized partition creation during archiving from BW
Note 2190504 – BW NLS IQ: New dbspace-oriented partitioning mode
If the SAP/Sybase IQ automated Installation routine is used, then these settings are automatically calculated, see – Q – the easy Installer for SAP Sybase IQ
Settings for SAP HANA together with NLS
semi_join_virtual_table_threshold virtual_table_format join_relocation fda_enabled
In Addition you can also set the remote connection timeout as follows in the HANA Studio with the SQL interface: ALTER SYSTEM ALTER CONFIGURATION (‘indexserver.ini’, ‘system’) set (‘smart_data_access’, ‘remote_conn_idle_timeout’) = ‘180’ with reconfigure;
With HANA 1.0 SP10 (Rev. 10x.x) you should also set the DML Mode to "readonly"
rsdb/supports_fda_prot = 0 rsdb/max_blocking_factor = 50 rsdb/max_in_blocking_factor = 100 (as of SAP Kernel 7.49, previously 1024) rsdb/prefer_join = 0 rsdb/prefer_union_all = 0 rsdb/prefer_in_itab_opt = 1 rsdb/prefer_join_with_fda = 1 (as of SAP Kernel 7.43)
Note 1987132 – SAP HANA: Parameter setting for SELECT FOR ALL ENTRIES
Optimize the SAP-NLS Solution for SDA with SAP HANA
Note 2130587 – SYB IQ: performance enhancement for LOAD statement (latest ASE/IQ LibDBSL) Note 2352696 – SAP HANA smart data access 2.0 (latest ODBC drivers for SAP HANA) Note 2445973 – SAP_IQNC 16.1 SP01 (Build 10531) Release Notes Information (latest ODBC drivers for SAP IQ)
With the Introduction of SAP HANA Smart Data Access (SDA), the data access to archived data can be generally optimized. For general information on how BW on HANA use HANA Smart Data Access please see the following Blog – SAP BW on HANA smart data access
Please note that especially selections on navigational attributes are considered with the automatic usage of semi-joins or joint relocation’s for the execution of queries based on SDA together with HANA and IQ. For more details see – SAP BW on HANA & HANA Smart Data Access – BEx Query Execution and Note 2156717 – NLS: queries with navigation attributes/hierarchy node restrictions are slow
In addition, the following specific SAP Notes for Nearline Storage and Smart Data Access apply:
Note 2100962 – FAQ: BW Near-Line Storage with HANA Smart Data Access: Query Performance Note 2165650 – FAQ: BW-Nearline-Storage with SAP HANA Smart Data Access Note 2214892 – BW HANA SDA: Process Type for creating Statistics for Virtual Tables Note 2202052 – BW Near-Line Storage with HANA Smart Data Access: Poor Query Performance with InfoCubes Note 2283055 – External SAP HANA view: object specific enforce SQL engine execution
