topic Re: Select performance in Application Development and Automation Discussions

Select performance

Former Member — Tue, 13 Nov 2007 15:42:19 GMT

Hello to everyone,

I have to improve the performance of this select, because it takes several minutes.

Could someone help me ?

SELECT LIKP_{VBELN LIKP}LFDAT LIKP~WADAT_IST

LIPS_{POSNR LIPS}LFIMG LIPS_{MEINS LIPS}VGBEL LIPS~VGPOS

INTO CORRESPONDING FIELDS OF TABLE LT_LIPS

FROM LIKP JOIN LIPS ON LIKP_{VBELN = LIPS}VBELN

FOR ALL ENTRIES IN P_ITEM

WHERE LIPS~VGBEL = P_VBELN

AND LIPS~VGPOS = P_ITEM-POSNR.

Re: Select performance

Former Member — Tue, 13 Nov 2007 15:52:40 GMT

You aren't using a key filed in the SELECT, so there's not much you can do. But try this anyway:

SORT p_item BY posnr.
DELETE ADJACENT DUPLICATES FROM p_item COMPARING posnr.

SELECT likp~vbeln likp~lfdat likp~wadat_ist
       lips~posnr lips~lfimg lips~meins lips~vgbel lips~vgpos
  INTO CORRESPONDING FIELDS OF TABLE lt_lips
   FROM likp JOIN lips ON likp~vbeln =  lips~vbeln
   FOR ALL entries IN p_item
   WHERE lips~vgbel = p_vbeln
   AND   lips~vgpos = p_item-posnr.

Rob

Re: Select performance

Former Member — Tue, 13 Nov 2007 16:43:37 GMT

Hi,

Avoid CORRESPONDING FIELDS OF, this major impact on this query.

Declare internal table table only with the required fields.

Reward if it helps,

Satish

Re: Select performance

Former Member — Tue, 13 Nov 2007 17:28:53 GMT

the move into corresponding is no issue with the join!

Check the indexes on the two tables LIKP and LIPS and the size of tables.

Read this blog on the SQL trace:

/people/siegfried.boes/blog/2007/09/05/the-sql-trace-st05-150-quick-and-easy

Check the explain and try to understand selectivity, only with a selective index support you can get good performance.

Siegfried

Re: Select performance

Former Member — Wed, 14 Nov 2007 21:32:19 GMT

Try using this code. Let me know if this helps.

TYPES: BEGIN OF ty_lips,
         vbeln     TYPE lips-vbeln    ,
         posnr     TYPE lips-posnr    ,
         lfimg     TYPE lips-lfimg    ,
         meins     TYPE lips-meins    ,
         vgbel     TYPE lips-vgbel    ,
         vgpos     TYPE lips-vgpos    ,
         lfdat     TYPE likp-lfdat    ,
         wadat_ist TYPE likp-wadat_ist,
       END OF ty_lips,

       BEGIN OF ty_likp,
         vbeln     TYPE likp-vbeln    ,
         lfdat     TYPE likp-lfdat    ,
         wadat_ist TYPE likp-wadat_ist,
       END OF ty_likp.

DATA:  w_lips      TYPE                 ty_lips ,
       w_likp      TYPE                 ty_likp ,
       w_index     TYPE                 sy-tabix,

       lt_item     LIKE        TABLE OF p_item  ,
       lt_lips     TYPE        TABLE OF ty_lips ,
       lt_lips_tmp TYPE        TABLE OF ty_lips ,
       lt_likp     TYPE HASHED TABLE OF ty_likp
         WITH UNIQUE KEY vbeln.

IF NOT p_item[] IS INITIAL.

  lt_item[] = p_item[].

  SORT lt_item BY posnr.

  DELETE ADJACENT DUPLICATES FROM lt_item COMPARING posnr.

  SELECT vbeln
         posnr
         lfimg
         meins
         vgbel
         vgpos
    FROM lips
    INTO TABLE lt_lips
    FOR ALL ENTRIES IN lt_item
    WHERE vgbel EQ p_vbeln
    AND   vgpos EQ lt_item-posnr.

  IF sy-subrc EQ 0.

    SORT lt_lips BY vbeln posnr.

    lt_lips_tmp[] = lt_lips[].

    DELETE ADJACENT DUPLICATES FROM lt_lips_tmp COMPARING vbeln.

    SELECT vbeln
           lfdat
           wadat_ist
      FROM likp
      INTO TABLE lt_likp
      FOR ALL ENTRIES IN lt_lips_tmp
      WHERE vbeln EQ lt_lips_tmp-vbeln.

  ENDIF.


  LOOP AT lt_lips INTO w_lips.

    w_index = sy-tabix.

    AT NEW vbeln.
      READ TABLE lt_likp INTO w_likp WITH KEY vbeln = w_lips-vbeln
        TRANSPORTING
          lfdat
          wadat_ist.
      IF sy-subrc NE 0.
        CLEAR w_likp.
      ENDIF.
    ENDAT.

    w_lips-lfdat = w_likp-lfdat.

    w_lips-wadat_ist = w_likp-wadat_ist.

    MODIFY lt_lips FROM w_lips INDEX w_index
      TRANSPORTING
        lfdat
        wadat_ist.

  ENDLOOP.

ENDIF.

Re: Select performance

Former Member — Thu, 15 Nov 2007 07:59:43 GMT

Select Statements contd…For All Entries• The for all entries creates a where clause, where all the entries in the driver table are combined with OR. If the number of entries in the driver table is larger than rsdb/max_blocking_factor, several similar SQL statements are executed to limit the length of the WHERE clause.

The plus

• Large amount of data

• Mixing processing and reading of data

• Fast internal reprocessing of data

• Fast

The Minus

• Difficult to program/understand

• Memory could be critical (use FREE or PACKAGE size)

Points to be must considered FOR ALL ENTRIES • Check that data is present in the driver table

• Sorting the driver table

• Removing duplicates from the driver table

Consider the following piece of extract

Loop at int_cntry.

Select single * from zfligh into int_fligh

where cntry = int_cntry-cntry.

Append int_fligh.

Endloop.

The above mentioned can be more optimized by using the following code.

Sort int_cntry by cntry.

Delete adjacent duplicates from int_cntry.

If NOT int_cntry[] is INITIAL.

Select * from zfligh appending table int_fligh

For all entries in int_cntry

Where cntry = int_cntry-cntry.

Endif.

Re: Select performance

Former Member — Thu, 15 Nov 2007 08:42:00 GMT

I created a new index in LIPS and now the performance is better.

Thank you

Re: Select performance

Former Member — Thu, 15 Nov 2007 08:52:09 GMT

Sorry i didn't flag my message as question while creating . How can i reward your answer ?

Re: Select performance

Former Member — Thu, 15 Nov 2007 14:19:08 GMT

Sure - the performance of your single SELECT statement is better, but the creation of the new index will have a negative impact every time a new delivery is created. The new index will now have to be kept upd to date as well.

It's better to find a programming solution, let the users run the program in the background or live with slow performance on this single program.

Rob