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

Performance with select Statement

Hello Experts,

The following Select statement is taking a long time.

SELECT ALGNUM ABETYP ABENUM ATANUM

BVLTYP BVLPLA BMATNR BCHARG B~VISTM

BVSOLM BVSOLA BMEINS BALTME BUMREZ BUMREN

BNLTYP BNLPLA BNLENR BMAKTX

BRLTYP BRLPLA BTAPOS ABDATU AVBELN BWERKS A~REFNR

ADRUKZ BNISTM BNISTA BNDIFA BWDATU BVFDAT B~PQUIT

CRAUBE2 CTEMPB2 ABENUM BLGORT

APPENDING TABLE I_WM_TO

FROM LTAK AS A

INNER JOIN LTAP AS B

ON ALGNUM EQ BLGNUM

AND ATANUM EQ BTANUM

INNER JOIN MARC AS C

ON BMATNR EQ CMATNR

AND BWERKS EQ CWERKS

WHERE A~LGNUM = WHSE

AND A~BENUM IN BENUM

AND A~BETYP IN BETYP

AND B~TANUM IN TORDR

AND B~VLTYP IN SSTYP

AND B~NLPLA IN DBIN

AND B~NLTYP IN DSTYP

AND B~MATNR IN MATNR

AND B~CHARG IN CHARG

AND B~VLPLA IN SBIN

AND ( B~PQUIT = P_FPQUIT

OR B~PQUIT = P_TPQUIT ).

I tried the following statement also but didn't get much improvement.

SELECT ALGNUM ABETYP ABENUM ATANUM

BVLTYP BVLPLA BMATNR BCHARG B~VISTM

BVSOLM BVSOLA BMEINS BALTME BUMREZ BUMREN

BNLTYP BNLPLA BNLENR BMAKTX

BRLTYP BRLPLA BTAPOS ABDATU AVBELN BWERKS A~REFNR

ADRUKZ BNISTM BNISTA BNDIFA BWDATU BVFDAT B~PQUIT

ABENUM BLGORT

APPENDING TABLE I_WM_TO

FROM LTAK AS A

INNER JOIN LTAP AS B

ON ALGNUM EQ BLGNUM

AND ATANUM EQ BTANUM

WHERE A~LGNUM EQ WHSE

AND A~BENUM IN BENUM

AND A~BETYP IN BETYP

AND B~TANUM IN TORDR

AND B~VLTYP IN SSTYP

AND B~NLPLA IN DBIN

AND B~NLTYP IN DSTYP

AND B~MATNR IN MATNR

AND B~CHARG IN CHARG

AND B~VLPLA IN SBIN

AND ( B~PQUIT = P_FPQUIT

OR B~PQUIT = P_TPQUIT ).

TYPES : BEGIN OF it_marc,

MATNR LIKE MARC-MATNR,

WERKS LIKE MARC-WERKS,

RAUBE2 LIKE MARC-RAUBE2,

TEMPB2 LIKE MARC-TEMPB2,

END OF it_marc.

DATA : itab_marc TYPE TABLE OF it_marc INITIAL SIZE 0,

wa_marc LIKE LINE OF itab_marc.

SELECT MATNR WERKS RAUBE2 TEMPB2 FROM MARC INTO TABLE itab_marc

FOR ALL ENTRIES IN I_WM_TO where

MATNR EQ I_WM_TO-MATNR

AND WERKS EQ I_WM_TO-WERKS.

LOOP AT I_WM_TO.

READ TABLE itab_marc WITH KEY MATNR = I_WM_TO-MATNR WERKS = I_WM_TO-WERKS INTO wa_marc.

IF sy-subrc = 0.

I_WM_TO-RAUBE2 = wa_marc-RAUBE2.

I_WM_TO-TEMPB2 = wa_marc-TEMPB2.

MODIFY I_WM_TO.

ENDIF.

ENDLOOP.

Please suggest any better way to get the same data.

Thanks & Regards,

Nitin

Add a comment
10|10000 characters needed characters exceeded

Related questions

7 Answers

  • Best Answer
    Posted on Jan 15, 2009 at 12:02 PM

    Thomas is right.

    But it is actually even worse, there not conditions to be removed, because your condition are ranges, and they are probably not filled and actually not there.

    What you must do is adding conditions! You must fill something which is selective!

    Siegfried

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 15, 2009 at 10:30 AM

    just remove some where condition from select stament and delete outside . some time when we use

    lot of where condition it also decrease performance.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 15, 2009 at 10:33 AM

    Hi,

    Try to avoid JOIN in select statement.

    Thanks.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 15, 2009 at 10:50 AM

    Hi ,

    You can as well try with FOR ALL ENTRIES in this case.

    There are lot of condition involved in this JOIN statement.

    You can further refer this link for details :

    Inner Join Vs For all Entries

    Regards,

    Radhika.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jan 15, 2009 at 11:03 AM

    Your join conditions in the first code block look OK. You should not split the join, otherwise all selections could not be applied at once, and you're reading too much data from the DB.

    If there is a lot of entries in those tables for a given LGNUM and none of the additional select options is provided, then there is not much you can do about a long runtime.

    You can always run a ST05 trace to find out which access path is taken based of different selection criteria provided. E.g. try with a single MATNR, because there is an appropriate index on LTAP.

    Thomas

    Add a comment
    10|10000 characters needed characters exceeded

    • > Can I look for the option of removing some of where conditions in join statement and delete after this for these conditions

      This would mean reading data from the database and delete from memory afterwards. I would never recommend this. My approach would be like in your first code block, joining all three tables, and rather look if e.g. the user can be forced to enter as many and as restrictive selection options as possible from a functional standpoint, also analysing via ST05 as required.

      Thomas

  • author's profile photo Former Member
    Former Member
    Posted on Jan 15, 2009 at 11:16 AM

    field-symbols: <fs_i_wm_to> LIKE LINE OF I_WM_TO,

    <fs_itab_marc> LIKE LINE OF itab_marc.

    sort itab_marc by MATNR WERKS.

    LOOP at i_wm_to assigning <fs_i_wm_to>.

    READ TABLE itab_marc assigning <fs_itab_marc> WITH KEY MATNR = <fs_i_wm_to>-MATNR WERKS = <fs_i_wm_to>-WERKS binary search.

    IF sy-subrc = 0.

    <fs_i_wm_to>-RAUBE2 = <fs_itab_marc>-RAUBE2.

    <fs_i_wm_to>-TEMPB2 = <fs_itab_marc>-TEMPB2.

    ENDIF.

    will be faster.

    regards,

    Robert

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 16, 2009 at 07:15 AM

    Thanks a lot Guys....:-)

    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.