Help to optimize end routine since DTP taking long time to execute end routine....

[Former Member]

Hello Experts,

I am new to the SAP BW . I want suggestion how can i able to optimize end routine.

I have read lot of SDNs and come closer to solution that i should use Binary search while reading table in the end routine.

But while am trying to read table using binary search my end routine code is not getting executed at all even i sorted internal table before read statement.

Since 1st 2 internal table are already sort in descending order in the start routine i have not used binary search for those table for rest i have used.

plz try to look into my start and end routine code:

Here is the start routine:

IF source_package[] IS NOT INITIAL.
**select data for mrp from adso o_mrp based on mat,validity and*condition type
  SELECT val_start val_end /bic/zregi comp_code plant material knart
        knumh kprice loc_currcy rf_del_ind
        INTO TABLE it_omrp FROM /bic/ao_mrp2 FOR ALL ENTRIES IN
        source_package
        WHERE material = source_package-matnr AND
        knart = 'QPRT' AND
        val_start LE source_package-bedat AND
        val_end GE source_package-bedat.

  IF sy-subrc EQ 0.
    SORT it_omrp BY val_start DESCENDING.
  ENDIF.

**select data for mrp without vat from adso o_mrpvat based validity and
*condition type
  SELECT val_start val_end /bic/zregi mat_kondm /bic/zr_status
        /bic/k_mrpvat knart knumh loc_currcy
        INTO TABLE it_mrpvat FROM /bic/ao_mrpvat2 FOR ALL ENTRIES IN
        source_package WHERE
        knart = 'PPR1' AND
        val_start LE source_package-bedat AND
        val_end GE source_package-bedat .

  IF sy-subrc EQ 0.
    SORT it_mrpvat BY val_start DESCENDING.
  ENDIF.

*select region sales org from 0plant
  SELECT plant region salesorg INTO TABLE it_plant FROM
       /bi0/pplant
       FOR ALL ENTRIES IN source_package
       WHERE plant = source_package-werks AND
       objvers = 'A'.

  SORT it_plant BY plant ASCENDING.

*pass sales org from plant in range table
  CLEAR:wa_plant.
*
*select sales org data from zplantsal
  SELECT /bic/zplantsal salesorg distr_chan INTO TABLE it_salesorg1
       FROM /bic/pzplantsal
       FOR ALL ENTRIES IN source_package
       WHERE /bic/zplantsal = source_package-werks AND
       distr_chan = 'F1' AND
       objvers = 'A'.

  LOOP AT it_salesorg1 INTO wa_salesorg1.
    wa_salesorg-sign = 'I'.
    wa_salesorg-option = 'EQ'.
    wa_salesorg-low = wa_salesorg1-salesorg.
    APPEND wa_salesorg TO r_salesorg.
  ENDLOOP.

  SORT r_salesorg ASCENDING.
  DELETE ADJACENT DUPLICATES FROM r_salesorg COMPARING ALL FIELDS.

*select mat sales data based on material salesorg distr chnl
  SELECT salesorg distr_chan mat_sales mat_kondm INTO TABLE
       it_sales FROM /bi0/pmat_sales
       FOR ALL ENTRIES IN source_package
       WHERE mat_sales = source_package-matnr AND
       salesorg IN r_salesorg AND
       distr_chan = 'F1' .

*select reference doc no from mkpf adso based on mat doc no and year
  SELECT /bic/a_matyr mat_doc /bic/zref_doc INTO TABLE it_mkpf FROM
            /bic/ao_mkpf2
       FOR ALL ENTRIES IN source_package
        WHERE /bic/a_matyr = source_package-gjahr AND
       mat_doc = source_package-belnr.

ENDIF.

End routine:

Here is the end routine in which just i have modified code to write binary search:

I have just sorted internal table in ascending with read table keys and used binary search.

LOOP AT result_package ASSIGNING <result_fields>.
  wa_result_package = <result_fields>.
  CLEAR:wa_omrp,wa_result_package-kprice,
  wa_result_package-loc_currcy,
  wa_result_package-/bic/k_mrpvat,wa_plant.
*read data of mrp based on mat and plant
  READ TABLE it_omrp INTO wa_omrp WITH KEY
      material = wa_result_package-material
      plant = wa_result_package-plant
      rf_del_ind = '' .
*if data found assign mrp value to result
  IF sy-subrc EQ 0.
    wa_result_package-kprice = wa_omrp-kprice.
    wa_result_package-loc_currcy = wa_omrp-loc_currcy.
*if not found read data of mrp based on mat and region
  ELSE.
    READ TABLE it_plant INTO wa_plant
    WITH KEY plant = wa_result_package-plant BINARY SEARCH.
    IF sy-subrc EQ 0.
      v_reg = wa_plant-region.
      READ TABLE it_omrp INTO wa_omrp WITH KEY
          material = wa_result_package-material
          /bic/zregi = v_reg
          rf_del_ind = '' .
*if found assign mrp value to result
      IF sy-subrc EQ 0.
          wa_result_package-kprice = wa_omrp-kprice.
          wa_result_package-loc_currcy = wa_omrp-loc_currcy.
      ELSE.
*if not found read data based on mat and comp code
        READ TABLE it_omrp INTO wa_omrp WITH KEY
        material = wa_result_package-material
        comp_code = wa_result_package-comp_code
        rf_del_ind = ''.

        IF sy-subrc EQ 0.
          wa_result_package-kprice = wa_omrp-kprice.
          wa_result_package-loc_currcy = wa_omrp-loc_currcy.
        ENDIF.
      ENDIF.
    ENDIF.
  ENDIF.
  <result_fields>-kprice = wa_result_package-kprice.
  <result_fields>-loc_currcy = wa_result_package-loc_currcy.
*read mat sales data for material
  CLEAR:v_salesorg,v_reg.
  READ TABLE it_plant INTO wa_plant
      WITH KEY plant = wa_result_package-plant BINARY SEARCH.
  IF sy-subrc EQ 0.
    v_reg = wa_plant-region.
    SORT it_salesorg1 BY plantsal ASCENDING.
    READ TABLE it_salesorg1 INTO wa_salesorg1
    WITH KEY plantsal = wa_result_package-plant BINARY SEARCH.
    IF sy-subrc EQ 0.
      v_salesorg = wa_salesorg1-salesorg.
      SORT it_sales BY mat_sales salesorg ASCENDING.
      READ TABLE it_sales INTO wa_sales WITH KEY
        mat_sales = wa_result_package-material
        salesorg = v_salesorg
        distr_chan = 'F1' BINARY SEARCH.
      IF sy-subrc EQ 0.
        wa_result_package-salesorg = v_salesorg .
*if true then read mrp vat data from adso o_mrpvat based on material
*group
        READ TABLE it_mrpvat INTO wa_mrpvat WITH KEY
            /bic/zregi = v_reg
            mat_kondm = wa_sales-mat_kondm.

*if true assign mrp without vat value to result
        IF sy-subrc EQ 0.
          wa_result_package-/bic/k_mrpvat = wa_mrpvat-/bic/k_mrpvat.
          wa_result_package-loc_currcy = wa_mrpvat-loc_currcy.
        ENDIF.
      ENDIF.
    ENDIF.
  ENDIF.

  <result_fields>-/bic/k_mrpvat = wa_result_package-/bic/k_mrpvat.
  <result_fields>-loc_currcy = wa_result_package-loc_currcy.
  <result_fields>-salesorg = wa_result_package-salesorg.
ENDLOOP.

[matt]

In future, if you're going to post source code, make sure that you pretty print it first, and remove any commented out code. If your code is readable, then it's easier for people to help. I've done it for you this time, but in future, I'll reject your question as "unreadable".

[matt]

"But while am trying to read table using binary search my end routine code is not getting executed at all even i sorted internal table before read statement."

What do you mean, your code isn't getting executed? That it's not running quickly, or that it times out and dumps because it takes too long or what?

[matt]

For some reason the comments you made have disappeared.

You said But when i try to sort that 1st two internal table it_mrp & It_mrpvat in ascending order in the end routine and used with binary search my code is not getting execute.

What do you mean "Not getting executed". That makes no sense.

Also don't use BINARY SEARCH at all. Using HASHED tables. And don't ask for examples. This isn't a training site, and there are plenty of examples if you'll search.

[matt]

Just exercise restraint and don't log on. Or get someone else to change your password to something you don't know.

[matt]

"Since 1st 2 internal table are already sort in descending order in the start routine i have not used binary search for those table for rest i have used."This makes no sense. To use binary search you must have a table in sort order AND you must add BINARY SEARCH to the READ statement. If it's already sorted, then you still have to use BINARY SEARCH.

You are sorting it_sales in a loop. That means for every record in your result package, you sort the table again. You should only have sorted it once outside the loop.

But in any case, SORT/READ... BINARY SEARCH is largely obsolete. Use a HASHED table (if the key will be unique) or SORTED table (if the key isn't unique), and you'll find your READs are much faster. Read the documentation about HASHED and SORTED internal tables, and search for blogs and examples of how to use them. This type of internal table has been available since 2000 so there's no excuse not to use them.

Finally, if you're going to be writing much ABAP in routines, ask your employer to send you on an ABAP course. Otherwise, unless you are very diligent in your research and self-training, you're going to write very bad code.

[Former Member]

Then what is the use of community..

[matt]

To get expert advice about technical issues. Not hand holding nor training nor someone doing their job on your behalf. I've given you the benefit of my experience. I've spent quite some time helping you. I don't get paid for that - no-one here does.