Need performance tunning on this code

[former_member779991]

Hi,

I hve issue with the code where loop inside loop logic has been written, where first loop is header and second loop for item of BOM data. First table is fetching data for material and second table item level data. There are more than 50 thousand records in first table so its showing dump as time out error. Please give the better option for the same code which i have mentioned below.

LOOP AT gt_mast. ---> this is header table

CALL FUNCTION 'CSAP_MAT_BOM_ITEM_SELECT'

EXPORTING

material = gt_mast-matnr

plant = gt_mast-werks

bom_usage = gt_mast-stlan

alternative = gt_mast-stlal

TABLES

t_stpo = gt_stpo

EXCEPTIONS

error = 1

OTHERS = 2.

LOOP AT gt_stpo.

gv_comp_qty = gt_stpo-comp_qty.

REPLACE ALL OCCURRENCES OF ',' IN gv_comp_qty WITH space.

CONDENSE gv_comp_qty.

gv_qty = gv_comp_qty.

MOVE gt_mast-matnr TO ycps_bom_tab-matnr.

MOVE gt_mast-werks TO ycps_bom_tab-werks.

MOVE gt_mast-stlan TO ycps_bom_tab-stlan.

*Individual mapping is done as the valid to and from date is not in BW format

ycps_bom_tab-stlkn = gt_stpo-item_node.

ycps_bom_tab-stpoz = gt_stpo-item_count.

ycps_bom_tab-sanin = gt_stpo-rel_pmaint.

ycps_bom_tab-rekri = gt_stpo-recursive.

ycps_bom_tab-alekz = gt_stpo-ale_ind.

ENDLOOP.

REFRESH gt_stpo.

ENDLOOP.

Thanks in advance.

[ThomasZloch]

I suppose you want some where-conditions for the inner loop that limit the loop to the items belonging to the corresponding header entry. Also make sure the inner table is a sorted table with key fields as used in that where-condition.

Thomas

[Former Member]

your inner loop does only a loop but no change or write .... nobody will notice if you drop it

[ThomasZloch]

I stopped looking after I saw the unconditional inner loop...

[Rui_Dantas]

Thomas, gt_stpo is calculated just before the loop (and refreshed afterwards), so the fact that it is unconditional is ok.

Reena, CSAP_MAT_BOM_ITEM_SELECT is probably doing more things than you need, so you could try to replace it with an access to STAS / STPO ( / STKO ).

Another option would be to rethink the logic and make only one access to the database and create one big (sorted) gt_stpo table (then, of course, you would have a conditional inner loop).

I am not sure if the code you have is complete, but if it is consider Siegfried's comment, because you code is doing nothing at the moment.

Rui Dantas

[ThomasZloch]

> Thomas, gt_stpo is calculated just before the loop (and refreshed afterwards), so the fact that it is unconditional is ok.

Seems I even stopped looking before the inner loop

[Former Member]

Well, I agree with Siegfried - I don't see what the inner loop is actually doing. Maybe the OP has omitted some code to comply with the 2,500 character limit.

However, the problem could be due to the call to FM CSAP_MAT_BOM_ITEM_SELECT within the loop. It may be more efficient to retrieve the data outside of the first loop and process it inside.

Rob

[former_member779991]

idealy the inner loop is item level data that we need.

its like loop of header level data

call the fm to retrieve the item level data for the particular record

then loop the item level data and pass the data into custome table.

i am worried why time out error is coming for 50 thousand record?

is there any option to split the records and do the same process.

please provide the logical fessible answer.

Thanks alot..

[Former Member]

Please rethink your program logic:

+ your inner loop has no effect, you shuffle the data, but you don't save anything

+ how large are the two tables ???? There are 2 tables you mjultiply the execution, and you think 50.000 is small ..

if the other one is also 50.000 then your have 2.500.000.000 executions .... which is far away from being small.

[former_member779991]

Hi Siegfried,

idealy yes, my header data is 50,000 and yes it multiplies at item level say for ex: each header have two item then 1 lakh iterartion for item level . but the code that is provided to me is like that.

can u suggest, is there any other way to write the logic for the same requirement, is there any other Function module similar to 'CSAP_MAT_BOM_ITEM_SELECT'

[former_member779991]

Hi Experts,

is there any other alternative Function Module which will work same sa fm 'CSAP_MAT_BOM_ITEM_SELECT' ?? i ma getting performance issue (time out error) while using this FM.

Please suggest.

Thanks..

[Former Member]

it is a quite hard communiation!

LOOP AT gt_mast. ---> this is header table
  CALL FUNCTION 'CSAP_MAT_BOM_ITEM_SELECT' 
            EXPORTING  ...
            TABLES       t_stpo = gt_stpo
            EXCEPTIONS ...

  LOOP AT gt_stpo.
     gv_comp_qty = gt_stpo-comp_qty.
     REPLACE ALL OCCURRENCES OF ',' IN gv_comp_qty WITH space.
     CONDENSE gv_comp_qty.
     gv_qty = gv_comp_qty.
* also this useless, you change an global variable, inside the loop, but only the
* last execution can have an effect.


     MOVE gt_mast-matnr TO ycps_bom_tab-matnr.
     MOVE gt_mast-werks TO ycps_bom_tab-werks.
     MOVE gt_mast-stlan TO ycps_bom_tab-stlan.

*Individual mapping is done as the valid to and from date is not in BW format
ycps_bom_tab-stlkn = gt_stpo-item_node.
ycps_bom_tab-stpoz = gt_stpo-item_count.
ycps_bom_tab-sanin = gt_stpo-rel_pmaint.
ycps_bom_tab-rekri = gt_stpo-recursive.
ycps_bom_tab-alekz = gt_stpo-ale_ind.

* here is something missing, otherwise you move and move ... completely useless!!!

  ENDLOOP.
  REFRESH gt_stpo.
ENDLOOP.

gt_mast has 2 records ???? what should be the issue with 'CSAP_MAT_BOM_ITEM_SELECT

gt_stpo has how many ... please use numbers and not indian words!!!

I would say your problem is not here but somewhere else !!!

Siegfried

[former_member779991]

Hi,

Please don't look on the code

LOOP AT gt_stpo.

gv_comp_qty = gt_stpo-comp_qty.

REPLACE ALL OCCURRENCES OF ',' IN gv_comp_qty WITH space.

CONDENSE gv_comp_qty.

gv_qty = gv_comp_qty.

• also this useless, you change an global variable, inside the loop, but only the

• last execution can have an effect.

THIS GLOBAL VARIABLE IS MAPPED WITH MY FINAL INTERNAL TABLE ycps_bom_tab, WHICH IS NOT MENTIONED IN THIS CODE. SO PLEASE DON'T LOOK ON THIS..

PLEASE CONCENTRATED ON THE LOGIC PART OF THIS CODE--->

LOOP AT gt_mast. -

---

> WHICH HAVE AROUND 50.000 THOUSAND RECORDS

CALL FUNCTION 'CSAP_MAT_BOM_ITEM_SELECT'

EXPORTING

material = gt_mast-matnr

plant = gt_mast-werks

bom_usage = gt_mast-stlan

alternative = gt_mast-stlal

TABLES

t_stpo = gt_stpo ---> THIS WILL GIVE ITEM RECORDS FOR EACH HEADER DATA (WHICH CAN HAVE 5-6 ITEMS FOR EACH MATERIAL)

LOOP AT gt_stpo.

ycps_bom_tab-stlkn = gt_stpo-item_node.

ycps_bom_tab-stpoz = gt_stpo-item_count.

ycps_bom_tab-lkenz = gt_stpo-fldelete.

ycps_bom_tab-andat = gt_stpo-created_on.

APPEND ycps_bom_tab.

ENDLOOP.

REFRESH gt_stpo.

ENDLOOP.

THIS ENTIRE LOGIC IS GIVING TIME OUT ERROR.

IS THERE ANY OPTION TO DO THE PERFORMANCE TUNNING SO THAT IT WIILL RUN SMOOTHLY WITHOUT ANY DUMP (BACKGROUND/FOREGROUND)

Note: is there any logic to split the gt_mast table records into 2-3 internal table and do the loop end loop these many times for the entire logic??

Please suggest

Thanks,

[HermannGahm]

Hi Reena,

if i got you right:

LOOP AT gt_mast. "------> WHICH HAVE AROUND 50.000 THOUSAND RECORDS
CALL FUNCTION 'CSAP_MAT_BOM_ITEM_SELECT'  "called 50000 times
EXPORTING
material = gt_mast-matnr
plant = gt_mast-werks
bom_usage = gt_mast-stlan
alternative = gt_mast-stlal
TABLES
t_stpo = gt_stpo " ---> THIS WILL GIVE ITEM RECORDS FOR EACH HEADER DATA (WHICH CAN HAVE 5-6 ITEMS FOR EACH MATERIAL)

LOOP AT gt_stpo. "called 100000 times (1 lakh) if each header has 2 items or even more if we have 5 - 6 items
ycps_bom_tab-stlkn = gt_stpo-item_node.
ycps_bom_tab-stpoz = gt_stpo-item_count.
ycps_bom_tab-lkenz = gt_stpo-fldelete.
ycps_bom_tab-andat = gt_stpo-created_on.
APPEND ycps_bom_tab.
ENDLOOP.
REFRESH gt_stpo.
ENDLOOP.

why don't you trace it with SE30 / ST12 to see which par of this OR OTHER code takes the majority of the time?

Kind regards,

Hermann

[former_member779991]

Hi Hermann,

Yes, exactly, i ment the same.

have any one come across performance issue or time out error while using the FM - 'CSAP_MAT_BOM_ITEM_SELECT' .

If yes, please suggest the relevant approch the entire code, which have mentioned in the above mail chain.

Thanks,

[Rui_Dantas]

Hello Reena,

I am reposting what I said in the beginning of this thread (since apparently you guys are exchanging comments but not really making any progress). Have you even considered this?

 CSAP_MAT_BOM_ITEM_SELECT is probably doing more things than you need, so you could try to replace it with an access to STAS / STPO ( / STKO ).
Another option would be to rethink the logic and make only one access to the database and create one big (sorted) gt_stpo table (then, of course, you would have a conditional inner loop). 

Rui Dantas

[former_member779991]

Hi Rui,

ya, i know nothing is working out. can anybody tell which all table this FM is using to fetch the records in i_stpo, so that rather than using the whole FM we can direct the select query in program itself.

[Rui_Dantas]

>

> can anybody tell which all table this FM is using to fetch the records in i_stpo, so that rather than using the whole FM we can direct the select query in program itself.

MAST -> STAS -> STPO should be enough for you.

Try to make the join and post here the explain plan.

[former_member779991]

Hi All,

Can anyone tell me the whole functionality of the FM 'CSAP_MAT_BOM_ITEM_SELECT' . Does it updating any database table ?? if yes please mentio all the list of tables.

Thanks

[Former Member]

o.k. the append was missing, it could have been something else, the APPEND explains nothing.

50.000 header

> have two item then

> 1 lakh iterartion for item level .

what does the second line mean????

[Former Member]

.... sometimes it makes sense to look for the layout of a question:

Your first LOOP contains 50.000 records and call an FM plus some cheap internal table processing, that is something completely different.

Run a test, reduce your mast table to 1000 records, then it should not dump. Execute the SE30, get the results,

come back and ask again.

Siegfried

[Former Member]

Please read header thread of the forum and learn how to use the traces.

The ST05 tells you exactly was SQL statements are done!

[Former Member]

Hi Reena,

I am sorry to say that there is very little you can do at your end to improve the performance of this code. The performnce issue is caused because of the call to function module 'CSAP_MAT_BOM_ITEM_SELECT'. Function module 'CSAP_MAT_BOM_ITEM_SELECT' determines the BOM for one material in each call. If you can copy this function module and adapt it to accept multiple materials at a time, only then will you able to make a significant difference in the performance of the program. This is not impossible but it would require a significant development and testing effort. It is so much easier to run the program in the background. Unless there is a pressing need to do this in the foreground (I would be very surprised if you tell me that there is such a need), you should not consider undertaking this exercise. Just ask the user to run the program in the background or process it for a fewer materials in the foregound.

[Former Member]

Hi Reena,

I am not much worked with BOM items...

based on code we can do like

LOOP AT gt_mast. ---> this is header table

CALL FUNCTION 'CSAP_MAT_BOM_ITEM_SELECT'

EXPORTING

material = gt_mast-matnr

plant = gt_mast-werks

bom_usage = gt_mast-stlan

alternative = gt_mast-stlal

TABLES

t_stpo = gt_stpo

EXCEPTIONS

error = 1

OTHERS = 2.

loop at gt_stpo

gt_stpo_2 = gt_stpo.

append gt_stpo_2.

clear gt_stpo, gt_stpo_2.

refresh gt_stpo.

endloop.

endloop.

LOOP AT gt_stpo_2.

here u can read gt_mast using READ statement...

so the number of executions will be reduced.

read table gt_mast with key field1 = gt_stpo_2-field1.

gv_comp_qty = gt_stpo-comp_qty.

REPLACE ALL OCCURRENCES OF ',' IN gv_comp_qty WITH space.

CONDENSE gv_comp_qty.

gv_qty = gv_comp_qty.

MOVE gt_mast-matnr TO ycps_bom_tab-matnr.

MOVE gt_mast-werks TO ycps_bom_tab-werks.

MOVE gt_mast-stlan TO ycps_bom_tab-stlan.

*Individual mapping is done as the valid to and from date is not in BW format

ycps_bom_tab-stlkn = gt_stpo_2-item_node.

ycps_bom_tab-stpoz = gt_stpo_2-item_count.

ycps_bom_tab-sanin = gt_stpo_2-rel_pmaint.

ycps_bom_tab-rekri = gt_stpo_2-recursive.

ycps_bom_tab-alekz = gt_stpo_2-ale_ind.

ENDLOOP.

Regards

Syed

[Former Member]

Hello,

try to give space to the to FL_* Parameters of the Function Module. This could help a little.

But your problem is the huge amount of data. So, the only thing you can do is

1. reduce the selected materials for frontend use

2. run the report in Background

3. Try to create JOINS and avoid using this FM.

bastian