topic Select query takes long time.... in Application Development and Automation Discussions

Select query takes long time....

Former Member — Fri, 24 Aug 2007 08:56:14 GMT

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

Re: Select query takes long time....

Former Member — Fri, 24 Aug 2007 09:13:26 GMT

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

Re: Select query takes long time....

Former Member — Fri, 24 Aug 2007 09:17:30 GMT

Hi Mallick,

What is View comprising ? How to make it ?

Pl.

Yusuf.

Re: Select query takes long time....

Former Member — Fri, 24 Aug 2007 09:18:38 GMT

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

Re: Select query takes long time....

Former Member — Fri, 24 Aug 2007 09:37:28 GMT

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 ........

reward points for helpful answers

Re: Select query takes long time....

Former Member — Fri, 24 Aug 2007 09:37:28 GMT

Hi Sankar,

I Have removed Corresponding but eventhough it takes long time ?

Yusuf

Re: Select query takes long time....

Former Member — Fri, 24 Aug 2007 09:43:34 GMT

Solved.

Re: Select query takes long time....

Former Member — Mon, 27 Aug 2007 06:29:17 GMT

reward points if helpful

Re: Select query takes long time....

Former Member — Mon, 27 Aug 2007 09:44:30 GMT

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