Skip to Content
author's profile photo Former Member
0
Former Member

Query Performance

Posted on Dec 04, 2007 at 07:11 AM 10 Views

Hi,

I have written a SQL query, but i found that the performance is not good:

SELECT A1~VBELN
         WADAT_IST
         BOLNR
         C1~LIFNR
         B1~MATNR
         LFIMG
         BTGEW
         B1~CHARG
    INTO CORRESPONDING FIELDS OF TABLE TMP_REPORT
    FROM LIKP AS A1 JOIN LIPS AS B1 ON A1~VBELN = B1~VBELN
        JOIN MCHA AS C1 ON B1~MATNR = C1~MATNR
                           AND
                           B1~WERKS = C1~WERKS
                           AND
                           B1~CHARG = C1~CHARG
     WHERE LFART = 'EL'
           AND
           WADAT_IST IN S_WADAT
           AND
           B1~CHARG NE ''

Any idea??

Regards,

Kit

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • author's profile photo Prashant Patil
    Prashant Patil
    Posted on Dec 04, 2007 at 07:14 AM
    0

    Hi,

    Avoid join and Use FOR ALL ENTRIES

    Refer this code

    &----

    *& Form SUB_READ_VBRK

    &----

    • text

    ----

    FORM sub_read_vbrk.

    SELECT vbeln

    rplnr

    bukrs

    FROM vbrk

    INTO TABLE it_vbrk

    WHERE vbeln IN s_vbeln

    AND rplnr NE ' '.

    IF sy-subrc EQ 0.

    SORT it_vbrk BY rplnr.

    ENDIF.

    ENDFORM. " SUB_READ_VBRK

    &----

    *& Form SUB_READ_FPLTC

    &----

    • text

    ----

    FORM sub_read_fpltc.

    IF NOT it_vbrk[] IS INITIAL.

    SELECT fplnr

    fpltr

    ccnum

    FROM fpltc

    INTO TABLE it_fpltc

    FOR ALL ENTRIES IN it_vbrk

    WHERE fplnr EQ it_vbrk-rplnr

    AND ccins EQ 'GIFC'.

    IF sy-subrc EQ 0.

    SORT it_fpltc BY fplnr.

    ENDIF.

    ENDIF.

    ENDFORM. " SUB_READ_FPLTC

    Regards,

    Prashant

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member
      Dec 04, 2007 at 07:21 AM

      Thanks for your reply.

      I had tried the below, but the performance not improved:

       *LIKP
 SELECT  * FROM LIKP
 INTO TABLE I_DELIVERY
 WHERE
 LFART = 'EL'
 AND
 WADAT_IST IN S_WADAT.
 AND
 (WHERE_TAB).

 * LIPS
 SELECT *
 FROM LIPS INTO  TABLE I_DELIVERY_INFO
 FOR ALL ENTRIES IN I_DELIVERY
 WHERE
 VBELN = I_DELIVERY-VBELN
 AND
 CHARG NE ''.
 
 *MCHA
  SELECT *
  FROM MCHA INTO  TABLE I_BATCH
  FOR ALL ENTRIES IN I_DELIVERY_INFO
  WHERE
  MATNR = I_DELIVERY_INFO-MATNR
  AND
  WERKS = I_DELIVERY_INFO-WERKS
  AND
  CHARG = I_DELIVERY_INFO-CHARG.

  • author's profile photo Former Member
    Former Member
    Posted on Dec 04, 2007 at 07:24 AM
    0

    Hi Kit,

    Use for all entries option and make use of all possible key fields in where clause also before selecting next entries for all entries ,check INITIAL for first internal table.

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member
      Dec 04, 2007 at 07:29 AM

      Hi Hermant,

      As you can see, i had put the following key value the 2 and third query.

      LIPS: vbeln , posnr(i can't provide this field when when join the table)

      MCHA: MATNR, WERKS,CHARG

      Also, the dataset is not empty.

      Regards,

      Kit

  • author's profile photo Former Member
    Former Member
    Posted on Dec 04, 2007 at 07:35 AM
    0

    Hi Kit,

    Declare internal tables seperately and do as below

    SELECT VBELN WADAT_IST BOLNR LFIMG BTGEW FROM LIKP INTO TABLE IT_LIKP WHERE WADAT_IST IN S_WADAT AND LFART = 'EL'.
IF SY-SUBRC = 0.
 SELECT VBELN MATNR WERKS CHARG FROM LIPS INTO TABLE IT_LIPS FOR ALL ENTRIES IN IT_LIKP WHERE VBELN = IT_LIKP-VBELN.
 IF SY-SUBRC = 0.
  SELECT LIFNR WERKS CHARG FROM MCHA INTO TABLE IT_MCHA FOR ALL ENTRIES IN IT_LIPS WHERE WERKS = IT_LIPS-WERKS AND CHARG = IT_CHARG.
 ENDIF.
ENDIF.

DELETE IT_LIPS WHERE CHARG = ' '.

LOOP AT IT_LIKP INTO WA_LIKP.
 MOVE DATA TO FINAL ITAB FROM WA_LIKP.
 READ TABLE IT_LIPS INTO WA_LIPS WITH KEY VBELN = WA_LIKP-VBELN.
 IF SY-SUBRC = 0.
  MOVE DATA TO FINAL ITAB FROM WA_LIPS.
 ENDIF.
 READ TABLE IT_MCHA INTO WA_MCHA WITH KEY WERKS = WA_LIPS-WERKS CHARG = WA_LIPS-CHARG.
 IF SY-SUBRC = 0.
  MOVE DATA TO FINAL ITAB FROM WA_MCHA.
 ENDIF.
ENDLOOP.

    Regards,

    Satish

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.