topic Re: Performance issue while fetching AFRU Table in Application Development and Automation Discussions

Performance issue while fetching AFRU Table

vijay_kumar134 — Wed, 27 Mar 2013 11:54:46 GMT

Hi,

The below query's are taking more time for execution, please guide me for performance improvement:

if git_mseg_conv is not initial.

open cursor l_cl for

select ltxa1 rueck rmzhl
from afru

for all entries in git_mseg_conv

where ltxa1 = git_mseg_conv-charg and

werks = p_werks

%_hints oracle 'INDEX("AFRU" "AFRU~MS2")'.

if l_cl is not initial.

fetch next cursor l_cl into table git_afru_cast.

close cursor l_cl.

endif.

----
if not git_aufk is initial.
    select rueck rmzhl ltxa1 aufnr
            from afru
            into table git_afru
            for all entries in git_aufk
            where aufnr = git_aufk-aufnr and
                  ltxa1 in s_charg.

endif.
delete git_afru where ltxa1 eq ' '.

Thanks.

Viji.

Re: Performance issue while fetching AFRU Table

guilherme_frisoni — Wed, 27 Mar 2013 13:23:46 GMT

Hi Vijay,

it seems that if you don't have any ordem in git_aufk you need to get Order Confirmation for a batch in a whole plant. It mens you are need to search for all entries in afru to check the batch.

You can certainly create an index in AFRU-CHARG, but maybe you should try another approach.

You can try like this:

Fetch AFPO by CHARG. (maybe create an index by charg)

Fetch AFKO by WERKS with AFPO entries found (AUFNR)

Fetch AFVC by AUFPL with AFKO entries found (AUFPL)

Fetch AFRU by RUECK with AFVC entries found (RUECK)

Certainly are other options, you just need to find the one with fewer results in first selections.

Regards,

Frisoni

Re: Performance issue while fetching AFRU Table

vijay_kumar134 — Wed, 27 Mar 2013 13:39:49 GMT

thanks for your reply Frisoni .

Am not understand your alternative way you suggested.

Thanks in Advance.

Re: Performance issue while fetching AFRU Table

guilherme_frisoni — Wed, 27 Mar 2013 13:56:33 GMT

Ok, let's try another explanation.

You are selection AFRU by LTXA1 = CHARG.

Probably in any point AFRU-LTXA1 is filled with CHARG. But LTXA1 is a text field without index, and a search by it can be very bad.

So, let's try to get this charg in another way, and then select AFRU by it's index field RUECK.

We can start selection all orders that have CHARG meeting our conditions.

Select *

from AFPO

into IT_AFPO

for all entries in git_mseg_conv

where CHARG = git_mseg_conv-charg.

" For now we have all orders with your batchs (CHARG).

Select *

from AFKO

into IT_AFKO

for all entries in IT_AFPO

where AUFNR = IT_AFPO-AUFNR and

WERKS = p_werks.
" Now we all orders filtered by werks too

Select *

from afvc

into IT_AFVC

for all entries in IT_AFPO

where AUFPL = IT_AFPO-AUFPL.

" Now we have all operations that match CHARG and WERKS parameters

Select *

from AFRU

into IT_AFRU

for all entries in IT_AFVC

where RUECK = IT_AFVC-RUECK.

" Now, we should get the same results you need. All Confirmations that match initial CHARG and WERKS.

But in this scenario, we used most key fields, and all search have been doing with few entries each time we go forward.

Hope it helps,

Frisoni

Re: Performance issue while fetching AFRU Table

vijay_kumar134 — Wed, 27 Mar 2013 14:36:55 GMT

I will try this way and let you know.

Anyways thank you for your effort.

Re: Performance issue while fetching AFRU Table

vijay_kumar134 — Wed, 27 Mar 2013 16:15:27 GMT

Frisoni,

Using the below query am getting error like: "When you use the addition "FOR ALL ENTRIES IN itab", the fields "CHARG" and "GIT_MSEG_CONV-CHARG" must have the same type and the same length."

I think CHARG.- is 10 But LTXA1 is 40???

SELECT charg

FROM afpo

INTO TABLE git_afpo

FOR ALL ENTRIES IN git_mseg_conv

WHERE charg = git_mseg_conv-charg.

Re: Performance issue while fetching AFRU Table

vijay_kumar134 — Wed, 27 Mar 2013 16:15:35 GMT

Frisoni,

Using the below query am getting error like: "When you use the addition "FOR ALL ENTRIES IN itab", the fields "CHARG" and "GIT_MSEG_CONV-CHARG" must have the same type and the same length."

I think CHARG.- is 10 But LTXA1 is 40???

SELECT charg

FROM afpo

INTO TABLE git_afpo

FOR ALL ENTRIES IN git_mseg_conv

WHERE charg = git_mseg_conv-charg.

Re: Performance issue while fetching AFRU Table

vijay_kumar134 — Wed, 27 Mar 2013 16:17:19 GMT

Frisoni,

Using the below query am getting error like: "When you use the addition "FOR ALL ENTRIES IN itab", the fields "CHARG" and "GIT_MSEG_CONV-CHARG" must have the same type and the same length."

I think CHARG.- is 10 But LTXA1 is 40???

SELECT charg

FROM afpo

INTO TABLE git_afpo

FOR ALL ENTRIES IN git_mseg_conv

WHERE charg = git_mseg_conv-charg.

SELECT charg

FROM afpo

INTO TABLE git_afpo

FOR ALL ENTRIES IN git_mseg_conv

WHERE charg = git_mseg_conv-charg.

Re: Performance issue while fetching AFRU Table

guilherme_frisoni — Wed, 27 Mar 2013 16:23:25 GMT

Where do you get GIT_MSEG_CONV-CHARG? If it comes from MSEG its type is already CHAR10. Try to change GIT_MSEG_CONV-CHARG type.

Frisoni