Performance Issue with the Query urgent

[Former Member]

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 a_{mblnr = b}mblnr

AND a_{mjahr = b}mjahr

WHERE a~lifnr = p_lifnr

AND a~bwart IN ('122')

AND b~budat IN s_budat

GROUP BY lifnr.

ENDSELECT.

abhishek suppal

[RichHeilman]

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

[Former Member]

hi

is there no other table except MSEG thru which i can get the data based on lifnr and movement 122.

[Former Member]

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.

[Former Member]

Hi Abhi,

Try like this ....

SELECT SUM( a~dmbtr )INTO value1

FROM mseg AS a INNER JOIN mkpf AS b

ON a_{mblnr = b}mblnr

AND a_{mjahr = b}mjahr

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

[Former Member]

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.

[Former Member]

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