How to improve the performance for SELECT statement?

[Former Member]

Hi,

I got an issue in my program while fetching data from MSEG & MKPF using Inner Join it is taking more time .

i wrote the select statement as below.

SELECT ms_{mblnr ms}mjahr ms_{zeile ms}bwart ms_{matnr ms}werks ms_{lgort ms}shkzg ms_{menge mk}budat

INTO TABLE it_msegmkpf

FROM mseg AS ms INNER JOIN mkpf AS mk

ON ms_{mblnr = mk}mblnr

AND ms_{mjahr = mk}mjahr

FOR ALL ENTRIES IN it_mara

WHERE ms~matnr = it_mara-matnr

AND ms~werks IN site

AND ms~lgort = sloc

AND ms~bwart IN rt_bwart

AND mk~budat BETWEEN date-low AND sy-datum.

can anyone suggest me how to improve performance of this the above select query

[Former Member]

Hi,

I got an issue in my program while fetching data from MSEG & MKPF using Inner Join it is taking more time .

i wrote the select statement as below.

SELECT ms_{mblnr ms}mjahr ms_{zeile ms}bwart ms_{matnr ms}werks ms_{lgort ms}shkzg ms_{menge mk}budat

INTO TABLE it_msegmkpf

FROM mseg AS ms INNER JOIN mkpf AS mk

ON ms_{mblnr = mk}mblnr

AND ms_{mjahr = mk}mjahr

FOR ALL ENTRIES IN it_mara

WHERE ms~matnr = it_mara-matnr

AND ms~werks IN site

AND ms~lgort = sloc

AND ms~bwart IN rt_bwart

AND mk~budat BETWEEN date-low AND sy-datum.

can anyone suggest me how to improve performance of this the above select query

[Former Member]

Hi Ramana,

It looks fine as You r using index on MSEG .

But one thing you should ensure that before select query , Entries in IT_MARA should be Ensured .

thanks

Sreenivas Reddy

[Former Member]

Hi,

Data fetching from BSEG and MSEG table reduces the performance of a code.

Try to Remove the join ; this will definately improve the performance. Also see that select query is not with in the loop and you have not used select end select.

Thanks,

[Former Member]

Hi,,

Dont go for the join for MSEG and MKPF as they are very huge table like BSEG...Instead select the values from MKPF and use one more FOR ALL ENTRIES retrieve the data from MSEG...Dont use select and end select also....

[Former Member]

o.k. before you start optimizing without looking at the execution I would recommend you to

use the SQL Trace:

Which order of processing do the optimizer use

WHERE ms~matnr = it_mara-matnr
AND ms~werks IN site
AND ms~lgort = sloc
AND ms~bwart IN rt_bwart
AND mk~budat BETWEEN date-low AND sy-datum.

on mseg there is the index M

MANDT

MATNR

WERKS

LGORT

BWART

on mkpf there is the index BUD with the fields:

MANDT

BUDAT

MBLNR

Which one is used and which one is better? Nobody can tell without knowing, what values

you specify.

Just try, go to the debugger stop at the SELECT, note down values and try in SE16, the

selection on MSEG and MKPF. How many records do you find, in each selection.

The smaller one should be the entry for your selection. Compare with the explain of the join.

The join can be much better here than any FAE, the join can change the order of processing.

Siegfried

[Former Member]

Hi Ramana,

Declare one int table it_mkpf with mblnr mjahr budat fields.

SELECT mblnr mjahr zeile bwart matnr werks lgort shkzg menge 
INTO CORRESPONDING FIELDS OF TABLE it_msegmkpf
FROM mseg 
FOR ALL ENTRIES IN it_mara
WHERE matnr = it_mara-matnr
AND werks IN site
AND lgort = sloc
AND bwart IN rt_bwart.


IF NOT it_msegmkpf NOT INITIAL.
 SELECT mblnr mjahr budat 
 INTO TABLE it_mkpf
 FROM mkpf 
 FOR ALL ENTRIES IN it_msegmkpf
 WHERE mblnr = it_msegmkpf mblnr
       and  mjahr = it_msegmkpf-mjahr
       AND budat BETWEEN date-low AND sy-datum..
ENDIF.


LOOP AT it_msegmkpf.
READ TABLE it_mkpf into wa WITH KEY mblnr = it_msegmkpf mblnr
                                                             mjahr = it_msegmkpf-mjahr.
IF sy-subrc EQ 0.
 MODIFY TABLE it_msegmkpf from wa TRANSPORTING budat.
ENDIF.

Reg,

Sachin

[Former Member]

Hi

Plz avoid using a join on MSEG table as it is quite a bulky table.

This will surely help the performance.

Regards

Harsh

[Former Member]

Check date-low IS NOT INITIAL

Cheers

[Former Member]

Instead of using inner join on mseg and mkpf you can use the view for mkpf and mseg

WB2_V_MKPF_MSEG and select data from it.

[Former Member]

hi,

the first and foremost thing is that you are using innerjoin. please use for all entries instead of that.

always do a sy-subrc check after a select statement.

if sy-subrc = 0 then sort i_tab by key field.

using innerjoin is a very bad programming practice.

thanks,

trijeeta

[ThomasZloch]

> using innerjoin is a very bad programming practice.

Please do some research before publishing such wrong and misleading statements, e.g.

Thomas

[Former Member]

>

> using innerjoin is a very bad programming practice.

trijeeta944 - please see the sticky at the top of this forum.

rob

[Former Member]

> using innerjoin is a very bad programming practice.

No, the oppposite is true, knowing the advantages and disadvantages of JOINs is good

performance practice.

Repeating the same things again and again, without thinking about it, is generally bad

forum practice.

Siegfried

[dhananjayansr]

Hi Ramana,

To optimize a SQL statement,

1) use primary key fields in the where clause

2) create database index based on the fields in the where clause

3) even if you don't have a value just use <> '9999' or <> 'XXX"

Dj

[Former Member]

hi,

if two tables have huge amount of data the try to avoid the INNER JOIN. because it compare all records of one table to another table.

my sugetion is using mblnr get the data from MKPF. it will have less record as compare to MSEG because mblnr is a primare key. if SY-SUBRC = 0. then get data from MSEG on condition and using FOR ALL ENTRIES in MKPF. FOR all entries is useful when table(i.e MKPF) having less number of record. other wise try to use the concept of parallel cursor.

[Former Member]

> if two tables have huge amount of data the try to avoid the INNER JOIN. because it compare all

> records of one table to another table.

very often repeated misunderstanding, still wrong!

[Former Member]

Thank u