Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Performance Problem in Querry

Former Member
0 Kudos

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.

1 ACCEPTED SOLUTION

Former Member
0 Kudos

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

6 REPLIES 6

Former Member
0 Kudos

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

Former Member
0 Kudos

Hi,

Try to avoid CORRESPONDING FIELDS OF clause.

0 Kudos

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.

0 Kudos

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.

valter_oliveira
Active Contributor
0 Kudos

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.

by

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.

Former Member
0 Kudos

Thanks for all inputs