Long run time for ABAP Program

[Former Member]

Hi All,

When I run a program I see that 2 particular select statements are taking a very long time to execute thus causing performance issues.

1st select statement:

1) SELECT banfn bnfpo bsart matnr werks

menge flief badat lfdat

INTO TABLE t_pur_reqs

FROM eban FOR ALL ENTRIES IN t_marc

WHERE matnr = t_marc-matnr

AND werks = t_marc-werks

AND bsart IN s_prbsrt "From the input variant

AND knttp IN s_prkntt

AND banfn IN s_banfn "From the input variant

AND bstyp = 'B' "Purchase Doc category PR

AND loekz = '' "Purchase Requisition Doc not deleted

AND ebakz <> 'X' "Purchase Requisition must not be closed

AND statu <> 'B'. "PR not transformed to PO

There are about 474358 entries for this selection.

Similarly, the second statement :

2) SELECT a_{ebeln b}ebelp c~etenr

a_{bsart a}bedat a~lifnr

b_{matnr b}werks

c_{eindt c}menge a~ekorg

c~wemng

c~slfdt

INTO CORRESPONDING FIELDS OF TABLE t_poschedline

FROM ekko AS a INNER JOIN

ekpo AS b

ON a_{ebeln = b}ebeln

INNER JOIN eket AS c

ON b_{ebeln = c}ebeln

AND b_{ebelp = c}ebelp

FOR ALL ENTRIES IN t_marc

WHERE b~matnr = t_marc-matnr

AND b~werks = t_marc-werks

AND a~bsart IN s_pobsrt

AND b~knttp IN s_pokntt

AND a~ebeln IN s_ebeln

AND a~loekz = '' "PO header deletion indicator

AND b~loekz = '' "PO item Deletion indicator

AND b~elikz = '' "Delivery Not complete

AND c~menge > 0.

There are about 7 lakh entries for this selection.

The program takes about 7000 secs to execute. Is there any work around for this piece of code which can decrease its execution time?

Please suggest.

Thanks,

Rachana.

[Former Member]

Hi,

Just try like this...

1) First fetch the data from EBAN (as you are already doing)

2) For all entries fetech above, Get the data from EKKO,

3) For all entries fetched(from EKKO) above , fetch the data from EKPO.

Since inner join and for all entries are used, it may take time...as for all entries will in turn do the inner join...

Once you are done, compare the both cases in SE30 or ST05 and check the time/performance

Hope this helps..

Regards

Shiva

[Former Member]

Hi,

Also to add to above, to increase the performance of select query, use the fields in same order as in the table in the wherre conditon..

For eg: BSART should come first in where conditon on EBAN then MATNR or WERKS..

Regards

Shiva

[Former Member]

SELECTing a large amount of data will take time. That's one of the first rules in performance tuning. So sometimes there's a bullet that you just have to bite.

The only thing that I can suggest is that you make sure that t_marc is not empty and contains no duplicates comparing matnr and werks.

Rob

[Former Member]

Hi Rachana,

You need to use joins here.

why dont u join EBAN and MARC?

For all entries will definitely decrease performance in this case. Get the PR details in one internal table using one query. This will be much better than using for all entries.

ditto for PO/RFQ tables.

Regards,

Abdullah

[Former Member]

Try to use indexes for the table where you are making "SELECT" statement.

[Former Member]

Join EBAN and MARC.....this shud improve the performance.

[Former Member]

Hi, Before using for all entries addition make sure its not initial.You can just put an initial check as

If not t_marc is initial.

Your select statament

Endif.

And try avoiding into corresponding fields of table

Regards

Akmal