topic Re: SQL performance in Application Development and Automation Discussions

SQL performance

Former Member — Fri, 11 Apr 2008 05:46:48 GMT

Hi,

The following query statement has taken a significant amount of time processing. can someone help to give suggestion on how to improve the performance?

SQL Code :

SELECT * FROM lqua INTO CORRESPONDING FIELDS OF TABLE it_lqua

FOR ALL ENTRIES IN it_zvpa[]

WHERE matnr = it_zvpa-zmatnr

AND werks = it_zvpa-zplant

AND lgort = it_zvpa-zstoloc

AND lgnum = it_zvpa-zlgnum

AND letyp in ('P1','P2','P3','P1','P2','P3*','BLK')

AND lgtyp = ( SELECT zstotyp FROM zvpapslwstyp

WHERE zplant = it_zvpa-zplant

AND zstoloc = it_zvpa-zstoloc

AND zwhouse = it_zvpa-zlgnum ) .

Re: SQL performance

Former Member — Fri, 11 Apr 2008 05:51:57 GMT

remove into corresponding and also the subquery written inside the main query

Regards

Shiva

Re: SQL performance

venkat_o — Fri, 11 Apr 2008 06:43:39 GMT

Hui Leng Yeoh, Please try to develop the code like that. It develops the performance.

IF it_zvpa[] IS NOT INITIAL."This check is must. If it_zvpa table does not have data, select query gives dump.
  SELECT zstotyp
    FROM zvpapslwstyp
    INTO TABLE i_zstotyp " or use INTO CORRESPONDING FIELDS OF TABLE
  FOR ALL ENTRIES IN it_zvpa
    WHERE zplant  = it_zvpa-zplant
      AND zstoloc = it_zvpa-zstoloc
      AND zwhouse = it_zvpa-zlgnum.
ENDIF.

LOOP AT it_zvpa .
  IF it_zvpa[] IS NOT INITIAL.
    SELECT *  "Please try to give what fields exactly you need
      FROM lqua
      INTO CORRESPONDING FIELDS OF TABLE it_lqua " Please try to use INTO TABLE option ..not necessary.
      FOR ALL ENTRIES IN it_zvpa[]
    WHERE matnr = it_zvpa-zmatnr
      AND werks = it_zvpa-zplant
      AND lgort = it_zvpa-zstoloc
      AND lgnum = it_zvpa-zlgnum
      AND letyp IN ('P1','P2','P3','P1*','P2*','P3*','BLK')
      AND lgtyp = i_zstotyp-zstotyp.
  ENDIF.
ENDLOOP.

I hope that it helps you . Regards, Venkat.O

Re: SQL performance

Former Member — Fri, 11 Apr 2008 07:35:01 GMT

subqueries are in principle o.k. and can help to increase performance.

However, in your case you use the subquery together with the FOR ALL ENTRIES table, I am not sure whether this is advisable.

Before you change anything you should check the indexes, which fields appear in an index? If there is an index, but the performance is still poor

( read /people/siegfried.boes/blog/2007/09/05/the-sql-trace-st05-150-quick-and-easy, low performance means cost per record (minimal and average) is larger 10.000 microsec)

then you should change the statement.

You must apply a FOR ALL ENTRIES to the the table zvpapslwstyp first and get the zstotyp values


loop at  it_zvpa[] into wa.
    SELECT single zstotyp 
                 FROM zvpapslwstyp
                 into wa-lgtyp  
                 WHERE zplant = it_zvpa-zplant 
                  AND zstoloc = it_zvpa-zstoloc 
                 AND zwhouse = it_zvpa-zlgnum 
    append wa to itab.
endloop.

SELECT * FROM lqua 
         INTO CORRESPONDING FIELDS OF TABLE it_lqua 
         FOR ALL ENTRIES IN itab 
         WHERE matnr = itab-zmatnr
         AND werks = itab-zplant
         AND lgort = itab-zstoloc
         AND lgnum = it_zvpa-zlgnum
         AND letyp in  'P1','P2','P3','P1*','P2*','P3*','BLK')
         AND lgtyp =  itab-lgtyp.

Siegfried

Re: SQL performance

Former Member — Fri, 11 Apr 2008 07:50:04 GMT

Hi Venkat

AND lgtyp = i_zstotyp-zstotyp. "

This is internal table, can you refer like this?

Re: SQL performance

venkat_o — Fri, 11 Apr 2008 09:55:08 GMT

Hi Hui Leng Yeoh, I did not get what u asked ? Regards, Venkat.O

Re: SQL performance

Former Member — Fri, 11 Apr 2008 10:47:25 GMT

@Venkat

your solution is incorrect, the lower select works with 2 driver tables which is not possible.

@Hui Leng,

I gave you a solution and anyway the index question is always the first you must check.

The select inside a loop is not the best solution but no issue, if this is gthe right there is still some optimization possible (but of lower order).

Siegfried

Re: SQL performance

Former Member — Fri, 11 Apr 2008 13:49:29 GMT

Hi Hui Leng Yeoh,

You can try the following piece of code and check if it improves the performance.

TYPES: BEGIN OF ty_zvpapslwstyp,
         zstotyp TYPE zvpapslwstyp-zstotyp,
       END OF ty_zvpapslwstyp.

DATA: w_zvpapslwstyp  TYPE          ty_zvpapslwstyp,

      it_zvpapslwstyp TYPE TABLE OF ty_zvpapslwstyp,
      it_zvpa_tmp     LIKE TABLE OF it_zvpa        ,
      it_lqua         TYPE TABLE OF lqua           .

RANGES: r_zstotyp FOR zvpapslwstyp-zstotyp.


IF NOT it_zvpa[] IS INITIAL.

  REFRESH r_zstotyp.

  it_zvpa_tmp[] = it_zvpa[].

  SORT it_zvpa_tmp BY zplant
                      zstoloc
                      zlgnum.

  DELETE ADJACENT DUPLICATES FROM it_zvpa_tmp COMPARING zplant
                                                        zstoloc
                                                        zlgnum.
  SELECT zstotyp
    FROM zvpapslwstyp
    INTO TABLE it_zvpapslwstyp
    FOR ALL ENTRIES IN it_zvpa_tmp
    WHERE zplant  EQ it_zvpa_tmp-zplant
    AND   zstoloc EQ it_zvpa_tmp-zstoloc
    AND   zwhouse EQ it_zvpa_tmp-zlgnum.

  IF sy-subrc EQ 0.

    SORT it_zvpapslwstyp BY zstotyp.

    DELETE ADJACENT DUPLICATES FROM it_zvpapslwstyp COMPARING zstotyp.

    r_zstotyp-sign = 'I'.

    r_zstotyp-option = 'EQ'.

    LOOP AT it_zvpapslwstyp INTO w_zvpapslwstyp.

      r_zstotyp-low = w_zvpapslwstyp-zstotyp.

      APPEND r_zstotyp.

    ENDLOOP.

    CLEAR r_zstotyp.

    it_zvpa_tmp[] = it_zvpa[].

    SORT it_zvpa_tmp BY zlgnum
                        zmatnr
                        zplant
                        zstoloc.

    DELETE ADJACENT DUPLICATES FROM it_zvpa_tmp COMPARING zlgnum
                                                          zmatnr
                                                          zplant
                                                          zstoloc.
    SELECT *
      FROM lqua
      INTO TABLE it_lqua
      FOR ALL ENTRIES IN it_zvpa_tmp
      WHERE lgnum EQ it_zvpa_tmp-zlgnum
      AND   matnr EQ it_zvpa_tmp-zmatnr
      AND   werks EQ it_zvpa_tmp-zplant
      AND   lgort EQ it_zvpa_tmp-zstoloc
      AND   letyp IN ('P1','P2','P3','P1*','P2*','P3*','BLK')
      AND   lgtyp IN r_zstotyp.

  ENDIF.

ENDIF.

Re: SQL performance

Former Member — Mon, 14 Apr 2008 17:16:12 GMT

HI,

For letyp IN ('P1','P2','P3','P1','P2','P3*','BLK')

create a ranges and use range in the select statement.

Thanks,

Sriram Ponna.