topic SQL performance in Application Development and Automation Discussions

SQL performance

Former Member — Wed, 24 Oct 2007 09:12:20 GMT

Below is my program. I want to improve the performance using option A, however I cant get the data for lt_lqua.

Other than Option A, how can i improve on option B?

Thanks.

REPORT ZTEST_HL6.

DATA: BEGIN OF it_stotype OCCURS 0,

lgnum TYPE lgnum,

lgtyp TYPE t301-lgtyp,

END OF it_stotype.

DATA: BEGIN OF it_lqua OCCURS 0.

INCLUDE STRUCTURE lqua.

DATA: END OF it_lqua.

DATA : IT_ST2 LIKE IT_STOTYPE OCCURS 0 WITH HEADER LINE.

DATA : IT_LQ LIKE IT_LQUA OCCURS 0 WITH HEADER LINE.

DATA : v_zustd TYPE dzustd.

SELECT lgnum lgtyp FROM t301 INTO CORRESPONDING FIELDS OF TABLE it_stotype.

<b>Option A :</b>

IF it_stotype[] is not initial.

Loop at it_stotype.

SELECT lqua~lgtyp

lqua~verme

lqua~matnr

lqua~charg

INTO CORRESPONDING FIELDS OF TABLE it_lqua

FROM lqua

WHERE lgnum = it_stotype-lgnum AND

lgtyp = it_stotype-lgtyp AND

NOT EXISTS ( SELECT charg

FROM zmmim004

WHERE matnr = lqua~matnr

AND charg = lqua~charg

AND restricted EQ 'X' )

and verme NE 0.

endloop.

ENDIF.

<b>OPTION B :</b>

SELECT * FROM lqua INTO CORRESPONDING FIELDS OF TABLE it_lq

FOR ALL ENTRIES IN it_stotype

WHERE lgnum = it_stotype-lgnum AND

lgtyp = it_stotype-lgtyp AND

verme NE 0.

LOOP AT it_lq.

CLEAR : v_zustd.

SELECT SINGLE restricted FROM zmmim004 INTO v_zustd WHERE matnr = it_lq-matnr AND

charg = it_lq-charg.

IF v_zustd IS NOT INITIAL.

DELETE it_lq WHERE charg = it_lqua-charg.

ENDIF.

ENDLOOP.

Re: SQL performance

Former Member — Wed, 24 Oct 2007 09:16:32 GMT

Hi Hui,

You should try other option also

OPTION B :

SELECT * FROM lqua INTO CORRESPONDING FIELDS OF TABLE it_lq

FOR ALL ENTRIES IN it_stotype

WHERE lgnum = it_stotype-lgnum AND

lgtyp = it_stotype-lgtyp AND

verme NE 0.

LOOP AT it_lq.

CLEAR : v_zustd.

SELECT SINGLE restricted FROM zmmim004 INTO v_zustd WHERE matnr = it_lq-matnr AND

charg = it_lq-charg.

IF v_zustd IS NOT INITIAL.

DELETE it_lq WHERE charg = it_lqua-charg.

ENDIF.

ENDLOOP.

here change SELECT SINGLE and sue FOR ALL ENTRIES.

Regards,

Atish

Re: SQL performance

Former Member — Wed, 24 Oct 2007 09:20:28 GMT

Hi Hui,

You can use option B like this.

SELECT lgtyp verme matnr charg

FROM lqua INTO CORRESPONDING FIELDS OF TABLE it_lq

FOR ALL ENTRIES IN it_stotype

WHERE lgnum = it_stotype-lgnum AND

lgtyp = it_stotype-lgtyp AND

verme NE 0.

if sy-subrc ne 0. " This will not allow the further processing

exit.

endif.

CLEAR : v_zustd.

SELECT restricted FROM zmmim004 INTO v_zustd for all entries in it_lq WHERE matnr = it_lq-matnr AND

charg = it_lq-charg.

IF v_zustd IS NOT INITIAL.

DELETE it_lq WHERE charg = it_lqua-charg.

ENDIF.

Reward If Useful.

Regards,

Chitra

Message was edited by:

Chitra Parameswaran

Re: SQL performance

jayanthi_jayaraman — Wed, 24 Oct 2007 09:27:27 GMT

Hi,

if not it_lq[] is initial.

SELECT * FROM lqua INTO CORRESPONDING FIELDS OF TABLE it_lq

FOR ALL ENTRIES IN it_stotype

WHERE lgnum = it_stotype-lgnum AND

lgtyp = it_stotype-lgtyp AND

verme NE 0.

if sy-subrc eq 0.

SELECT matnr charg restricted FROM zmmim004 INTO table it_zmmim004 for all entries in it_lq WHERE matnr = it_lq-matnr AND

charg = it_lq-charg and restricted is initial.

endif.

LOOP AT it_lq.

read table it_zmmim004 with key matnr = it_lq-matnr

charg = it_lq-charg.

if sy-subrc ne 0.

DELETE it_lq WHERE matnr = it_lq-matnr AND

charg = it_lq-charg.

ENDIF.

ENDLOOP.

Re: SQL performance

Former Member — Wed, 24 Oct 2007 13:13:39 GMT

the performance for finding a good response would highly improve, if you all would use CODE to write your coding

Re: SQL performance

Former Member — Wed, 24 Oct 2007 17:53:53 GMT

Hi Hui Leng Yeoh ,

Please try the following code and let me know. Do not forget to include all the primary key fields where ever indicated by my comments in the code.

TYPES: BEGIN OF ty_lqua,
         lgtyp  TYPE lqua-lgtyp,
         verme  TYPE lqua-verme,
         matnr  TYPE lqua-matnr,
         charg  TYPE lqua-charg,
         lgnum  TYPE lqua-lgnum,
         lqnum  TYPE lqua-lqnum,
         del(1) TYPE c         ,
       END OF ty_lqua,

       BEGIN OF ty_zmmim004,
         matnr TYPE lqua-matnr,
         charg TYPE lqua-charg,
*        Make sure that all the fields of the primary key are included
       END OF ty_zmmim004.

DATA: w_index     TYPE                 sy-tabix   ,
      w_lqua      TYPE                 ty_lqua    ,

      it_stotype  TYPE        TABLE OF t301       ,
      it_lqua     TYPE        TABLE OF ty_lqua    ,
      it_lqua_tmp TYPE        TABLE OF ty_lqua    ,
      it_zmmim004 TYPE SORTED TABLE OF ty_zmmim004
        WITH NON-UNIQUE KEY matnr charg.


SELECT *
  FROM t301
  INTO TABLE it_stotype.

IF sy-subrc EQ 0.

  SELECT lgtyp
         verme
         matnr
         charg
         lgnum
         lqnum
    FROM lqua
    INTO TABLE it_lqua
    FOR ALL ENTRIES IN it_stotype
    WHERE lgnum EQ it_stotype-lgnum
    AND   lgtyp EQ it_stotype-lgtyp
    AND   verme NE 0.

  IF sy-subrc EQ 0.

    it_lqua_tmp[] = it_lqua[].

    SORT it_lqua_tmp BY matnr charg.

    DELETE ADJACENT DUPLICATES FROM it_lqua_tmp COMPARING matnr charg.

    SELECT matnr
           charg
*          Make sure all the primary key fields are present
      FROM zmmim004
      INTO TABLE it_zmmim004
      FOR ALL ENTRIES IN it_lqua_tmp
      WHERE matnr      EQ it_lqua_tmp-matnr
      AND   charg      EQ it_lqua_tmp-charg
      AND   restricted EQ 'X'.

    LOOP AT it_lqua INTO w_lqua.

      w_index = sy-tabix.

      READ TABLE it_zmmim004 WITH KEY matnr = w_lqua-matnr
                                      charg = w_lqua-charg
                                      TRANSPORTING NO FIELDS.
      IF sy-subrc EQ 0.

        w_lqua-del = 'X'.

        MODIFY it_lqua FROM w_lqua INDEX w_index TRANSPORTING del.

      ENDIF.

    ENDLOOP.

    DELETE it_lqua WHERE del = 'X'.

  ENDIF.

ENDIF.

Re: SQL performance

Former Member — Wed, 24 Oct 2007 22:36:52 GMT

Hi,

Remove the INTO CORRESPONDING fields.

Do not use Nested selects instead use FOR ALL ENTRIES.

Thanks,

Srinivas