01-15-2009 10:26 AM
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
01-15-2009 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
01-15-2009 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.
01-15-2009 10:33 AM
Hi,
Try to avoid JOIN in select statement.
Thanks.
01-15-2009 10:50 AM
01-15-2009 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
01-15-2009 11:22 AM
Thanks Thomas for your time.
Can I look for the option of removing some of where conditions in join statement and delete after this for these conditions as suggested by Anurag.
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.
DELETE I_WM_TO WHERE TANUM NOT IN TORDR
OR VLTYP NOT IN SSTYP
OR NLPLA NOT IN DBIN
OR NLTYP NOT IN DSTYP
OR MATNR NOT IN MATNR
OR CHARG NOT IN CHARG
OR VLPLA NOT IN SBIN
OR ( PQUIT NE P_FPQUIT
OR PQUIT NE 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.
Thanks & Regards,
Nitin
01-15-2009 11:44 AM
> 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
01-15-2009 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
01-15-2009 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
01-16-2009 7:15 AM