topic Re: Query takes so much time in Application Development and Automation Discussions

Query takes so much time

Former Member — Wed, 30 Jan 2008 10:43:27 GMT

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

Re: Query takes so much time

former_member386202 — Wed, 30 Jan 2008 10:46:47 GMT

Hi,

Use Into table instead of INTO CORRESPONDING FIELDS OF TABLE

and try to pass apll the keys in where condition.

Regards,

Prashant

Re: Query takes so much time

Former Member — Wed, 30 Jan 2008 10:52:42 GMT

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

Re: Query takes so much time

Former Member — Wed, 30 Jan 2008 10:55:08 GMT

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

Re: Query takes so much time

Former Member — Wed, 30 Jan 2008 10:56:29 GMT

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

Re: Query takes so much time

Former Member — Wed, 30 Jan 2008 11:01:27 GMT

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

Pls help me out.

Re: Query takes so much time

Former Member — Wed, 30 Jan 2008 11:04:25 GMT

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

Re: Query takes so much time

Former Member — Wed, 30 Jan 2008 12:02:27 GMT

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.

Re: Query takes so much time

Former Member — Wed, 30 Jan 2008 12:07:11 GMT

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

Re: Query takes so much time

Former Member — Wed, 30 Jan 2008 12:18:37 GMT

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.

Re: Query takes so much time

Former Member — Wed, 30 Jan 2008 12:32:29 GMT

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.

Re: Query takes so much time

Former Member — Wed, 30 Jan 2008 12:36:41 GMT

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

Re: Query takes so much time

Former Member — Wed, 30 Jan 2008 12:39:08 GMT

Hi,

write like:

if i_afpo[] is not initial.

endif.

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

Regards,

Renjith Michael.

Re: Query takes so much time

Former Member — Thu, 31 Jan 2008 03:36:11 GMT

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

but still its not working fine.

Re: Query takes so much time

Former Member — Thu, 31 Jan 2008 03:38:19 GMT

These are the steps to improve the performance

there must be some problem with your code

Regards

Shiva

Re: Query takes so much time

Former Member — Thu, 31 Jan 2008 03:42:48 GMT

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.