topic Re: Performance Problem in Querry in Application Development and Automation Discussions

Performance Problem in Querry

Former Member — Thu, 08 May 2008 09:06:08 GMT

Hi All,

I am getting a time out in this following piece of code.

FORM get_matnr.

FIELD-SYMBOLS: <fl_marc> TYPE lty_marc. "Inserted by CTIWARS

SELECT * INTO CORRESPONDING FIELDS OF TABLE lt_mat

FROM pgmi

WHERE prgrp IN s_prgrp

AND werks IN s_werks.

CLEAR: zlines.

DESCRIBE TABLE lt_mat LINES zlines.

IF zlines NE 0.

lt_mat4[] = lt_mat[].

SORT lt_mat4 BY nrmit wemit.

DELETE ADJACENT DUPLICATES FROM lt_mat4 COMPARING nrmit wemit.

*SELECT prgrp*

*werks*

*nrmit*

*wemit*

*INTO TABLE lt_mat3 package size 1000*

*FROM pgmi*

*FOR ALL ENTRIES IN lt_mat4*

*WHERE prgrp = lt_mat4-nrmit*

*AND werks = lt_mat4-wemit.*

CLEAR lv_mat2.

LOOP AT lt_mat3 into lv_mat3.

lv_mat2-prgrp = lv_mat3-prgrp.

lv_mat2-werks = lv_mat3-werks.

lv_mat2-nrmit = lv_mat3-nrmit.

lv_mat2-wemit = lv_mat3-wemit.

APPEND lv_mat2 TO lt_mat2.

CLEAR: lv_mat2,

lv_mat3.

ENDLOOP.

ENDSELECT.

The bold or commented querry is giving the error.

In internal table lt_mat4 there are 57000 rows.

But an earlier querry(pasted below) there is even more data.

SELECT * INTO CORRESPONDING FIELDS OF TABLE lt_mat

FROM pgmi

WHERE prgrp IN s_prgrp

AND werks IN s_werks.

Can anyone point out why is this happening.

Thanks in advance,

Saket.

Re: Performance Problem in Querry

Former Member — Thu, 08 May 2008 09:46:43 GMT

SELECT * INTO CORRESPONDING FIELDS OF TABLE lt_mat

FROM pgmi

WHERE prgrp IN s_prgrp

AND werks IN s_werks.

why select-end select. populate the data into it_pgmi and then do the necessary processing like select, loops.

this is why the select is taking more time..

regards,

madhumitha

Re: Performance Problem in Querry

Former Member — Thu, 08 May 2008 10:14:08 GMT

Hi,

Try to avoid CORRESPONDING FIELDS OF clause.

Re: Performance Problem in Querry

Former Member — Thu, 08 May 2008 10:22:15 GMT

Thanks for reply but i am not getting error in the querry u r pointing to.

But in the following querry:

SELECT PRGRP

WERKS

NRMIT

WEMIT

DATUM

VSNDA

INTO TABLE LT_MAT3 PACKAGE SIZE 1000

FROM PGMI

FOR ALL ENTRIES IN LT_MAT4

WHERE PRGRP = LT_MAT4-NRMIT

AND WERKS = LT_MAT4-WEMIT.

AND NRMIT IN TR_NRMIT.
AND WEMIT IN S_WERKS.

CLEAR LV_MAT2.

LOOP AT LT_MAT3 INTO LV_MAT3.

LV_MAT2-PRGRP = LV_MAT3-PRGRP.

LV_MAT2-WERKS = LV_MAT3-WERKS.

LV_MAT2-NRMIT = LV_MAT3-NRMIT.

LV_MAT2-WEMIT = LV_MAT3-WEMIT.

APPEND LV_MAT2 TO LT_MAT2.

CLEAR: LV_MAT2,

LV_MAT3.

ENDLOOP.

ENDSELECT.

So please look in this and suugest.

Re: Performance Problem in Querry

Former Member — Thu, 08 May 2008 10:35:37 GMT

Hi,

Your query is like following query.

SELECT * FROM SPFLI

INTO TABLE T_SPFLI

WHERE CITYFROM = 'FRANKFURT'

AND CITYTO = 'NEW YORK'.

SELECT * FROM SFLIGHT AS F

INTO SFLIGHT_WA

FOR ALL ENTRIES IN T_SPFLI

WHERE SEATSOCC < F~SEATSMAX

AND CARRID = T_SPFLI-CARRID

AND CONNID = T_SPFLI-CONNID

AND FLDATE BETWEEN '19990101' AND '19990331'.

ENDSELECT.

Above query written in different way which has better performance is

SELECT * FROM SFLIGHT AS F INTO SFLIGHT_WA

WHERE SEATSOCC < F~SEATSMAX

AND EXISTS ( SELECT * FROM SPFLI

WHERE CARRID = F~CARRID

AND CONNID = F~CONNID

AND CITYFROM = 'FRANKFURT'

AND CITYTO = 'NEW YORK' )

AND FLDATE BETWEEN '19990101' AND '19990331'.

ENDSELECT

This way of writing query will improve performance.

Re: Performance Problem in Querry

valter_oliveira — Thu, 08 May 2008 16:18:00 GMT

Hello Saket.

First of all, remember that if you are getting a time out, it means that all program should be shaped for getting faster (not only the point where it stops).

Now, some peace of advices:

1) Replace

SELECT * INTO CORRESPONDING FIELDS OF TABLE lt_mat

FROM pgmi

WHERE prgrp IN s_prgrp

AND werks IN s_werks.

...

ENDSELECT.

SELECT a b c ... INTO TABLE lt_mat

FROM pgmi

WHERE prgrp IN s_prgrp

AND werks IN s_werks.

(lt_mat should be of a type with the fields you need)

Avoid to use a FOR ALL ENTRIES query inside a SELECT/ENDSELECT

2) Considering the big amount of the internal tables, replace:

CLEAR: zlines.

DESCRIBE TABLE lt_mat LINES zlines

use:

READ TABLE lt_mat TRANSPORTING NO FIELDS INDEX 1.

3) Finally, don't use for all entries with that kind of entries (50.000). Split lt_mat4 in 3 or 4 differente selects.

As I said, use what you think is best ... test it ... use ST05 to analyse.

Best regards.

Valter Oliveira.

Re: Performance Problem in Querry

Former Member — Thu, 24 Mar 2011 11:27:28 GMT

Thanks for all inputs