Query Performance

[Former Member]

Hi Friends,

I am facing a serius problem with users regarding a query performance.

It is taking a lot of time for retriving the data of infoobject which was set to be Filter from the query.

Can some one giude me what I can do to improve the performance of that filter infoobject.

[Former Member]

The BW Performance Tuning Knowledge Center that was mentioned is good. Try searching the SDN BI forums on "Query Performance" and you should get many hits that should help provide ideas/techniques.

Compression and Aggregates are probably the first two thing to make sure you are using.

[edwin_harpino]

hi friend,

take a look sap oss note 748623 - 'Input help (F4) has a very long runtime - recommendations'

Input help (F4) has a very long runtime - recommendations Help

SAP Note Number Note Language DEEN Display Versions 123

Print SSCR Download

Text Header Data Release Correction Instructions Support Packages >>

Symptom

Calling the input help on a characteristic in the Business Information Warehouse takes a very long time. If you carry out the corresponding operation in the Web, a timeout can occur.

If performance is generally poor in the query or Web application, see notes 567746 and 764394 and the related notes.

Other terms

F4, booked_values, InfoObject, filter, variable

Reason and Prerequisites

This behavior can have different causes. Some of these are program errors, for which corresponding notes are available. In order not to have to import all notes individually, check whether you can improve the situation by implementing the latest Support Package.

More frequently, however, this will not be the case because the cause of the long runtime occurs in the Query design.

Solution

This note describes the options you can use to reduce a long runtime in the input help.

Refer to notes 626887 (design time) and 661251 (runtime) for information on the basic behavior of the input help and for details of restrictions. Even if you already have the corrections from the notes in your system, the explanations contained there are very significant.

In addition to the explanations and the settings specified in these notes, you can significantly improve the performance by designing a well-planned query.

A long runtime for the input help is frequently caused by compounding the characteristics involved. In this case, the "smaller"/more restrictive of the two characteristics should be preselected, either in the query definition or at runtime with a filter.

Even without compounding, this method (another characteristic is used for the preselection) results in significant performance improvements because fewer values have to be read and processed from the tables involved. The simplest way to do this is to generate one or more navigation attributes for the characteristic to be displayed, whereby the former is restricted first.

However, if the value set of the navigation attribute is very large, this may result in additional performance problems if you request an input help for it. Note 581079 contains a solution for this.

A more common cause for long runtimes is the use of MultiCubes with lots of individual InfoCubes involved. The likeliest improvement results in a reduction in the number of InfoCubes involved. If this is not possible because of the scenario, the MultiCubes can be divided into several smaller ones (right down to the individual InfoCube), whereby the individual value sets are then sent to the target data provider using the BW command URL. The target data provider is the query that shows the long runtime or the timeout. This query must then only go on one or two InfoCubes instead of on the large MultiCube. Refer to the documentation for more information.

By varying the read mode, you can force a somewhat different algorithm for reading the values. The M read mode is the fastest (for details, see note 661251) before D and then Q. Naturally, more values are issued for M than for D or even Q. You can implement this setting (see note 626887) on the InfoObject or in the template definition in the Web. You can choose this setting (see note 626887) for the InfoObject or in the Web in the template definition.

In the BEx Web Analyzer, a dialog box for preselecting the values exists which you can and should use. There is currently no equivalent when you execute the input help in the Web.

The corresponding timeout parameters are still relevant for executing the input help in the Web. These are mentioned here for the sake of completeness because although their configuration can guarantee a successful execution without a timeout, it cannot reduce the execution time.

icm/conn_timeout

icm/keep_alive_timeout

icm/server_port_...

icm/wp_roll_timeout

rdisp/plugin_auto_logout

The following can also be relevant when you use a message server:

ms/conn_timeout

ms/http_timeout

[Former Member]

Query Performance is relevant to so many factors:

1.) Number of records in a CUBE

2.) CPU Speed

3.) Memory

4.) Current Server Load

5.) Design of your BW Info Providers

6.) Indexes

7.) Aggregates

Tweak any of these and you will feel a change in query performance. (But the change could be either good or bad) You can go on experiment which of these factors would benefit you...

Check your BW Statistics cube or the table RSDDSTAT and

check following parameter values:

Check queries with highest runtimes and check where most time has been consumed

OLAP init

DB

OLAP

Frontend

Check for ratio of selected to transferred

Depending on your situation you will be able to make use of Portal Cache, Pre-calculation, OLAP CACHE or Aggreagtes.

The SDN BW Performance Tuning Knowledge center provides lots of information this topic:

https://www.sdn.sap.com/sdn/developerareas/bi.sdn?page=bi_performance.htm

Regards,