Start Routine Issue

[former_member184884]

Hi Mates,

We have are facing one strange issue in our production system we just fed up, i need all your valuable support in resolving this issue, Hope i will succeeded in this.

Scenario:

There is delta DTP which is part of daily process chain which will load the data from DSO(standard) to Cube as usual in the transformation level their is some business logic is written both in the start & end routine but this DTP will approximately 1.5 million records for this it will take

approx 10-14 hrs runtime,so after analysis we have found code which is written in the start routine is not optimized so we have done some minor changes in the code to improve the load performance.

But issue started from here onward we have done changes on development and transported to quality we have ran same DTP which is their in the production it's performance has improved drastically for 1.7 million records it took only 15 mins runtime so we felt very happy and on the same day we have transported code to production but big magic has happened here only.

when that particular DTP has ran in the production through schedule process chain it running for long time like for one data package(50000) to complete it is taking 3-4 hours so to pull all 1.5 million records it is running for 2 days due to this long run it has impacted entire production system means other loads.

So my doubt here is why it is take acting strange in the production, Please give your valuable reviews on my issue.

Below is the piece of code which is there is in the start routine

DATA : s_material  TYPE /bi0/pmaterial ,

          t_material  LIKE STANDARD TABLE OF s_material,

          wa_material LIKE LINE OF t_material,

         s_matunit   TYPE /bi0/pmat_unit,

          t_matunit   LIKE STANDARD TABLE OF s_matunit,

          wa_matunit  LIKE LINE OF t_matunit.

DATA : s_primrate TYPE /bic/azsd_o01900,

       t_primrate LIKE STANDARD TABLE OF s_primrate,

       wa_primrate LIKE LINE OF t_primrate,

       min_calday LIKE sy-datum.

DATA : BEGIN OF r_compcode OCCURS 100,

        sign   TYPE c LENGTH 1,

        option TYPE c LENGTH 2,

        low    TYPE c LENGTH 4,

        high   TYPE c LENGTH 4,

       END OF r_compcode.

data : s_/BI0/PCOMP_CODE  type /BI0/PCOMP_CODE ,

        t_/BI0/PCOMP_CODE  like STANDARD TABLE OF s_/BI0/PCOMP_CODE,

       wa_/BI0/PCOMP_CODE LIKE LINE OF t_/BI0/PCOMP_CODE.

data  wa_r_compcode like LINE OF r_compcode.

CLASS lcl_transform IMPLEMENTATION.

  METHOD start_routine.

    FIELD-SYMBOLS:

      <SOURCE_FIELDS>    TYPE _ty_s_SC_1.

    DATA: MONITOR_REC     TYPE rstmonitor.

min_calday = sy-datum - 30.

    if SOURCE_PACKAGE is NOT INITIAL.

      SELECT *

        INTO TABLE t_material

        FROM /bi0/pmaterial

        FOR ALL ENTRIES IN SOURCE_PACKAGE

       WHERE material eq SOURCE_PACKAGE-material

        and  objvers EQ 'A'.

      SELECT *

         INTO TABLE t_matunit

          FROM /bi0/pmat_unit

          FOR ALL ENTRIES IN SOURCE_PACKAGE

          WHERE material eq SOURCE_PACKAGE-material

          and objvers EQ 'A'.

   endif.

    select *

      into table T_/BI0/PCOMP_CODE

      from /BI0/PCOMP_CODE

     where COUNTRY eq 'PK'

       and OBJVERS eq 'A'.

    loop at t_/BI0/PCOMP_CODE into wa_/BI0/PCOMP_CODE.

      wa_r_compcode-sign   = 'I'.

      wa_r_compcode-option = 'EQ'.

      wa_r_compcode-low    = wa_/BI0/PCOMP_CODE-COMP_CODE.

      wa_r_compcode-high   = ''.

      append wa_r_compcode to r_compcode.

    endloop.

    IF SOURCE_PACKAGE[] IS NOT INITIAL.

  REFRESH: t_primrate.

  DELETE SOURCE_PACKAGE[] WHERE comp_code NOT IN r_compcode.

  SELECT *

        INTO TABLE t_primrate

        FROM /bic/azsd_o01900

        FOR ALL ENTRIES IN SOURCE_PACKAGE

        WHERE salesorg EQ SOURCE_PACKAGE-salesorg

        and comp_code EQ SOURCE_PACKAGE-comp_code

        and division EQ SOURCE_PACKAGE-division

        and distr_chan EQ SOURCE_PACKAGE-distr_chan

        and calday GT min_calday

        and customer  EQ SOURCE_PACKAGE-/bic/zsd_dist

        and material EQ SOURCE_PACKAGE-material.

   ENDIF.

    SORT t_material BY material objvers.

    SORT t_matunit  BY material mat_unit objvers.

    SORT t_primrate BY salesorg   comp_code division

                       distr_chan calday    customer material.

ENDMETHOD.                   

METHOD inverse_start_routine.

ENDMETHOD.      

ENDCLASS.                                

Please free to ask me if you have any doubts regarding my issue.

Regards,

Harish

[KamalMehta]

Hi Harish ,

There is really lot of scope of improvement in your code.

1. Use only required fields instead of select *. Doing this only you would see lot of performance improvement in your loading.

2.

select *

      into table T_/BI0/PCOMP_CODE

      from /BI0/PCOMP_CODE

     where COUNTRY eq 'PK'

       and OBJVERS eq 'A'.

    loop at t_/BI0/PCOMP_CODE into wa_/BI0/PCOMP_CODE.

      wa_r_compcode-sign   = 'I'.

      wa_r_compcode-option = 'EQ'.

      wa_r_compcode-low    = wa_/BI0/PCOMP_CODE-COMP_CODE.

      wa_r_compcode-high   = ''.

      append wa_r_compcode to r_compcode.

    endloop.

You are only using COMP_CODE here to populate r_compcode so select COMP_CODE only in the select statement used above.

3. Also can you please let us know i see you are preparing lot of internal tables in start routine such as

t_material ,t_matunit  ,t_primrate. Are you reading them in your field routines.

4. Does rest of the coding in your transformation end routine etc is working fine. Check those as well.

Thanks

Kamal

[former_member184884]

Hi Kamal,

Thanks for your valuable inputs, i'll accept there is so wide scope to improve my code but my real concern is already expressed here.

t_material ,t_matunit  ,t_primrate. are used in field routines and as well as in end routines to perform some business logic.

rest of the code is working fine because we have not touched that piece of code as it is not time consuming one so we have just concentrated on to optimize start routine code.

i have attached screenshot DTP run monitor screen hope it may help you in some extent for better understanding of my issue.

Please do let me know if you need any inputs from my end.

Kind Regards,

Harish

[KamalMehta]

Hi Harish ,

Its clearly evident from your screenshot that start routine is consuming most of the time.

As we have already given you several pointers where you need to check and take care of i.e. Select * and loops if possible in your code , Populate t_/BI0/PCOMP_CODE with company code only.

Please optimize them in the code and lets verify the DTP runtime now.

Thanks