Issue with program

[Former Member]

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

[FredericGirod]

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

[Former Member]

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

[Former Member]

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.

[FredericGirod]

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 )

[RaymondGiuseppi]

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

[Former Member]

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.

[Former Member]

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

[Former Member]

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

[Former Member]

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.

[Former Member]

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

[Former Member]

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

[RaymondGiuseppi]

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

[Former Member]

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

[ThomasZloch]

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

[Former Member]

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

Regards,

Supratik

[Former Member]

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

[former_member209120]

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