Problem while getting the base price for materials

[Former Member]

Hi All,

Right now we are getting base price from A004 &KONP tables.

Please see the code below.

problem is : We have around 2 lacs materials in production ,while hitting A004 table for all materials its taking around 2 days to get the values, since it is a Pooled table.

Is there any other way to get the base price for materials.

if not it_mvke is initial.

select kappl

kschl

vkorg

vtweg

matnr

datbi

datab

knumh

from a004

into table it_a004

for all entries in it_mvke

where kappl = c_v and

kschl = c_zmbp and

vkorg in rng_vkorg and

( vtweg in '30' or

'32' ) and

matnr = it_mvke-matnr and

datbi ge gv_datum.

if sy-subrc eq 0.

select knumh

kopos

kbetr

from konp

into table it_konp

for all entries in it_a004 where knumh = it_a004-knumh. endif.

<removed_by_moderator>

Thanks

Peddi

Edited by: Julius Bussche on Aug 29, 2008 12:41 PM

[valter_oliveira]

Hello.

I personally never use FOR ALL ENTRIES when the internal table is very big. It has a poor performance. Also, I would change your restriction of vtweg field and wouldn't get field that I have the value, like matnr ...

Try:

SELECT matnr vkorg vtweg FROM mvke ... WHERE vkorg in rng_vkorg AND vtweg in ('30','32').

  SELECT vkorg vtweg datbi datab knumh
    FROM a004
    APPENDING table it_a004
   WHERE kappl = c_v 
     AND kschl = c_zmbp 
     AND vkorg = mvke-vkorh
     AND vtweg in ('30','32') "OR vtweg = mvke-vtweg
     AND matnr = mvke-matnr 
     AND datbi ge gv_datum
     AND datab le gv_datum.

ENDSELECT. "mvke

READ TABLE it_a004 TRANSPORTING NO FIELDS INDEX 1.
IF sy_subrc EQ 0.
  select knumh kopos kbetr
    from konp
    into table it_konp for all entries in it_a004 
  where knumh = it_a004-knumh. endif.
ENDIF.

Regards,

Valter Oliveira.

[Former Member]

Hi Valter,

Thanks for your reply.

I used select statement inside loop also,but still its taking more time.

Is there any other table tog et the condition record number(KNUMH ) other than A004.

I have Application,Condition type,material number,sale organization,distribution channel.

thanks

peddi

[valter_oliveira]

Hi again.

How much records do you have? Since you have almost all the key of A004 is strange that you are taking so much time. Sometimes it's not good to have so much intermediary internal tables. Why not, just get the data once, like:

SELECT matnr FROM mvke ...

SELECT knumh FROM a004 UP TO 1 ROWS (because of date field)

WHERE ...

EXIT.

ENDSELECT.

SELECT kbetr FROM konp UP TO 1 ROWS

WHERE ...

EXIT.

ENDSELECT.

APPEND work area with matnr kbetr INTO itab (1st itab).

ENDSELECT.

This souldn't take more than 5 minutes ... and i'm considering some thowsand records ...

However there is an FM to calculate price I think ... I'll search and post here ...

Regards.

Valter Oliveira.

[Former Member]

Hi Valter,

I have around 2,00,000 records in MVKE internal table.

Date field also i am giving like greater than or equal to current date.

[Former Member]

Hi Peddi Reddy .,

You can Use KONH Table to get Condition Number instead if A004 .

Please let me know Your exact Requirement if you are not able to get data from KONH and KONP .

thanks

Sreenivas Reddy

[valter_oliveira]

Another idea. Go to A004 only once, like:

TYPES: BEGIN OF ty_a004,
         vkorg TYPE vkorg,
         vtweg TYPE vtweg,
         matnr TYPE matnr,
         knumh TYPE knumh,
       END OF ty_a004.

DATA: it_a004 TYPE HASHED TABLE OF ty_a004 WITH UNIQUE KEY vkorg vtweg matnr,
      wa_a004 TYPE ty_a004.

SELECT vkorg vtweg matnr knumh
  FROM a004
  INTO TABLE it_a004
 WHERE kappl = c_v
   AND kschl = c_kschl
   AND vkorg IN r_vkorg
   AND vtweg IN ('30','32')
   AND datbi GE gv_datum
   AND datab LE gv datum.

Then READ this table (hashed table).
SELECT vkorg vtweg matnr FROM mvke WHERE ...
  READ TABLE it_a004 INTO wa_a004 TRANSPORTING knumh
        WITH TABLE KEY vkorg = mvke-vkorg 
                  vtweg = mvke-vtweg 
                  matnr = mvke-matnr.
  IF sy-subrc EQ 0.
* KONP SELECT
  ENDIF.
ENDSELECT.

FREE it_a004.

Regards.

Valter Oliveira.

[Former Member]

Hi All,

I have tried A004 and KONP tables inside loop also

belwo is the loop we have ,in that we have 2,00,000 records.

loop at it_mvke into wa_mvke.

select knumh

from a004

into wa_a004

where kappl = c_v and

kschl = c_zmbp and

vkorg = wa_mvke-vkorg and

vtweg = wa_mvke-vtweg and

matnr = wa_mvke-matnr and

( datab <= gv_datum and

datbi >= gv_datum ).

endselect.

select

kbetr up to 1 rows

from konp

into wa_konp

where knumh = wa_a004-knumh.

endselect.

*then doing some calculations.

endloop.

Hi Srinivas reddy,

I have sale organization ,distribuiton channel ,material,applcation,conditon type.

I checked with KONH table,in taht table we don't have the material,sale organization etc.

I need to get the base price for materials from KONP table (KBETR) for this first we need KNUMH,This we will get from A004 table (Material)

If anyone have ideas please share with me.

Thanks for all your support.

best regards

Peddi Reddy.

[valter_oliveira]

Did you try the only one select to A004 into an hashed table? It will work ... (tryied here).

[Former Member]

Hi Valter,

i am trying with hashed table.

thanks for your input

i will inform you with the result.

best regards

peddireddy

[valter_oliveira]

Ok.

This is the program i made for testing. 5 seconds for 100.000 records.

TABLES: mvke.

TYPES: BEGIN OF ty_a004,
         vkorg TYPE vkorg,
         vtweg TYPE vtweg,
         matnr TYPE matnr,
         knumh TYPE knumh,
       END OF ty_a004.

DATA: it_a004 TYPE HASHED TABLE OF ty_a004 WITH UNIQUE KEY vkorg vtweg matnr,
      wa_a004 TYPE ty_a004,
      count(10) TYPE n.

START-OF-SELECTION.

  SELECT vkorg vtweg matnr knumh
    FROM a004
    INTO TABLE it_a004
   WHERE kappl = 'V'
     AND kschl = 'ZPR0'
     AND vkorg EQ 'INCM'
     AND vtweg IN ('L1','S1')
     AND datbi GE sy-datum
     AND datab LE sy-datum.

  CLEAR count.

  SELECT vkorg vtweg matnr FROM mvke
    INTO (mvke-vkorg, mvke-vtweg, mvke-matnr)
   WHERE vkorg EQ 'INCM'
     AND vtweg IN ('L1','S1').

    READ TABLE it_a004 INTO wa_a004
          WITH TABLE KEY vkorg = mvke-vkorg
                    vtweg = mvke-vtweg
                    matnr = mvke-matnr.

    CHECK sy-subrc EQ 0.
    ADD 1 TO count.
    WRITE: /1 mvke-vkorg, mvke-vtweg, mvke-matnr, wa_a004-knumh.

  ENDSELECT.

  SKIP.
  WRITE /1 count.

  FREE it_a004.

Regards,

Valter Oliveira.

[Former Member]

Hi Valter,

i have treid with hased table removing for all entries.

I have created background job 28.08.2008,still it is running.

can you please go thorugh the below code.

select kappl

kschl

vkorg

vtweg

matnr

datbi

datab

knumh

from a004

into table it_a004

where kappl = c_v and

kschl = c_zmbp and

vkorg = '6501' and

vtweg in ('30','32') and

datbi ge gv_datum and

datab le gv_datum.

if sy-subrc eq 0.

select knumh

kopos

kbetr

from konp

into table it_konp

for all entries in it_a004

where knumh = it_a004-knumh and

kopos = '01'.

endif.

Then we have a loop after this select stament.

in the below loop we have around 185.000 records.

now loop has to run 185.000 times .

Do you think taht loop is taking more time?.

loop at it_mvke into wa_mvke.

read table it_a004 into wa_a004 with table key

vkorg = wa_mvke-vkorg

vtweg = wa_mvke-vtweg

matnr = wa_mvke-matnr.

• if sy-subrc = 0.

read table it_konp into wa_konp with key knumh = wa_a004-knumh

kopos = '01'

binary search.

Doing some other calcaulations

endloop.

and after that we haveto send all these records to application layer file.

then used opendataset for file for output

loop

transfer the data

endloop.

Will Loop also effect the performance?

Please give me your valable inputs?

best regards

Peddi reddy.

[Former Member]

Hi Valteir,

Now A004 table hitting is taking 4 minutes only after removing for all entries.thanks

But after that loop is there (with 170.000 records)

here contunously runnign the loop

Do you have any idea to do loop fast ?

Cn you pelase give me inputs.

thanks in advance

[valter_oliveira]

Hi again.

What were the fields that you got from A004 and defined as the unique key in hashed table? I don't think you need kappl and kschl into your itab, but remember that if you do, you should include them in the unique key declaration in hashed table. And datab I wouldn't get too ... you don't need it.

I would do like program i made for testing:

select vkorg vtweg matnr knumh

from a004

into table it_a004 "table with only those 4 fields

where kappl = c_v and

kschl = c_zmbp and

vkorg = '6501' and

vtweg in ('30','32') and

datbi ge gv_datum and

datab le gv_datum.

and

read table it_a004 into wa_a004

with table key vkorg = wa_mvke-vkorg

vtweg = wa_mvke-vtweg

matnr = wa_mvke-matnr.

or if you want kappl and kschl in your itab, remember to use it in the read statement and unique key defintion, like:

read table it_a004 into wa_a004

with table key kappl = c_v

kschl = c_zmbp

vkorg = wa_mvke-vkorg

vtweg = wa_mvke-vtweg

matnr = wa_mvke-matnr.

Another thing. ti_konp can be a hashed table too with key knumh kopos and then .

read table it_konp into wa_konp

with table key knumh = wa_a004-knumh

kopos = '01'.

I don't think OPEN DATASET will be a performance problem.

Regards.

Valter Oliveira.