topic Re: re: performance in Application Development and Automation Discussions

re: performance

Former Member — Wed, 21 Dec 2005 04:18:30 GMT

Hi,

Iam using this code. How can i improve this performance. In the first statement i retriving the created po's for each pr and in the select statement i am retriveing the posting date for each po. it_one contains the pr and pr items.

loop at it_one.

select ebeln into ebeln from ekpo

where banfn = it_one-banfn

and bnfpo = it_one-bnfpo

and loekz <> 'L'.

select single bedat from ekko

into podat

where ebeln = ebeln.

**for processing the bedat***

z = z + 1.
if podat <> 0 and badat <> 0.
po_ba = podat - badat.
endif.
po_ba1 = po_ba1 + po_ba.
clear: podat.

endselect.

endloop.

rgds

p.kp

Re: re: performance

Former Member — Wed, 21 Dec 2005 04:26:39 GMT

Hi paluri krishna prasad,

From your code i can understand that for each entry in the internal table you want ebeln field from the ekpo.

For that ebeln you want bedat from ekko. Right.

1. Instead of loop at it_one use For all entries. Remember before using for all entries you have to check whether itab is initial or not.

2. Use sy_subrc statement before select single which increase your performance in the abap side.

Hope this helps you.

Regards,

Maheswaran.B

Re: re: performance

Former Member — Wed, 21 Dec 2005 04:35:16 GMT

The problem is that EKPO is a very large table and you are not selecting from it based on an index. To fix it, we need to know how you are filling it_one.

Rob

Re: re: performance

former_member221770 — Wed, 21 Dec 2005 04:40:30 GMT

Paluri,

Try this:


data: begin of tbl_ekko occurs 0,
        banfn  like ekpo-banfn,
        bedat  like ekko-bedat,
      end of tbl_ekko.

check not it_one[] is initial.

select ekpo~banfn ekko~bedat
       into table tbl_ekko
       from ekko

       inner join ekpo
       on ekko~ebeln = ekpo~ebeln

       for all entries in it_one
       where ekpo~bnfpo = it_one-banfn and
             ekpo~loekz ne 'L'.

* Prepare Table for Binary Search
sort tbl_ekko by banfn.

loop at it_one.
  read table tbl_ekko with key banfn = it_one-banfn 
                           binary search.
  if sy-subrc eq 0.

** do processing logic here.

  endif.

endloop.

This method would be more efficient than your current method as it uses an ARRAY FETCH (ie it reads the database once and stores the relevant data into an internal table - this lowers the load onthe DB) and then uses a Binary Search to process the data (the main processing is done on your App Server which is scalable).

Would also recommend using a proper index for EKPO as BANFN alone is not an index and can lead to an inefficient query.

Hope this helps.

Cheers,

Pat.

Re: re: performance

Former Member — Wed, 21 Dec 2005 04:46:58 GMT

Hi,

I will try this and i will let u know.

Thanks for quick response

rgds

p.kp

Re: re: performance

Former Member — Wed, 21 Dec 2005 05:05:01 GMT

Hi,

Suppose i don't want to change my code. I want to create a secondary indexes in ekpo for fields banfn and bnfpo. Then performance will increase in my opinion. Is it recommendable or not.

rgs

p.kp

Re: re: performance

former_member221770 — Wed, 21 Dec 2005 05:12:25 GMT

Paluri,

SAP recommends that tables not have more than 5-6 secondary indexes. This is because each secondary index needs a separate db table and each time the system updates the table it also needs to update the tables behind the secondary indexes.

By putting creating the secondary index, the performance of your query SHOULD improve but this MAY come at a cost when you save your PO's.

Would recommend running transaction DB05 to see if your proposed index is worthwhile or not.

Hope this helps.

Cheers,

Pat.

Re: re: performance

Former Member — Wed, 21 Dec 2005 05:13:39 GMT

You should not create indexes based on your retrieval performance. Imagine the load the system takes everytime you create or change a PO, particularly if you have a steady rate of PO creation and changes. Every time a new PO is added or changed, system has to reindex and that adds more burden to the processing.

You should tackle it in your code only.

Srinivas