Skip to Content
author's profile photo Former Member
Former Member

Query Performance

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.

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Dec 09, 2005 at 03:07 PM

    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,

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 11, 2005 at 04:50 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 12, 2005 at 05:10 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.