Select statements used in POSDM Z-implementation

[Former Member]

The code being run by  ABAP team is hitting an upper limit. We had reduced the transaction volumes to 10000 per batch and appears to be running OK. But when loads increase the system deteriorates.  We have been asked to investigate the following.  

1. 1.       The SQLs are using the indexes. ( Cost Optimizer). Please re-create all the missing indexes, If any. ( else SQL goes for a table scan.) How big this table is and whether an index has an impact is another issue…)
2. 2.       Regarding the “time-out” error occurring for huge loads, why is a Dialog WP being assigned to run the job? Whereas, it should be typically be assigned to a background WP.

  Could you please help us to investigate and how to proceed with this further. If necessary we may turn on the trace in the quality.

Job :                       ZO_SD_SENDTOBW

Program:             /POSDW/PIPEDISPATCHER,

Variant:                BW

  "Fetch transaction type code from ZTVARVC table
  SELECT var_name var_value
  FROM ztvarvc
  INTO TABLE lt_var
  WHERE prg_name EQ lc_prg_name.

      SELECT plant distr_chan salesorg                "#EC CI_SGLSELECT
      INTO TABLE lt_pplant
      FROM /bi0/pplant
      WHERE plant   = lw_ct_plant-plant AND
            objvers = lc_objvers .

          SELECT ws_datab ws_datbi me_condrt            "#CR
                 salesorg distr_chan                  "#EC CI_SGLSELECT
          INTO TABLE lt_pcondrecno
          FROM /bi0/pcondrecno
          FOR ALL ENTRIES IN lt_pplant
          WHERE objvers = lc_objvers AND
                kschl = lc_cndtyp    AND
                distr_chan = lt_pplant-distr_chan AND
                salesorg   = lt_pplant-salesorg.

The below select is in loop and sort is also in the loop.

              SELECT rt_promo rt_dscdcl ad_name
              FROM  /bi0/prt_promo
              INTO TABLE lt_prt_promo BYPASSING BUFFER
              WHERE rt_prslfrm  LE lv_rt_prslfrm "c_transaction-businessdaydate
              AND rt_prslto   GE lv_rt_prslto ."c_transaction-businessdaydate.

      SELECT plant                                 "#CR
             distr_chan                               "#EC CI_SGLSELECT
             salesorg
        FROM /bi0/pplant
        INTO TABLE lt_plant
        WHERE plant = lw_plant_temp-plant
        AND objvers = lc_objvers.

        SELECT ws_datab                              "#CR
               ws_datbi
               kschl
               material
               me_condrt
               salesorg
               distr_chan
          FROM /bi0/pcondrecno
          INTO TABLE lt_pcond
          WHERE ( ( ws_datab  LE c_transaction-businessdaydate
            AND     ws_datbi  GE c_transaction-businessdaydate )
            AND   ( kschl  = lw_var1-var_value
            OR      kschl  = lw_var2-var_value ) ).

Thanks,

Madhavan K

[Former Member]

Hi Madhavan,

Please let me know whether the issue is resolved or not. If not will investigate on it .

KR,

Ankit Maskara.