topic Re: Performance with select Statement in Application Development and Automation Discussions

Performance with select Statement

Former Member — Thu, 15 Jan 2009 10:26:28 GMT

Hello Experts,

The following Select statement is taking a long time.

SELECT A_{LGNUM A}BETYP A_{BENUM A}TANUM

B_{VLTYP B}VLPLA B_{MATNR B}CHARG B~VISTM

B_{VSOLM B}VSOLA B_{MEINS B}ALTME B_{UMREZ B}UMREN

B_{NLTYP B}NLPLA B_{NLENR B}MAKTX

B_{RLTYP B}RLPLA B_{TAPOS A}BDATU A_{VBELN B}WERKS A~REFNR

A_{DRUKZ B}NISTM B_{NISTA B}NDIFA B_{WDATU B}VFDAT B~PQUIT

C_{RAUBE2 C}TEMPB2 A_{BENUM B}LGORT

APPENDING TABLE I_WM_TO

FROM LTAK AS A

INNER JOIN LTAP AS B

ON A_{LGNUM EQ B}LGNUM

AND A_{TANUM EQ B}TANUM

INNER JOIN MARC AS C

ON B_{MATNR EQ C}MATNR

AND B_{WERKS EQ C}WERKS

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 A_{LGNUM A}BETYP A_{BENUM A}TANUM

B_{VLTYP B}VLPLA B_{MATNR B}CHARG B~VISTM

B_{VSOLM B}VSOLA B_{MEINS B}ALTME B_{UMREZ B}UMREN

B_{NLTYP B}NLPLA B_{NLENR B}MAKTX

B_{RLTYP B}RLPLA B_{TAPOS A}BDATU A_{VBELN B}WERKS A~REFNR

A_{DRUKZ B}NISTM B_{NISTA B}NDIFA B_{WDATU B}VFDAT B~PQUIT

A_{BENUM B}LGORT

APPENDING TABLE I_WM_TO

FROM LTAK AS A

INNER JOIN LTAP AS B

ON A_{LGNUM EQ B}LGNUM

AND A_{TANUM EQ B}TANUM

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

Re: Performance with select Statement

Former Member — Thu, 15 Jan 2009 10:30:06 GMT

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

lot of where condition it also decrease performance.

Re: Performance with select Statement

Former Member — Thu, 15 Jan 2009 10:33:16 GMT

Hi,

Try to avoid JOIN in select statement.

Thanks.

Re: Performance with select Statement

Former Member — Thu, 15 Jan 2009 10:50:51 GMT

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 :

Regards,

Radhika.

Re: Performance with select Statement

ThomasZloch — Thu, 15 Jan 2009 11:03:24 GMT

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

Re: Performance with select Statement

Former Member — Thu, 15 Jan 2009 11:16:35 GMT

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

Re: Performance with select Statement

Former Member — Thu, 15 Jan 2009 11:22:40 GMT

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 A_{LGNUM A}BETYP A_{BENUM A}TANUM

B_{VLTYP B}VLPLA B_{MATNR B}CHARG B~VISTM

B_{VSOLM B}VSOLA B_{MEINS B}ALTME B_{UMREZ B}UMREN

B_{NLTYP B}NLPLA B_{NLENR B}MAKTX

B_{RLTYP B}RLPLA B_{TAPOS A}BDATU A_{VBELN B}WERKS A~REFNR

A_{DRUKZ B}NISTM B_{NISTA B}NDIFA B_{WDATU B}VFDAT B~PQUIT

A_{BENUM B}LGORT

APPENDING TABLE I_WM_TO

FROM LTAK AS A

INNER JOIN LTAP AS B

ON A_{LGNUM EQ B}LGNUM

AND A_{TANUM EQ B}TANUM

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

Re: Performance with select Statement

ThomasZloch — Thu, 15 Jan 2009 11:44:08 GMT

> 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

Re: Performance with select Statement

Former Member — Thu, 15 Jan 2009 12:02:11 GMT

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

Re: Performance with select Statement

Former Member — Fri, 16 Jan 2009 07:15:21 GMT

Thanks a lot Guys....:-)