What is causing bad performance?

[former_member447950]

Hello,

I am writing an extract program and it runs very slow when I give a date range. When I just give one date it runs in 2 seconds... a range of dates can take 5-6 minutes. Do you have any ideas of how to improve performance?

TABLES: likp,

         lips.

DATA: lv_filepath   TYPE char100.

*================================================================================*

* INITIALIZATION *

*================================================================================*

INITIALIZATION.

   CONCATENATE: '/usr/sap' sy-sysid 'interfaces/fi' INTO lv_filepath SEPARATED BY '/'.

*================================================================================*

* SELECTION SCREEN *

*================================================================================*

   SELECTION-SCREEN BEGIN OF BLOCK a1 WITH FRAME TITLE text-001.

*Outbound delivery type

   SELECTION-SCREEN BEGIN OF LINE.

   SELECTION-SCREEN COMMENT 2(48) text-002.

   SELECT-OPTIONS s_lfart FOR likp-lfart OBLIGATORY DEFAULT 'ZNL1'.

   SELECTION-SCREEN END OF LINE.

*Shipping point

   SELECTION-SCREEN BEGIN OF LINE.

   SELECTION-SCREEN COMMENT 2(48) text-003.

   SELECT-OPTIONS s_vstel FOR likp-vstel OBLIGATORY.

   SELECTION-SCREEN END OF LINE.

*Outbound delivery #

   SELECTION-SCREEN BEGIN OF LINE.

   SELECTION-SCREEN COMMENT 2(48) text-004.

   SELECT-OPTIONS s_vbeln FOR likp-vbeln.

   SELECTION-SCREEN END OF LINE.

*Actual PGI date

   SELECTION-SCREEN BEGIN OF LINE.

   SELECTION-SCREEN COMMENT 2(48) text-005.

   SELECT-OPTIONS s_wadat FOR likp-wadat_ist OBLIGATORY.

   SELECTION-SCREEN END OF LINE.

*Material number

   SELECTION-SCREEN BEGIN OF LINE.

   SELECTION-SCREEN COMMENT 2(48) text-006.

   SELECT-OPTIONS s_matnr FOR lips-matnr.

   SELECTION-SCREEN END OF LINE.

*Goods movement status for header (unchangeable)

   SELECTION-SCREEN BEGIN OF LINE.

   SELECTION-SCREEN COMMENT 2(48) text-008.

   PARAMETERS p_wbstk TYPE wbstk DEFAULT 'C' OBLIGATORY MODIF ID 001.

   p_wbstk = 'C'.

   SELECTION-SCREEN END OF LINE.

*Goods movement status for material (unchangeable)

   SELECTION-SCREEN BEGIN OF LINE.

   SELECTION-SCREEN COMMENT 2(48) text-009.

   PARAMETERS p_wbsta TYPE wbsta DEFAULT 'C' OBLIGATORY MODIF ID 001.

   p_wbsta = 'C'.

   SELECTION-SCREEN END OF LINE.

*File path

   SELECTION-SCREEN BEGIN OF LINE.

   SELECTION-SCREEN COMMENT 2(48) text-007.

   PARAMETERS p_file TYPE char100 DEFAULT lv_filepath LOWER CASE OBLIGATORY.

   p_file = lv_filepath.

   SELECTION-SCREEN END OF LINE.

*Job log with detail

   SELECTION-SCREEN BEGIN OF LINE.

   SELECTION-SCREEN COMMENT 2(48) text-010.

   PARAMETERS p_check AS CHECKBOX.

   SELECTION-SCREEN END OF LINE.

   SELECTION-SCREEN END OF BLOCK a1.

*Make goods movement status unchangeable

AT SELECTION-SCREEN OUTPUT.

   LOOP AT SCREEN.

     IF screen-group1 = '001'.

       screen-input = 0.

       MODIFY SCREEN.

     ENDIF.

   ENDLOOP.

*================================================================================*

* START OF SELECTION *

*================================================================================*

START-OF-SELECTION.

*================================================================================*

* DATA DECLARATION *

*================================================================================*

   TYPES: BEGIN OF ty_matinfo,

            werks     TYPE werks,

            matnr     TYPE matnr,

            comp      TYPE matnr,

            maktx     TYPE maktx, "component description

            gamng     TYPE bdmng,

            sto       TYPE lips-vgbel,

            sto_pos   TYPE lips-vgpos,

            deliv     TYPE lips-vbeln,

            deliv_pos TYPE lips-posnr,

            po_num    TYPE lips-vbelv,

            po_pos    TYPE lips-posnv,

            uom       TYPE meins,

          END OF ty_matinfo.

   TYPES: BEGIN OF ty_likp,

            vbeln TYPE vbeln,

            vstel TYPE vstel,

          END OF ty_likp.

   TYPES: BEGIN OF ty_lips,

            vbeln TYPE vbeln,

            posnr TYPE posnr,

            matnr TYPE matnr,

            werks TYPE werks,

            vbelv TYPE vbelv,

            posnv TYPE posnv,

            vgbel TYPE vgbel,

            vgpos TYPE vgpos,

            mtart TYPE mtart,

          END OF ty_lips.

   DATA: it_matinfo   TYPE TABLE OF ty_matinfo,

         wa_matinfo   LIKE LINE OF it_matinfo,

         it_lips      TYPE TABLE OF ty_lips,

         wa_lips      LIKE LINE OF it_lips,

         it_likp      TYPE TABLE OF ty_likp,

         wa_likp      LIKE LINE OF it_likp,

         lv_wbstk     TYPE bestk,

         lv_wbsta     TYPE wbsta,

         lv_aufnr     TYPE aufnr,

         lv_posnr     TYPE posnr,

         it_component TYPE TABLE OF bapi_order_component,

         wa_component LIKE LINE OF it_component,

         lv_quantity  TYPE char25,

         file         TYPE string,

         wa_bapi      TYPE bapi_pp_order_objects,

         lv_count(15) TYPE c,

         lv_message   TYPE char100,

         count        TYPE int4.

*================================================================================*

* SELECT STATEMENTS *

*================================================================================*

   IF s_vbeln IS NOT INITIAL. "If user provided deliveries go directly to LIPS

     SELECT vbeln posnr matnr werks vbelv posnv vgbel vgpos mtart FROM lips

       INTO CORRESPONDING FIELDS OF TABLE it_lips

       WHERE vbeln IN s_vbeln

       AND   matnr IN s_matnr.

     IF it_lips IS INITIAL.

       MESSAGE 'No deliveries found. Please check selection criteria.' TYPE 'I'.

     ENDIF.

   ELSE. "If no deliveries were provided we need to first go into LIKP

     SELECT vbeln vstel FROM likp

       INTO CORRESPONDING FIELDS OF TABLE it_likp

       WHERE wadat_ist IN s_wadat

       AND   vstel IN s_vstel

       AND   lfart IN s_lfart.

     IF it_likp IS INITIAL.

       MESSAGE 'No deliveries found. Please check selection criteria.' TYPE 'I'.

     ELSE.

       SELECT vbeln posnr matnr werks vbelv posnv vgbel vgpos mtart FROM lips

         INTO CORRESPONDING FIELDS OF TABLE it_lips

         FOR ALL ENTRIES IN it_likp

         WHERE vbeln = it_likp-vbeln

         AND   matnr IN s_matnr

         AND   mtart = 'KMAT'. "Only want KMAT records

       IF it_lips IS INITIAL.

         MESSAGE 'No deliveries found. Please check selection criteria.' TYPE 'I'.

       ENDIF.

     ENDIF.

   ENDIF.

[Former Member]

Range of dates in large tables usually have bad performance, considering tha date is stored as char in the DB.

How many records are in your LIKP table and how many are retrieved with a range of dates?

Would it be possible to include some key or index field in your selection?

Regards

[former_member447950]

Hi Luis - That is what I assumed. Unfortunately, the only key field in LIKP is delivery which is why we are selecting from LIKP anyways. Many entries are stored in LIKP and many are retrieved within a date which is what I assumed was causing performance issues.

[former_member210963]

Hi Jess,

Create View for LIKP Table, and use it instead of LIKP table you will get the more performance.

Regards,

Sivanadh

[raymond_giuseppi]

Could you try to JOIN LIKP and LIPS in second case, so optimizer may use of actual criteria of both tables to choose the best index.

Did you execute a SQL trace (ST05) to analyze access plan ?

Regards,

Raymond

[former_member195402]

Hi Jess,

do you have any active LIKP secondary index for fields LFART, WADAT_ISt or VSTEL? For example in our systems we have a Z index for fields MANDT / LFART. Any of them might be helpful.

Regards,

Klaus

[former_member447950]

Hi everyone,

Thank you all for the responses.

I ran a SQL trace and found the select query on LIKP was taking a while. It is because delivery number is the only key.

We have elected to run the program in the background. It takes ~5 minutes each time but is not a major issue. I appreciate all of your advice. If I run into this again, I will try other options.