topic Re: Performance issue in Application Development and Automation Discussions

Performance issue

shilpik20 — Wed, 28 Mar 2007 14:52:27 GMT

I have two DB tables 'ekko' and 'zcontractwflow' with a field EBELN (PO#) in them. If any new PO# is created in the ekko table it shud automatically be added to the zcontractwflow. I used the following logic:-

sort tb_zcontractwflow by ebeln seqnr.

loop at tb_ekko.

read table tb_zcontractwflow with key

ebeln = tb_ekko-ebeln binary search.

if sy-subrc <> 0.

tb_zcontractwflow-ebeln = tb_ekko-ebeln.

insert zcontractwflow from tb_zcontractwflow.

endif.

endloop.

But it has performance issues due the 'loop at tb_ekko' as the ekko has over 180,000 records. What do u suggest other than LOOP.

Re: Performance issue

Former Member — Wed, 28 Mar 2007 14:59:08 GMT

Hi,

You first select all the records from EKKO and from zcontractwflow.

delete all the records from ekko if the same records exists in zcontractwflow.

Now ul have less number of records in EKKO.

then insert all the EKKO records.

Regards,

Ashok...

Re: Performance issue

Former Member — Wed, 28 Mar 2007 15:02:57 GMT

hi Shilpik,

You can think about the following logic:

Since purchase order numbers are generated using a specific number range,

the succeeding numbers will be greater than the preceeding one. Hence, if

we can sort the internal tables in descending order, and then loop the tb_ekko

table, the loop passes will be very few. Once the record is found in tb_zcontractflow, we can exit the

loop assuming that remaining records were existing.

sort tb_zcontractwflow by ebeln seqnr descending.
sort tb_ekko by ebeln seqnr descending.

loop at tb_ekko. 
       read table tb_zcontractwflow with key
                           ebeln = tb_ekko-ebeln binary search.
       if sy-subrc <> 0.
              tb_zcontractwflow-ebeln = tb_ekko-ebeln.
              insert zcontractwflow from tb_zcontractwflow.
        else.
              exit.
        endif.
endloop.

Hope this helps,

Sajan Joseph.

Re: Performance issue

Former Member — Wed, 28 Mar 2007 15:03:39 GMT

Are you coding this inside an exit? If yes, I do not see the need for a loop in that case..

If not, and if the z-table is not as huge as ekko, you could try this:

LOOP AT tb_zcontractwflow.

READ TABLE tb_ekko WITH KEY ebeln = tb_zcontractwflow-ebeln.

IF sy-subrc = 0.

DELETE tb_ekko index sy-tabix.

ENDIF.

ENDLOOP.

MODIFY tb_zcontractwflow FROM TABLE tb_ekko.

Hope this helps.

Sudha