topic Re: Need performance tunning on this code in Application Development and Automation Discussions

Need performance tunning on this code

former_member779991 — Fri, 30 Oct 2009 07:49:36 GMT

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.

Re: Need performance tunning on this code

ThomasZloch — Fri, 30 Oct 2009 07:59:39 GMT

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

Re: Need performance tunning on this code

Former Member — Fri, 30 Oct 2009 08:18:19 GMT

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

Re: Need performance tunning on this code

ThomasZloch — Fri, 30 Oct 2009 08:21:37 GMT

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

Re: Need performance tunning on this code

Rui_Dantas — Fri, 30 Oct 2009 09:42:41 GMT

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

Re: Need performance tunning on this code

ThomasZloch — Fri, 30 Oct 2009 09:54:04 GMT

> 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

Re: Need performance tunning on this code

Former Member — Fri, 30 Oct 2009 13:21:57 GMT

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

Re: Need performance tunning on this code

former_member779991 — Mon, 02 Nov 2009 14:02:06 GMT

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..

Re: Need performance tunning on this code

Former Member — Mon, 02 Nov 2009 14:53:04 GMT

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.

Re: Need performance tunning on this code

former_member779991 — Tue, 03 Nov 2009 06:58:42 GMT

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'

Re: Need performance tunning on this code

former_member779991 — Tue, 03 Nov 2009 07:34:49 GMT

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..

Re: Need performance tunning on this code

Former Member — Tue, 03 Nov 2009 08:46:13 GMT

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

Re: Need performance tunning on this code

former_member779991 — Tue, 03 Nov 2009 09:36:53 GMT

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,

Re: Need performance tunning on this code

Former Member — Tue, 03 Nov 2009 10:14:58 GMT

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????

Re: Need performance tunning on this code

HermannGahm — Tue, 03 Nov 2009 11:03:35 GMT

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

Re: Need performance tunning on this code

former_member779991 — Tue, 03 Nov 2009 13:12:02 GMT

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,

Re: Need performance tunning on this code

Rui_Dantas — Tue, 03 Nov 2009 13:20:10 GMT

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

Re: Need performance tunning on this code

former_member779991 — Tue, 03 Nov 2009 13:43:23 GMT

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.

Re: Need performance tunning on this code

Rui_Dantas — Tue, 03 Nov 2009 14:10:34 GMT


> 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.

Re: Need performance tunning on this code

Former Member — Tue, 03 Nov 2009 14:37:43 GMT

.... 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