Select query takes long time....

[Former Member]

Hi Experts,

I am using a select query in which inspection lot is in another table and order no. is in another table. this select query taking very long time, what is the problem in this query ? Pl. guide us.

select b_{PRUEFLOS b}MBLNR b_{CPUDT a}AUFNR a_{matnr a}LGORT a~bwart

a_{menge a}ummat a_{sgtxt a}xauto

into corresponding fields of table itab

*into table itab

from mseg as a inner join qamb as b

on a_{mblnr = b}mblnr

and a_{zeile = b}zeile

where b~PRUEFLOS in insp

and b~cpudt in date1

and b~typ = '3'

and a~bwart = '321'

and a~aufnr in aufnr1.

Yusuf

[Former Member]

Hi Yusuf,

Also put MJAHR in selection criteria, as the same document can exist in many years. USE the following

select b_{PRUEFLOS b}MBLNR b_{CPUDT a}AUFNR a_{matnr a}LGORT a~bwart

a_{menge a}ummat a_{sgtxt a}xauto

into corresponding fields of table itab

*into table itab

from mseg as a inner join qamb as b

on a_{mblnr = b}mblnr

on a_{mjahr = b}mjahr

and a_{zeile = b}zeile

where b~PRUEFLOS in insp

and b~cpudt in date1

and b~typ = '3'

and a~bwart = '321'

and a~aufnr in aufnr1.

If it takes a lot of time then make a view comprising of MSEG and QAMB containing all the fields.

Regards,

Mallick

[Former Member]

Hi Mallick,

What is View comprising ? How to make it ?

Pl.

Yusuf.

[Former Member]

Hi,

What is the structure of ITAB. If it is in same order as fields selected in the select query use INTO TABLE ITAB instead of INTO CORRESPONDING FIELDS OF TABLE ITAB.

If it is not of the same order then define another internal table with same order of fields and use in select query.

Into corresponding fields degrades performance of the program.

Never use INTO CORRESPONDING FIELDS...

REgards,

Sankar

[Former Member]

Hi Sankar,

I Have removed Corresponding but eventhough it takes long time ?

Yusuf

[Former Member]

hi

instead of using 'move to corresponding of itab' fields use 'into table itab'.....

coz......if u use move to corresponding it will search for all the appropriate fields then it will place u r data........instead of that declare apprpiate internal table and use 'into table itab'.

and one more thing dont use joins ......coz joins will decrease u r performance .....so instead of that use 'for all entries' ....and mention all the key fields in where condition ........

ok

reward points for helpful answers

[Former Member]

Solved.

[Former Member]

reward points if helpful

[Former Member]

Hi,

The main problem in your code is ur using inner join syntax and fields which is using in on "a_{mblnr = b}mblnr and a_{zeile = b}zeile" are not Key fields in other table is QUMB, its leads highly perforamnce issue.

you can use the FOR ENTRIES concept inseady of joins.

and you have to give the key fields in where condition for a table MSEG, this tabel contain huge data. so while getting date use always key fields in where condition and maintain sequence..

Reward with points if it useful.

Regards,

Vijay