Skip to Content
User Experience Insights
Author's profile photo Roland Kramer

Increasing the SAP-NLS Performance

last update: 2nd of October 2022

Increasing the SAP-NLS Performance

 

 

Blog 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/BW/4)

 

Version SP-Level Current SP (05/2022) Support
SAP BW 7.40 26 SAPKW74026 until End of 2020
SAP BW 7.50 24 SAPK-75022INSAPBW until End of 2027
SAP BW/4 1.0 20 SAPK-10020INDW4CORE until End of 2021
SAP BW/4 2.0 12 SAPK-20010INDW4CORE until End of 2024
SAP BW/4 2021 02 SAPK-30001INDW4CORE commitment until 2040
Components Category
BW-WHM-DST-ARC BW/4 only BW4-DM-DTO Program Error
BW-SYS-DB-IQ optional BW-WHM-DST-DTP Search Term
BC-SYB-IQ optional BW-WHM-DBA-ADSO archive, near-line, IQ

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:

General Performance Optimizations in SAP NLS (BW ABAP)

As a general reference please consider the following knowledge base article on SAP NLS IQ performance:

Performance in writing Archives – see also NLS load parallelization

Note 2109015 – Continuation of Archiving Requests for Copy, Verification, and Deletion Phase in parallel
Note 2371160 – FAQ: BW archiving to SAP IQ performance considerations
Note 2525213 – NLS archiving performance with SAPIQDB_DIAG.log showing batch insert entries

 


settings for SAP IQ for SAP-NLS

Check the SAP IQ database settings (based on SAP IQ 16.1 SP04 and higher)

Note 3017355 – SAP IQ 16.1 SP04 PLx – correct SAPIQDB.cfg settings
Note 3119008 – Configure SAP IQ and HANA for SDA/ODBC
Note 2767466 – Options to control client connection timeout in SAP IQ

  • 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

Blog – Tuning SAP IQ NLS in SAP BW on HANA and BW/4HANA

 


settings for SAP HANA together with NLS

Currently the following SDA parameters should/could be changed on the SAP HANA server:

  • remote_objects_tree_max_size
  • remote_conn_idle_timeout
  • semi_join_execution_strategies
  • semi_join_virtual_table_threshold
  • virtual_table_format
  • join_relocation
  • fda_enabled
  • enable_remote_cache
  • enable_remote_source_capability
  • default_connections_pool_max_size

In Addition you can also set the remote connection timeout in the HANA Studio with the SQL interface:

Note 2578523 – SAP HANA Smart Data Access – exceeded simultaneous SESSIONS_PER_USER limit

ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'system') set ('smart_data_access', 'remote_conn_idle_timeout') = '180' with reconfigure;

Note 2184030 – SAP HANA Smart Data Access: How to Increase the Number of Objects Displayed

ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'system') SET ('smart_data_access', 'remote_objects_tree_max_size') = '5000' WITH RECONFIGURE;

 

With HANA (all Versions), you should also set the DML Mode to “readonly” in the remote DS:

 


Currently the following parameters should be changed on the SAP ABAP server:

  • 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

 


The Reports RSDDB_INDEX_CREATE_MASS / RSDDB_LOGINDEX_CREATE

Note 2194638 – Report RSDDB_INDEX_CREATE_MASS: regenerate indices if invalid, independent of ‘skip_if_exists’ flag
Note 2379817 – DataStore Objects (advanced) with Near-Line Storage: Error during Extraction
Note 2607883 – Checking Column view and calculation scenario/ calculation model errors in BW Queries

 


The Report RSDA_DROP_TEMP_TABLES

Note 2708894 – Clean-up report for temporary lookup tables from IQ
Note 2198386 – BW HANA SDA: Performance Improvement for Creation of Database Statistics for Virtual Tables of Open ODS Views or NLS-Archives
Note 2288710 – BW HANA SDA: Create Database Statistics for Virtual Tables of Open ODS Views
Note 2431673 – RSSDA_CREATE_TABLE_STAT shows error: statistics cannot be found

 


The Report RSSDA_CREATE_TABLE_STAT

can be used to create database statistics for HANA Virtual Tables.

Note 1990181 – BW HANA SDA: Create Database Statistics for Virtual Tables of Open ODS Views and Near-Line Storage (NLS) Archives

HANA Virtual Tables are used in the context of HANA Smart Data Access. The execution time can be quite time consuming. As of HANA SP10, a new statistics type RECORD COUNT is available for virtual tables via a system view named SYS.DATA_STATISTICS.
This View offers for Example CREATE_TIME und LAST_REFRESH_TIME as columns.

 


improve nearline storage (NLS) load 

Note 2128579 – Data Load into SAP IQ during Copy Phase utilizes only one server-side Thread
Note 2364354 – SIQ: Support for aDSO and load striping
Note 2399003 – SIQ: Load fails with error: DBSQL_DUPLICATE_KEY_ERROR
Note 2420571 – SIQ: enhance performance of bulk updates and deletes
Note 2520897 – ODP package size and data load performance
Note 2902060 – Why the temp files created by DBSL to handle data loads from DTO into IQ are excessively large
Note 2941940 – Troubleshooting SAP API performance issues (Data Packages, Work Processes, RFC Connections, Partner Profiles)

With SAP Note 2128579 two additional parameters were introduced to significantly increase the LOAD statement for writing data into SAP-NLS.

Parameter LOAD_STRIPE_SIZE setting this parameter to a value n > 1 parallelizes the load.
Parameter LOAD_STRIPE_WITH is the parallel degree multiplied with SYBASE_IQ_BUFFER_SIZE
Parameter SYBASE_IQ_LOAD_DIR could be changed at the database connection level (DBCO) and is by default the data directory of the SAP Instance. If you plan to load a large amount of data, please make sure that you have enough space left, or specify another directory/device.

Please make sure, that there is enough space on the underlying file system to hold all the data of at least 1 Data Package per concurrently running Data Archiving Process.
As a rule of thumb the reserved space should be 1.5 times the maximum number of concurrently running copy phases times the average size of a Data Package (the maximum Data Package size can be configured within the Data Archiving Process; its system default is 2000 MB)

The LOAD_STRIPE_WITH can be increased up to the amount of physical CPU’s are available on the IQ server depending on the other server resources like file system, RAM, etc.

If you plan to unload several large objects at the same time, the value should be calculated accordantly and must not be higher than the available physical CPU’s.

LOAD_STRIPE_SIZE=4; LOAD_STRIPE_WIDTH=4

 


Optimizing F4 Help access for SAP-NLS

It is suitable to change the settings for the affected Dimension tables to a more optimized setting. This can be done via the “Provider-Specific” settings for the Dimensions within the InfoCube. By default these options are empty, which means the default settings of the individual InfoObjects for query definition and execution are take place.

Furthermore any reorganization of the dimension tables e.g. via transaction RSRV should be avoided, due to no benefit at all for the application rather to corrupt the F4 access to the SAP-NLS solution and this will result in wrong F4 values and much less performance.

As an example see the following screens for an optimized setting. For further information, please consult the help.sap.com pages.

 


Optimizing Query access for SAP-NLS

Note 2001947 – Switch for operations in SAP HANA
Note 2099102 – SFAE implementation of LOOKUP has poor performance
Note 2129546 – Extended pruning on time characteristics for Near-line data
Note 2156717 – NLS: queries with navigation attributes/hierarchy node restrictions are slow

Wiki – Improving BW Query Performance by Pruning

Queries on InfoCubes show poor performance since filter conditions are pushed as “SID-based” filters to the HANA database instead of using the “key-values” for filtering. With “SID-based” filters, the SID-table needs to be joined to the Virtual Table in order to execute the filter. This join adds complexity to the SQL-Statement which makes it more difficult to optimize the query in a federated database environment.

Note 2231332 – Control of Query Optimization on Near-line Storage on InfoProvider Level

This note adds a checkbox to the “Near-line Storage” tab of the Data Archiving Process (DAP) maintenance to switch query optimization on (this is the default) or off. You must activate the DAP in order to make your setting effective. If optimization is switched on, but query optimization is not configured or not available for the Near-line Connection query access will use the non-optimized implementation via the Virtual Provider interface utilizing the Near-line Provider implementation via the standard Near-line Interface. In case Smart Data Access is configured for the Near-line Connection also the name of the HANA Virtual Table is shown.

 


Roland Kramer, SAP Platform Architect for Intelligent Data & Analytics
@RolandKramer

 

“I have no special talent, I am only passionately curious.”

Assigned Tags

      14 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      That s an excellent document.

      I suppose it will be reviewed regularly to add/modify notes correction.

      There is two different name for LOAD_STRIPE_WIDTH. I suppose that one is the correct one.

      Also, would it be possible to have performance optimization with and without HANA.

      in other words, which recommendations we can used on a BW system for NLS while we dont use HANA.

      Regards,

      Adel

      Author's profile photo Roland Kramer
      Roland Kramer
      Blog Post Author

      Hi,

      Without using HANA all recommendations for SDA cannot be applied. The ABAP setting can be applied also and the parallelisation for the data relocation is DB independent.

      Best Regards Roland

      Author's profile photo John Barton
      John Barton

      SAP_BW_NLS_LOAD_STRIPE_WIDTH.png

      Author's profile photo Amit Tewatia
      Amit Tewatia

      Hi Roland,

      Can you please update on NLS compatibility with BI reports built on Universe.

      I am looking for option to enable NLS data usage in BO Universe reports.

      We have enable "Near-Line access switched on" under InfoProvider

      properties but that doesn't seems to be enough for fetching NLS

      archived data thru BO Universe webi reports.

      Regards,

      Amit T

      Author's profile photo Roland Kramer
      Roland Kramer
      Blog Post Author

      Hi,

      I doubt to have BI reports build on Universes can access the NLS data rather use native SQL. See the graphic provided with 7.40 SP08 and higher.

      NLS_query_access.JPG

      Best Regards Roland

      Author's profile photo Amit Tewatia
      Amit Tewatia

      Thanks Roland.

      Author's profile photo Owen Pettiford
      Owen Pettiford

      Roland - Is it still not possible to access SAP IQ data from a BO Universe ?

      Author's profile photo Thomas Quäbicker
      Thomas Quäbicker

      Hello Roland,

      is there a possibility to see if the database statistics for SDA virtual tables are created and up to date? I have in mind a functionality comparable to BW on NON-HANA database cube administration where I have a traffic light that displays green if up to date or red if no statistic exists.

      Kind regards,

      Thomas

       

      Author's profile photo Roland Kramer
      Roland Kramer
      Blog Post Author

      Hi
      You might want to check the following Information - https://blogs.sap.com/2016/10/12/sap-nls-solution-sap-bw/#nlsperformance => Monitoring Nearline Storage Tables and Partitions from BW

      Best Regards Roland

      Author's profile photo Thomas Quäbicker
      Thomas Quäbicker

      Hello Roland,

       

      if this is your answer to my question from the 5th of March I have to say this is not the answer I expected.

      We are using BW 7.40 on HANA in combination with NLS IQ and SDA. As described above I have to run report RSSDA_CREATE_TABLE_STAT after I transferred data to NLS to ensure a good query performance. Now I would like to know if statistics really exist or if someone transferred data to NLS but forgot to run the report. For me it is not sufficient to see in SM37 that the job run successfully. I would like to have a traffic light for each DAP with virtual table that displays that statistics are up to date and I can expect that there is no negative impact on query performance when accessing NLS.

      Kind regards,

      Thomas

       

      Author's profile photo Roland Kramer
      Roland Kramer
      Blog Post Author

      Hello Thomas
      Since SAP HANA 1.0 SP12 you can monitor the DB Statistics for remote Views (e.g. NLS via SAP IQ) via the System View SYS.DATA_STATISTICS.
      This View offers for Example CREATE_TIME und LAST_REFRESH_TIME as columns.

      See - https://help.sap.com/doc/eb75509ab0fd1014a2c6ba9b6d252832/1.0.12/en-US/SAP_HANA_Administration_Guide_en.pdf

       

      Best Regards Roland

      Author's profile photo Former Member
      Former Member

      Question: Would it be ok to deploy BW HANA on one cloud provider and its NLS with SAP IQ system on another cloud provider? I'm looking for more that just whether it's technically possible or supported, will it perform well? who has done this, case studies? pros and cons, issues, pitfalls, anything that can help assess this, whether positive or negative.

      Author's profile photo Roland Kramer
      Roland Kramer
      Blog Post Author

      Answer (might not complete): In general is is suitable to have the BW System and the SAP IQ Database close together for performance reason.

      However running this is different environments, e.g. on-premise and Cloud might be a scenario, but having two different Cloud environments is that really necessary?

      In general see the SAP First Guidance - SAP First Guidance – Implement SAP BW/4HANA in the Azure Cloud

      Best Regards Roland

      Author's profile photo Frank Schneider
      Frank Schneider

      Hello,

      we currently have problems with access via calculationviews. Is there a HowTo for the performance optimization/use of DTO/NLS with calculationviews? Specifically, we are accessing a Calculation View via an HCPR which, among other things, uses an ADSO whose cold data is stored in an IQ via DTO and the performance is real bad also for Data which is not stored in the IQ.

      Thanks
      Frank