topic Re: Issue with program in Application Development and Automation Discussions

Issue with program

Former Member — Mon, 09 Sep 2013 14:39:56 GMT

Hi experts,

I created a program in BW to make some complex calculations, but it's making forever to complete the calcuations.

I used fields symbols and read binary serach to accelerate treatments, but i only won few minutes, I have about 4 millions records in my database.

What other kind of improvements can i use to accelerate my code?

Thanks.

Amine

Re: Issue with program

FredericGirod — Mon, 09 Sep 2013 14:42:19 GMT

Hi,

depends of the code Amine

there is a little tips to accelerate READ TABLE but it's more easy to explain with an example.

regards

Fred

Re: Issue with program

Former Member — Mon, 09 Sep 2013 14:48:02 GMT

Merci Frederic

I was on debug mode, i think that i have too much loops in my code and this what's wrong with my program. I have to use sort statements and find a way to supress those loops.

Amine

Re: Issue with program

Former Member — Mon, 09 Sep 2013 14:48:56 GMT

Here's my program:

TYPES:BEGIN OF structure,
partno TYPE /bic/oizbpn,
fam TYPE /bic/oizbfamge,
version TYPE /bic/oizbversge,
END OF structure.

DATA:
str_mph TYPE structure,
tble_mph TYPE TABLE OF structure,
it_resultpackage LIKE RESULT_PACKAGE,
wa_resultpackage LIKE LINE OF RESULT_PACKAGE.

DATA : program TYPE /bic/oizbprog.
CONSTANTS: novlue TYPE string VALUE 'NA'.

*Loop on MPL
LOOP AT RESULT_PACKAGE ASSIGNING <result_fields>.
CLEAR program.
CLEAR str_mph.
IF <result_fields>-fl_mph IS NOT INITIAL.
CONCATENATE <result_fields>-fam
<result_fields>-version <result_fields>-/bic/zbmodule
INTO program.
<result_fields>-program = program.
str_mph-partno = <result_fields>-partno.
str_mph-fam = <result_fields>-fam.
str_mph-version = <result_fields>-version.
APPEND str_mph TO tble_mph.
ENDIF.
ENDLOOP.
*Copy of Result package
CLEAR wa_resultpackage.
FREE it_resultpackage.
REFRESH it_resultpackage.
LOOP AT RESULT_PACKAGE INTO wa_resultpackage
WHERE fl_gpd IS NOT INITIAL
AND fam IS NOT INITIAL
AND version IS NOT INITIAL
AND fl_mph IS INITIAL.
APPEND wa_resultpackage TO it_resultpackage.
ENDLOOP.

*Loop on GPD
CLEAR wa_resultpackage.
LOOP AT it_resultpackage INTO wa_resultpackage.
READ TABLE tble_mph WITH KEY
partno = wa_resultpackage-partno
fam = wa_resultpackage-fam
version = wa_resultpackage-version
BINARY SEARCH TRANSPORTING NO FIELDS.
IF sy-subrc = 0.
*Delete lines already on MPH
DELETE RESULT_PACKAGE WHERE
/bic/zbmodule = wa_resultpackage-/bic/zbmodule
AND article = wa_resultpackage-article
AND rept   = wa_resultpackage-rept
AND partno    = wa_resultpackage-partno
AND fam    = wa_resultpackage-fam
AND version     = wa_resultpackage-version
AND fl_gpd = wa_resultpackage-fl_gpd
AND fl_mph    = wa_resultpackage-fl_mph.
ELSE."
*Assignement of program
LOOP AT RESULT_PACKAGE ASSIGNING <result_fields>
WHERE partno = wa_resultpackage-partno
AND fam = wa_resultpackage-fam
AND version = wa_resultpackage-version.
CLEAR program.
CONCATENATE <result_fields>-fam
<result_fields>-version novlue INTO program.
<result_fields>-program = program.
ENDLOOP.
ENDIF.
ENDLOOP.
*Assign NO_PROG where no program is assigned
LOOP AT RESULT_PACKAGE ASSIGNING <result_fields>
WHERE program IS INITIAL.
<result_fields>-program = 'NO_PROG'.
ENDLOOP.

Re: Issue with program

RaymondGiuseppi — Mon, 09 Sep 2013 14:49:43 GMT

You could try to parallel calculations in batches of records, but you should give us more informations, what do you perform with internal tables ?

Regards,

Raymond

Re: Issue with program

FredericGirod — Mon, 09 Sep 2013 14:52:47 GMT

Why did you LOOP several times in the same table ?

Do you think it's possible to make only one loop ?

Fred

(ok to speak in French )

Re: Issue with program

ThomasZloch — Mon, 09 Sep 2013 14:55:34 GMT

Please try choosing more meaningful subject lines in the future, most people here have an "issue with program".

I suggest you run your program through an ABAP trace, transactions SAT or ST12, this will tell you exactly where most time is spent and where to focus first for performance tuning measures.

Please search for existing SCN how-to-blogs for these transactions.

Thomas

Re: Issue with program

RaymondGiuseppi — Mon, 09 Sep 2013 15:01:46 GMT

Don't forget that a READ TABLE BINARY SEARCH on a not sorted table may (and will) give unpredictable result, and that a LOOP WHERE is only optimized on SORTED TABLE TYPE, not STANDARD tables even if sorted by SORT statement (define a TYPE SORTED TABLE of structuer and give the keys)

(It is not always easy to optimize BW extraction, I know for sure. And extraction can break package of records so you may not have every required record in the same package, or you insure this in a custom extractor bypassing package sieze when necessary...)

Regards,

Raymond

Re: Issue with program

Former Member — Mon, 09 Sep 2013 18:41:03 GMT

Hi Amine,

Yes, I can find couple of bottlenecks in the code. Well, I would also like to suggest few performance improvement points :

1. Pls use Parallel Curser Method for Looping,

http://wiki.scn.sap.com/wiki/display/Snippets/ABAP+Code+for+Parallel+Cursor+-+Loop+Processing

Else,

take the entries of internal table into another duplicate table, sort the table by relevant fields and the delete the unwanted entries and then loop.

Also free the duplicate table after the loop.

2. Do not delete the entries inside the loop, better set a flag and then delete the entries outside the loop, sort the table before deleting entries.

3. use as much as free as possible.

4. Sort the internal table before using in Loop ( with where cond. ) or Read ( with Binary search ).

Hope, it helps.

Pls. post for further clarification, if required.

Thanking You All.

Re: Issue with program

Former Member — Tue, 10 Sep 2013 08:20:33 GMT

Hi Amine,

You can merge most of the loop processes in a single loop itself. Like when you are doing

Loop on MPL, at that time just add a condition before endloop and update it_resultpackage. Also in this loop only Assign NO_PROG where no program is assigned. These will reduce your 3 loops to 1. This NO_PROG can be updated if you find another value in subsequent loop.

See if you can avoid a loop inside it_resultpackage - though i did not understand fully logic of this new internal table. Check if you can check conditions in first loop itself and populate the program field. So every thing is done in one loop only.

And offcourse do sorting of intenal tables before reading or better use Hashed Table with proper keys.

Regards,

Anil

Re: Issue with program

Former Member — Tue, 10 Sep 2013 09:14:45 GMT

Using parallel cursor method will increase the time while you are doing loop inside loop(almost 50%).

Regards,

Supratik

Re: Issue with program

Former Member — Tue, 10 Sep 2013 09:52:22 GMT

Hi raymond,

Here the use of each internal table :

RESULT_PACKAGE = Package of data collected by BW while loading

tble_mph = Table where i am collecting only MPH records

it_resultpackage = I am copying the result package but only GPD records

Thanks.

Amine

Re: Issue with program

Former Member — Tue, 10 Sep 2013 12:43:32 GMT

Hi Ankit,

I have a question regarding the following point:

2. Do not delete the entries inside the loop, better set a flag and then delete the entries outside the loop, sort the table before deleting entries.

How can i set a flag?

Thanks.

Amine

Re: Issue with program

Former Member — Tue, 10 Sep 2013 16:30:46 GMT

Amine,

You can use the following tips to increase performance:

1. Aggregate the loop over RESULT_PACKAGE in a minimal level (might be in a single LOOP).

2. Rather to use Loop all the time prefer to use READ statement if possible.

3. Also prefer to process 4 million records in multiple processes part by part based on primary key of RESULT_ PACKAGE.

Thanks,

Debabrata

Re: Issue with program

Former Member — Tue, 10 Sep 2013 18:36:37 GMT

Hi Amine,

Suppose we have a internal table

lt_tab with fields four fields f1, f2, f3 and f4.

Suppose we need to delete any particular type of entry inside loop based on some condition.

Then, we can now add one more filed in the table lt_tab,

say lf_del,

Based on the condition, set the flag lf_del = 'X' for all the records with suitable condition, where we need to delete the entry.

Now, once the loop ends,

sort lt_tab by lf_del

and then delete all the entries which does not satisfy our condition,

delete lt_tab where lf_del = 'X'.

By following this approach we can eliminate deletion of records inside loop.

Hope it helps.

Thanking You All.

Re: Issue with program

Former Member — Tue, 10 Sep 2013 19:04:41 GMT

Avoid using where conditions in loop also.Instead of that put check condition after loop pass with those where conditions.
@Thomas Zloch has suggested a good point and parallel cursor could lead the program performance to better extend

Regards

Samal

Re: Issue with program

former_member209120 — Wed, 11 Sep 2013 03:15:53 GMT

Hi Amine Lamkaissi,

1. Before using READ statement with BINARY SEARCH - SORT internal table.

2. Avoid LOOP inside LOOP - Use Parallel Cursor method.

3. Avoid DELETE inside the loop - Use DELETE after LOOP

Re: Issue with program

Former Member — Wed, 11 Sep 2013 08:53:31 GMT

Thanks Ankit,

The issue is that i have to delete my records directly in a BW result package.

I can't add any additional field to my result fields (not allowed in BW)

Any idea how i can bypass it?

Thanks.

Amine