Performance issue while fetching AFRU Table

[vijay_kumar134]

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.

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.

[guilherme_frisoni]

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

[vijay_kumar134]

thanks for your reply Frisoni .

Am not understand your alternative way you suggested.

Thanks in Advance.

[guilherme_frisoni]

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

[vijay_kumar134]

I will try this way and let you know.

Anyways  thank you for your effort.

[vijay_kumar134]

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.

[vijay_kumar134]

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.

[vijay_kumar134]

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.

[guilherme_frisoni]

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