Query takes so much time

[Former Member]

Hi Experts

IF i_afpo[] IS not INITIAL.

SELECT AUFNR

BUDAT

ISDD

WERKS

SMENG

ARBID

RUECK

RMZHL

VORNR

LTXA1

GRUND

STOKZ

STZHL

AENAM

AUERU

EXNAM

AUFPL

APLZL

ISM01

ILE01

ANZMA

RMNGA

XMNGA

extid

stzhl

GMEIN

LMNGA FROM AFRU

INTO CORRESPONDING FIELDS OF TABLE i_AFRU

FOR ALL ENTRIES IN i_afpo

WHERE aufnr EQ i_afpo-aufnr AND

BUDAT IN ZDAT AND

ISDD IN ZEDAT AND

GRUND IN ZLINE AND

WERKS IN ZPL.

ENDIF.

I have used the above query, but its taking so much time for execute.

i_afpo contains nearly 1300 records, i think this forall entry might be the cause for this problem.

how to solve this problem, advice me on this.

Thanks in advance.

Regards

Rajaram

[Former Member]

Hi Experts

IF i_afpo[] IS not INITIAL.

SELECT AUFNR

BUDAT

ISDD

WERKS

SMENG

ARBID

RUECK

RMZHL

VORNR

LTXA1

GRUND

STOKZ

STZHL

AENAM

AUERU

EXNAM

AUFPL

APLZL

ISM01

ILE01

ANZMA

RMNGA

XMNGA

extid

stzhl

GMEIN

LMNGA FROM AFRU

INTO CORRESPONDING FIELDS OF TABLE i_AFRU

FOR ALL ENTRIES IN i_afpo

WHERE aufnr EQ i_afpo-aufnr AND

BUDAT IN ZDAT AND

ISDD IN ZEDAT AND

GRUND IN ZLINE AND

WERKS IN ZPL.

ENDIF.

I have used the above query, but its taking so much time for execute.

i_afpo contains nearly 1300 records, i think this forall entry might be the cause for this problem.

how to solve this problem, advice me on this.

Thanks in advance.

Regards

Rajaram

[former_member386202]

Hi,

Use Into table instead of INTO CORRESPONDING FIELDS OF TABLE

and try to pass apll the keys in where condition.

Regards,

Prashant

[Former Member]

How to use INTO instead of INTO CORRESPONDING FIELDS in select query.

Pls help me out.

[Former Member]

Hi,

Declare the ITAB as same as your are using in SELECT statament.

select aufnr

into table it_afpo

.........................

If the position vary then it will generate dump

make sure that the sequence is same as the itab.

Regards

Shiva

Edited by: Shivakumar Hosaganiger on Jan 30, 2008 4:36 PM

[Former Member]

Hi now, i have changed the query as follows

SELECT * FROM AFRU

INTO i_AFRU

FOR ALL ENTRIES IN i_afpo

WHERE aufnr EQ i_afpo-aufnr AND

BUDAT IN ZDAT AND

ISDD IN ZEDAT AND

GRUND IN ZLINE AND

WERKS IN ZPL.

endselect.

but still its performance is very poor, what can i do.

[Former Member]

hi

data : begin of it_afru,

AUFNR

BUDAT

ISDD

WERKS

SMENG

ARBID

RUECK

RMZHL

VORNR

LTXA1

GRUND

STOKZ

STZHL

AENAM

AUERU

EXNAM

AUFPL

APLZL

ISM01

ILE01

ANZMA

RMNGA

XMNGA

extid

stzhl

GMEIN

LMNGA

end of it_afpo

data : li_afpo type standard table afpo.

select * into

table i_afpo

from afpo

where ...........

li_afpo[]= i_afpo[].

sort li_afpo by aufnr.

delete adjacent duplicates from li_afpo comparing aufnr.

SELECT AUFNR

BUDAT

ISDD

WERKS

SMENG

ARBID

RUECK

RMZHL

VORNR

LTXA1

GRUND

STOKZ

STZHL

AENAM

AUERU

EXNAM

AUFPL

APLZL

ISM01

ILE01

ANZMA

RMNGA

XMNGA

extid

stzhl

GMEIN

LMNGA

FROM AFRU

INTO table i_AFRU

FOR ALL ENTRIES IN li_afpo

WHERE aufnr EQ i_afpo-aufnr AND

BUDAT IN ZDAT AND

ISDD IN ZEDAT AND

GRUND IN ZLINE AND

WERKS IN ZPL.

Modify the code as i m am not any server to test

and make the changes accordingly

Regards

Shiva

[Former Member]

DATA:i_afru LIKE afru OCCURS 10 WITH HEADER LINE.

this is my i_afru internal table declarations.

so can it be the reason for the performance.

bcoz, the order am storing in internal table in the query is differ from the internal table order, is it.

[Former Member]

DATA: BEGIN OF i_AFRU OCCURS 0,

AUFNR LIKE AFRU-AUFNR,

BUDAT LIKE AFRU-BUDAT,

ISDD LIKE AFRU-ISDD,

WERKS LIKE AFRU-WERKS,

SMENG LIKE AFRU-SMENG,

ARBID LIKE AFRU-ARBID,

RUECK LIKE AFRU-RUECK,

RMZHL LIKE AFRU-RMZHL,

VORNR LIKE AFRU-VORNR,

LTXA1 LIKE AFRU-LTXA1,

GRUND LIKE AFRU-GRUND,

STOKZ LIKE AFRU-STOKZ,

STZHL LIKE AFRU-STZHL,

AENAM LIKE AFRU-AENAM,

AUERU LIKE AFRU-AUERU,

EXNAM LIKE AFRU-EXNAM,

AUFPL LIKE AFRU-AUFPL,

APLZL LIKE AFRU-APLZL,

ISM01 LIKE AFRU-ISM01,

ILE01 LIKE AFRU-ILE01,

ANZMA LIKE AFRU-ANZMA,

RMNGA LIKE AFRU-RMNGA,

XMNGA LIKE AFRU-XMNGA,

extid LIKE AFRU-extid,

stzhl1 LIKE AFRU-stzhl,

GMEIN LIKE AFRU-GMEIN,

LMNGA LIKE AFRU-LMNGA,

END OF I_AFRU.

and then

SELECT AUFNR

BUDAT

ISDD

WERKS

SMENG

ARBID

RUECK

RMZHL

VORNR

LTXA1

GRUND

STOKZ

STZHL

AENAM

AUERU

EXNAM

AUFPL

APLZL

ISM01

ILE01

ANZMA

RMNGA

XMNGA

extid

stzhl

GMEIN

LMNGA FROM AFRU

INTO TABLE i_AFRU

FOR ALL ENTRIES IN i_afpo

WHERE aufnr EQ i_afpo-aufnr AND

BUDAT IN ZDAT AND

ISDD IN ZEDAT AND

GRUND IN ZLINE AND

WERKS IN ZPL.

used but still its not working fine yar.

[Former Member]

delete the duplicate entreis from i_AFPo and move the same into one local ITAB

this will improve little bit of performance otherwise try using the join sometimes join may be effective

Regards

Shiva

[Former Member]

Hi,

write like:

if i_afpo[] is not initial.

<select query>

endif.

Then also in the select query try to specify all of the key fields.

Regards,

Renjith Michael.

[Former Member]

I have used IF i_afpo[] IS not INITIAL also yar.

but still its not working fine.

[Former Member]

These are the steps to improve the performance

there must be some problem with your code

Regards

Shiva

[Former Member]

This is my coding part, the i_afru declaration also the order as same as in the query. still what could be the problem.

IF i_afpo[] IS not INITIAL.

sort i_afpo by aufnr.

delete adjacent duplicates from i_afpo comparing aufnr.

SELECT AUFNR

BUDAT

ISDD

WERKS

SMENG

ARBID

RUECK

RMZHL

VORNR

LTXA1

GRUND

STOKZ

STZHL

AENAM

AUERU

EXNAM

AUFPL

APLZL

ISM01

ILE01

ANZMA

RMNGA

XMNGA

extid

stzhl

GMEIN

LMNGA FROM AFRU

INTO TABLE i_AFRU

FOR ALL ENTRIES IN i_afpo

WHERE aufnr EQ i_afpo-aufnr AND

BUDAT IN ZDAT AND

ISDD IN ZEDAT AND

GRUND IN ZLINE AND

WERKS IN ZPL.

ENDIF.

[Former Member]

Hi,

mention the fields as they appear in table

also remove the Ito corresponding

and check for multiple entries in I_AFPO, if yes then move table to a local and delete the adjacent duplicates from the local table and use it as for all entries

AFPO is having item numbers this will take more time as i said above remove the duplicate entries by moving into another ITAB

Regards

Shiva

[Former Member]

Hi,

Before writing select statement, sort i_afpo by aufnr and delete adjacent duplicates comparing aufnr. This will reduce the no. of records of i_afpo.

Try not to use Move Corresponding..

Regards,

Bindu

[Former Member]

Hi Raja,

Define your internal table i_afru in a sequence in which you have to retreive the fields from database table and avoid INTO CORRESPONDING FIELDS OF.

Regards

Sourabh Verma