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

Performance Issue with the Query urgent

is there any way to get the data for the material Rejected with Movement type 122 & 123 except MSEG table, as my report is very slow...

my query is as below : -

SELECT SUM( a~dmbtr )INTO value1

FROM mseg AS a INNER JOIN mkpf AS b

ON amblnr = bmblnr

AND amjahr = bmjahr

WHERE a~lifnr = p_lifnr

AND a~bwart IN ('122')

AND b~budat IN s_budat

GROUP BY lifnr.

ENDSELECT.

abhishek suppal

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

5 Answers

  • Posted on Dec 09, 2005 at 01:44 PM

    MSEG is very big and will take time to query. Of course it doesn't help that it is a SELECT...ENDSELECT

    REgards,

    Rich Heilman

    Add a comment
    10|10000 characters needed characters exceeded

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

    Try to avoid joins, Select EndSelect and Sum functionality for transaction table MSEG. Select the data and then do a Sum, that will be faster.

    Add a comment
    10|10000 characters needed characters exceeded

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

    Hi Abhi,

    Try like this ....

    SELECT SUM( a~dmbtr )INTO value1

    FROM mseg AS a INNER JOIN mkpf AS b

    ON amblnr = bmblnr

    AND amjahr = bmjahr

    WHERE a~lifnr = p_lifnr

    AND a~bwart IN ('122'<b>,'123'</b>)

    AND b~budat IN s_budat

    GROUP BY lifnr.

    ENDSELECT.

    or ...

    define ranges...like

    ranges: r_bwart for XXXX-bwart.

    r_bwart-sign = 'I'.

    r_bwart-option = 'EQ'.

    r_bwart-low = 122.

    append r_bwart.

    r_bwart-low = 123.

    append r_bwart.

    now...

    in select statement u just add

    AND a~bwart IN r_bwart

    Thanks

    Eswar

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Dec 09, 2005 at 02:05 PM

    Abhishek,

    GROUP BY, SUM etc are aggregrate functions and they take a toll on the database performance. Also, when you add fields in your WHERE clause, the sql parser/cost optimizer, will try to determine the best index to use. In your case, it may just decide to do a sequential search which is always slow on a huge table. So in order to force the system to use a particular index, you should use only those fields in your WHERE clause and remove the unwanted records with a IF condition for the rest of the fields.

    Move the records into an internal table and do your SUM there, instead of SELECT ENDSELECT.

    Add a comment
    10|10000 characters needed characters exceeded

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

    Hi

    use two internal tables and use for all entries statement. This will decrease the load on DB.

    or move required fields of DB1 into itab1 and respectively into itab2. And use ur own logic to get required output

    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.