Please help me optimize/tune this code for performance

[Former Member]

Hi,

Friends can you please help me optimize code below.

This is a start routine for SAP BW.

There is one item master table /bic/mzitm_id. Field zitm_id is the primary key of this table and another field is zbib_id.

Now in the data_package (source records) in BW start routine for every value of zbib_id I have to determine how many items (zitm_id) exists in table /bic/mzitm_id. (Total no records in table /bic/mzitm_id for a particular value of zbib_id).

Table /bic/zitm_id has about 6million recs and data_package is expected to have on app 300,000 recs (at a time data_package will only 20,000 recs).

Here is the existing code:

 DATA: max_bib_id TYPE /bic/mzitm_id-/bic/zbib_id,
        min_bib_id TYPE /bic/mzitm_id-/bic/zbib_id,
        temp_bib_id TYPE /bic/mzitm_id-/bic/zbib_id,
        tot_lines TYPE i,
        tot_items TYPE i.

  DATA: BEGIN OF lt_zitm_id OCCURS 0,
          zitm_id  TYPE /bic/mzitm_id-/bic/zitm_id,
          zbib_id TYPE /bic/mzitm_id-/bic/zbib_id,
        END OF lt_zitm_id.


  DATA: ls_data_package LIKE LINE OF DATA_PACKAGE[].
  FIELD-SYMBOLS:<ls_data_package> LIKE LINE OF DATA_PACKAGE[].


* ----------------------------------------------------------------

  SORT DATA_PACKAGE BY /bic/zbib_id.

* Find Min BIB ID
  READ TABLE DATA_PACKAGE
        INTO ls_data_package
       INDEX 1.
  min_bib_id = ls_data_package-/bic/zbib_id.

* Find Max BIB ID
  DESCRIBE TABLE DATA_PACKAGE LINES tot_lines.
  READ TABLE DATA_PACKAGE
        INTO ls_data_package
       INDEX tot_lines.
  max_bib_id = ls_data_package-/bic/zbib_id.


* Select Items for BIB range between Min and Max
  SELECT /bic/zitm_id /bic/zbib_id
    FROM /bic/mzitm_id
    INTO TABLE lt_zitm_id
         WHERE /bic/zbib_id BETWEEN min_bib_id
                                AND max_bib_id.


  SORT lt_zitm_id BY zbib_id.
  CLEAR tot_items.
  CLEAR temp_bib_id.

  LOOP AT DATA_PACKAGE ASSIGNING <ls_data_package>.

    IF  <ls_data_package>-/bic/zbib_id = temp_bib_id.
      <ls_data_package>-/bic/ztot_itm = tot_items.

    ELSE.
      CLEAR tot_items.
      LOOP AT lt_zitm_id WHERE zbib_id = <ls_data_package>-/bic/zbib_id.
        tot_items = tot_items + 1.
      ENDLOOP.
      <ls_data_package>-/bic/ztot_itm = tot_items.
      temp_bib_id = <ls_data_package>-/bic/zbib_id.
    ENDIF.

  ENDLOOP.

[Former Member]

If either data_package or zitm_id contain large amounts of data, you should replace the inner nested loop of zitm_id with a binary read followed by a loop using an index and exit.

Rob

[Former Member]

Rob, thanks for the reply,

Can you please help me with an example..

I am not an ABAP'er, I've been able to write this code with the help from SDN..

[Former Member]

Please see:

[The Performance of Nested Loops|]

Rob

[Former Member]

One more option,

I was thinking to compress the zitm table and then read it in a data_package loop, Is this is a good idea.

Here is the what I am thinking of:

  DATA: BEGIN OF lt_zitm_id_2 OCCURS 0,
               zbib_id TYPE /bic/mzitm_id-/bic/zbib_id,
		zcount type i,
        END OF lt_zitm_id_2.


loop at lt_zitm_id
clear lt_zitm_id_2.
lt_zitm_id_2-zbib_id = lt_zitm_id-zbib_id.
lt_zitm_id_2-zcount = 1.
collect lt_zitm_id_2.

endloop.

sort lt_zitm_id_2 by zbib_id.

Now loop at data_package and read lt_zitm_id with key zbib_id....

[Former Member]

Well, you have to make sure that the key being used for the COLLECT is the same as the one used in the READ. But it looks like it should work.

The example in the blog has fewer steps and should be quicker.

Don't forget the BINARY SEARCH option on the READ.

Rob

[Former Member]

Rob, here is the modified code. Can you please give some recommendations on it

  DATA: max_bib_id TYPE /bic/mzitm_id-/bic/zbib_id,
        min_bib_id TYPE /bic/mzitm_id-/bic/zbib_id,
        temp_bib_id TYPE /bic/mzitm_id-/bic/zbib_id,
        tot_lines TYPE i,
        tot_items TYPE i.

  DATA: BEGIN OF lt_zitm_id OCCURS 0,
          zitm_id  TYPE /bic/mzitm_id-/bic/zitm_id,
          zbib_id TYPE /bic/mzitm_id-/bic/zbib_id,
        END OF lt_zitm_id.

  DATA: BEGIN OF lt_zitm_id_2 OCCURS 0,
          zbib_id TYPE /bic/mzitm_id-/bic/zbib_id,
          zcount TYPE i,
        END OF lt_zitm_id_2.

  DATA: ls_zitm_id_2 LIKE LINE OF lt_zitm_id_2[].

  DATA: ls_data_package LIKE LINE OF data_package[].
  FIELD-SYMBOLS:<ls_data_package> LIKE LINE OF data_package[].


* ----------------------------------------------------------------

  SORT data_package BY /bic/zbib_id.

* Find Min BIB ID
  READ TABLE data_package
        INTO ls_data_package
       INDEX 1.
  min_bib_id = ls_data_package-/bic/zbib_id.

* Find Max BIB ID
  DESCRIBE TABLE data_package LINES tot_lines.
  READ TABLE data_package
        INTO ls_data_package
       INDEX tot_lines.
  max_bib_id = ls_data_package-/bic/zbib_id.


* Select Items for BIB range between Min and Max
  SELECT /bic/zitm_id /bic/zbib_id
    FROM /bic/mzitm_id
    INTO TABLE lt_zitm_id
         WHERE /bic/zbib_id BETWEEN min_bib_id
                                AND max_bib_id.


  SORT lt_zitm_id BY zbib_id.

  LOOP AT lt_zitm_id.
    CLEAR lt_zitm_id_2.
    lt_zitm_id_2-zbib_id = lt_zitm_id-zbib_id.
    lt_zitm_id_2-zcount = 1.
    COLLECT lt_zitm_id_2.
  ENDLOOP.


  CLEAR tot_items.
  CLEAR temp_bib_id.
  SORT lt_zitm_id_2 BY zbib_id.

  LOOP AT data_package ASSIGNING <ls_data_package>.

    IF  <ls_data_package>-/bic/zbib_id = temp_bib_id.
      <ls_data_package>-/bic/ztot_itm = tot_items.

    ELSE.
      CLEAR tot_items.

      READ TABLE lt_zitm_id_2
            INTO ls_zitm_id_2
          WITH KEY zbib_id = <ls_data_package>-/bic/zbib_id
            BINARY SEARCH.

      IF sy-subrc EQ 0.
        <ls_data_package>-/bic/ztot_itm = ls_zitm_id_2-zcount.
        temp_bib_id = <ls_data_package>-/bic/zbib_id.
      ENDIF.
    ENDIF.

  ENDLOOP.

[Former Member]

It looks OK to me - why not just give it a try?

Rob

[Former Member]

Rob I've been able to get a reduction in run time from app 2hours down to 15 mins.

Is there anything else you can recommend for further optimization in this code.

[Former Member]

>

> Rob I've been able to get a reduction in run time from app 2hours down to 15 mins.
> 
> Is there anything else you can recommend for further optimization in this code.

Well, OK - at least that's something. The only other thing I can think of is to do it as I suggested above. That will get rid of the COLLECT step.

This is what I mean - but check please:

DATA: tab_index TYPE sy-tabix.

LOOP AT data_package ASSIGNING <ls_data_package>.

  IF  <ls_data_package>-/bic/zbib_id = temp_bib_id.
    <ls_data_package>-/bic/ztot_itm = tot_items.

  ELSE.
    CLEAR tot_items.

    READ TABLE lt_zitm_id WITH KEY
      zbib_id = <ls_data_package>-/bic/zbib_id
      BINARY SEARCH.

    IF sy-subrc = 0.
      tab_index = sy-tabix.

      LOOP AT lt_zitm_id FROM tab_index.
        IF lt_zitm_id-zbib_id <> <ls_data_package>-/bic/zbib_id.
          EXIT.
        ENDIF.

        tot_items = tot_items + 1.
        <ls_data_package>-/bic/ztot_itm = tot_items.
        temp_bib_id = <ls_data_package>-/bic/zbib_id.

      ENDLOOP.
    ENDIF.

  ENDIF.

ENDLOOP.

Another thing - make sure that the results are the same as before.

Rob

Edited by: Rob Burbank on Oct 23, 2008 11:37 AM