topic Re: select statement in Application Development and Automation Discussions

select statement

Former Member — Wed, 19 Dec 2007 15:10:15 GMT

Hi experts,

I have a selection statement which is taking 80% of the execution time.

SELECT lgnum tanum tapos posnr matnr vltyp

nistm vbeln

INTO TABLE t_ltap

FROM ltap

FOR ALL ENTRIES IN t_matnr

WHERE lgnum IN s_lgnum

AND pquit = 'X'

AND matnr = t_matnr-matnr

AND werks IN s_werks

AND qdatu IN s_qdatu

AND lgort IN s_lgort.

I have re written this select statement several time making sure that all the indexes are used nad hte best possible performance way. But still am not able to enhance the performance as there are some around 30 million records in the ltap table. If you can suggest me any further enhancement it would be great as this report is timing out and 80% of the exec time is spending on this statement. Thanks in advance.

anirvesh.

Re: select statement

Former Member — Wed, 19 Dec 2007 15:17:20 GMT

Hi Anirvesh,

Chk the first 2 blogs in this

/people/siegfried.boes/blog

I hope you have used this, just to confirm

IF NOT t_matnr[] is initial.

SELECT....

ENDIF.

Re: select statement

Former Member — Wed, 19 Dec 2007 15:17:20 GMT

Is s_lgnum typically empty or have a very large range?

Rob

Re: select statement

Former Member — Wed, 19 Dec 2007 15:32:13 GMT

Hi Rob,

s_lgnum is neither empty nor does have large number of values. Our client has around 10-15 warehouses.

Re: select statement

Former Member — Wed, 19 Dec 2007 15:38:58 GMT

Then, as Chandrasekhar has suggested, make sure that the internal table is not empty.

If it is not empty, try this:

DATA: BEGIN OF matnr_sel OCCURS 0,
        matnr TYPE ltap-matnr,
      END   OF matnr_sel.

LOOP AT t_matnr.
  MOVE t_matnr-matnr TO matnr_sel-matnr.
  APPEND matnr_sel.
ENDLOOP.

SORT matnr_sel BY matnr.
DELETE ADJACENT DUPLICATES FROM matnr_sel.

SELECT lgnum tanum tapos posnr matnr vltyp
      nistm vbeln
INTO TABLE t_ltap
FROM ltap
FOR ALL ENTRIES IN matnr_sel    <====
  WHERE lgnum IN s_lgnum
  AND   pquit  = 'X'
  AND   matnr  = matnr_sel-matnr    <====
  AND   werks IN s_werks
  AND   qdatu IN s_qdatu
  AND   lgort IN s_lgort.

Rob

Edited by: Rob Burbank on Dec 19, 2007 10:43 AM

Re: select statement

Former Member — Wed, 19 Dec 2007 15:41:46 GMT

I did not copy that piece of code but I am already checking on the internal table. Thanks.

anirvesh

Re: select statement

Former Member — Wed, 19 Dec 2007 18:08:55 GMT

You need to make sure that the FOR ALL ENTRIES table has unique material numbers. Take a look at the code below.

DATA t_matnr_tmp LIKE TABLE OF t_matnr.

IF NOT t_matnr[] IS INITIAL.

t_matnr_tmp[] = t_matnr[].

SORT t_matnr_tmp BY matnr.

DELETE ADJACENT DUPLICATES FROM t_matnr_tmp COMPARING matnr.

SELECT lgnum

tanum

tapos

posnr

matnr

vltyp

nistm

vbeln

FROM ltap

INTO TABLE t_ltap

FOR ALL ENTRIES IN t_matnr_tmp

WHERE lgnum IN s_lgnum

AND matnr EQ t_matnr_tmp-matnr

AND werks IN s_werks

AND pquit EQ 'X'

AND qdatu IN s_qdatu

AND lgort IN s_lgort.

[/code]

Re: select statement

Former Member — Wed, 19 Dec 2007 18:22:10 GMT

Hi Rob and Mark,

The t_matnr I am using already has unique materials. That was one of the changes I made to the original version. The internal table t_matnr has unique materials. Any furher suggestions please. Thanks.

regards,

anirvers.

Re: select statement

Former Member — Wed, 19 Dec 2007 18:31:49 GMT

Make sure it is sorted by matnr.

Rob

Re: select statement

Former Member — Wed, 19 Dec 2007 18:33:24 GMT

Rob,

Yes t_matnr is sorted by matnr and ltap table also has an index on matnr.

anirvesh.

Re: select statement

Former Member — Wed, 19 Dec 2007 18:38:19 GMT

OK - this is all I can think of:

SELECT-OPTIONS s_lgnum FOR ltap-lgnum.

RANGES: r_lgnum FOR ltap-lgnum.

* Ensure tresting for equality on lgnum
r_lgnum-option = 'EQ'.
r_lgnum-sign   = 'I'.

SELECT lgnum FROM t300
  INTO r_lgnum-low
  WHERE lgnum IN s_lgnum.
  APPEND r_lgnum.
ENDSELECT.

SORT matnr_sel BY matnr.
DELETE ADJACENT DUPLICATES FROM matnr_sel.

SELECT lgnum tanum tapos posnr matnr vltyp
      nistm vbeln
INTO TABLE t_ltap
FROM ltap
FOR ALL ENTRIES IN t_matnr
  WHERE lgnum IN r_lgnum      <====
  AND   pquit  = 'X'
  AND   matnr  = t_matnr-matnr
  AND   werks IN s_werks
  AND   qdatu IN s_qdatu
  AND   lgort IN s_lgort.

Rob

Re: select statement

Former Member — Wed, 19 Dec 2007 18:43:47 GMT

Hi Rob,

Thanks for your time. I have used this one too the parameter in my sel stmt s_lgnum is filled in the same method. I need to thank you for one more thing, that is the blog on how to avoid nested loops. I have used the indexed loop method. Thanks a lot for all the time and help.

regards,

anirvesh.

Re: select statement

Former Member — Wed, 19 Dec 2007 18:47:16 GMT

Glad to help and glad you read the blog.

One last thing - have you used a performance trace (ST05) and looked at the Explain to make sure it is using the correct index?

Rob

Re: select statement

Former Member — Wed, 19 Dec 2007 19:03:19 GMT

Hi Rob,

I have checked in st05 and the table is using the right index. Thanks for the tip I didnt knew that before.

anirvesh