topic Re: Normalise the select statement in Application Development and Automation Discussions

Normalise the select statement

rohit_kaikala — Tue, 21 Sep 2010 04:58:59 GMT

Hi folks,

I have a select statement which is taking more than 10min to execute.

SELECT a_{ebeln a}bedat

a_{ihrez a}reswk

a~unsez

b_{ebelp b}matnr

b_{werks b}lgort

b_{matkl b}menge

FROM ekko AS a INNER JOIN ekpo AS b

ON a_{ebeln EQ b}ebeln

INTO CORRESPONDING FIELDS OF TABLE sto_tab

WHERE a~ebeln IN s_ebeln

AND a~bstyp EQ 'F'

AND a~bsart IN ('ZUB1' ,'ZUB2')

AND a~loekz EQ ' '

AND a~ekorg EQ '6000'

AND a~reswk EQ p_reswk

AND b~loekz EQ ' '

AND b~matnr IN s_matnr

AND b~werks IN s_werks

AND b~lgort EQ p_lgort

AND b~elikz NE 'X'

AND b~bstyp EQ 'F'.

Please give me some suggestions to get more faster.

Regards,

Rohith.

Re: Normalise the select statement

Former Member — Tue, 21 Sep 2010 05:12:15 GMT

Hi rohit kaikala,

Not sure whether it will increase the performance but try once :

Add LOEKZ,EKORG,BSTYP in your internal table and using DELETE command delete them. Just try if it can increase the performance.


SELECT a~ebeln a~bedat
a~ihrez a~reswk
a~unsez
b~ebelp b~matnr
b~werks b~lgort
b~matkl b~menge
FROM ekko AS a INNER JOIN ekpo AS b
ON a~ebeln EQ b~ebeln
INTO CORRESPONDING FIELDS OF TABLE sto_tab
WHERE a~ebeln IN s_ebeln
AND a~bsart IN ('ZUB1' ,'ZUB2')
AND a~reswk EQ p_reswk
AND b~matnr IN s_matnr
AND b~werks IN s_werks
AND b~lgort EQ p_lgort
AND b~elikz NE 'X'.
"Here you DELETE itab as required like 
DELETE STO_TAB WHERE LOEKZ IS NOT INITIAL.
DELETE STO_TAB WHERE BSTYP NE 'F'.
DELETE STO_TAB WHERE EKORG NE '6000'.

Thanks & Regards,

Rock.

Re: Normalise the select statement

Former Member — Tue, 21 Sep 2010 07:47:49 GMT

Hi,

Remove the into corresponding fields statement and also use fields in same sequence as in table.

try to use the primary index in where condition.

Regards

Shiva

Re: Normalise the select statement

Former Member — Tue, 21 Sep 2010 13:00:49 GMT

Moderator message - Please see before posting - post locked Rob