Please validate my logic performance point of view:

[Former Member]

Please validate my logic performance point of view:

logic I wrote :

LOOP AT i_mara INTO wa_mara.

*-----For material description, go to makt table.

SELECT SINGLE maktx

FROM makt

INTO l_maktx

WHERE matnr = lwa_mara-matnr

AND SPRAS = 'E'.

IF sy-subrc = 0.

wa_mara-MAKTX = l_maktx.

ENDIF. " IF sy-subrc = 0.

*-----For Recurring Inspection, go to marc table.

SELECT prfrq

FROM marc

INTO l_prfrq

UP TO 1 ROWS

WHERE matnr = lwa_mara-matnr.

ENDSELECT.

IF sy-subrc = 0.

wa_mara-prfrq = l_prfrq.

ENDIF. " IF sy-subrc = 0.

MODIFY TABLE i_mara FROM wa_mara

TRANSPORTING maktx.

CLEAR : wa_mara.

ENDLOOP. " LOOP AT i_mara INTO wa_mara.

Or is it better below : ?

To SELECT all the maktx values from makt and all prfrq values from marc

in two internal tables and

Loop at i_mara.

LOOP at all maktx itab

and pass corresponding maktx values into i_mara table

and pass corresponding prfrq values into i_mara table

ENDLOOP.

OR

is there any better performance logic you suggest ?

THANKS IN ADVANCE.

[JozsefSzikszai]

>

> is there any better performance logic you suggest ?

yes, there is!

you have to JOIN all three tables and select only the required fields, something like:

SELECT ...
INTO TABLE itab
FROM makt AS makt
INNER JOIN marc AS marc
ON makt~matnr EQ marc~matnr
INNER JOIN mara AS mara
ON marc~matnr EQ mara~matnr
WHERE makt~spras EQ sy-langu
AND makt~matnr ...

hope you got the idea...

[Former Member]

HI sam,

follow the belwo steps to inprove the performance...

SELECT maktx
         FROM makt
         INTO table l_maktx
       for all entries in i_mara 
        WHERE matnr = i_mara-matnr
          AND SPRAS = 'E'.
       IF sy-subrc = 0.
       endif.


   SELECT prfrq
         FROM marc
         INTO table  l_prfrq
         for all entries in i_mara 
        WHERE matnr = l_mara-matnr.
         IF sy-subrc = 0.
         endif.

    LOOP AT i_mara INTO wa_mara.
       w_ind  = sy-tabix. "capturing index
 *-----For material description, go to makt table.
       read table  l_maktx with key where matnr = lwa_mara-matnr binary search.
       IF sy-subrc = 0.
         wa_mara-MAKTX = l_maktx.
       ENDIF.        
*-----For Recurring Inspection, go to marc table.
       read table l_prfrq with key where matnr = lwa_mara-matnr binary search.
       IF sy-subrc = 0.
         wa_mara-prfrq = l_prfrq.
        ENDIF.        
      MODIFY TABLE i_mara FROM wa_mara index w_ind.    "Modifying table with index...
      CLEAR : wa_mara.
    ENDLOOP.  

Regards,

Prabhudas

Edited by: Prabhu Das on May 14, 2009 12:34 AM

[Former Member]

Hi,

Writing a select query inside a loop hits on very high performance issue...

and also dont use join and for all entries together in the select query.....

your idea about writing two different select queries and putting all the values into one table and then looping will improve the performance...

do it this way....

SELECT DISTINCT matnr maktx
FROM makt
INTO table t_maktx " this internal table should contain two fields namely matnr and maktx
for all entries in i_mara
WHERE matnr = i_mara-matnr
AND SPRAS = 'E'.

SELECT DISTINCT matnr prfrq
FROM marc
INTO table t_prfrq  " this internal table should contain two fields namely matnr and prfrq
for all entries in i_mara
WHERE matnr = i_mara-matnr.


LOOP AT i_mara INTO wa_mara.
*-----For material description, go to makt table.
READ TABLE t_maktx INTO i_maktx WITH KEY mara = wa_mara-mara.
IF sy-subrc = 0.
wa_mara-MAKTX = l_maktx.
ENDIF. " IF sy-subrc = 0.

*-----For Recurring Inspection, go to marc table.
READ TABLE t_prfrq INTO i_prfrq WITH KEY mara = wa_mara-mara.
IF sy-subrc = 0.
wa_mara-prfrq = l_prfrq.
ENDIF. " IF sy-subrc = 0.
MODIFY i_mara FROM wa_mara INDEX sy-tabix.
CLEAR : wa_mara.
ENDLOOP. " LOOP AT i_mara INTO wa_mara.

Regards,

Siddarth

[Former Member]

the above code will improve your performance and for more you can do a binary search after sorting the table

i have discussed some good coding practices on this link it might help

http://nafran.blogspot.com/search/label/performance

https://wiki.sdn.sap.com/wiki/display/Snippets/bestwaytocodenested+loops

Regards Nafran

[Former Member]

Hi sam,

please check the below optimized code.

SELECT matnr maktx
         FROM makt
         INTO table l_makt
       for all entries in i_mara 
        WHERE matnr = i_mara-matnr
          AND SPRAS = 'E'.
       IF sy-subrc = 0.
            sort i_maktx by matnr.
       endif.
 
 
   SELECT matnr prfrq
         FROM marc
         INTO table  l_marc
         for all entries in i_mara 
        WHERE matnr = l_mara-matnr.
         IF sy-subrc = 0.
               sort i_marc by matnr.
               delete adjacent duplicates comparing matnr.
         endif.
 
    LOOP AT i_mara INTO wa_mara.
       v_ind  = sy-tabix. 
 *-----For material description, go to makt table.
       read table  l_makt with key where matnr = wa_mara-matnr binary search.
       IF sy-subrc = 0.
         wa_mara-MAKTX = l_makt-maktx.
       ENDIF.        
*-----For Recurring Inspection, go to marc table.
       read table l_marc with key where matnr = lwa_mara-matnr binary search.
       IF sy-subrc = 0.
         wa_mara-prfrq = l_marc-prfrq.
        ENDIF.        
      MODIFY TABLE i_mara FROM wa_mara index w_ind.    "Modifying table with index...
      CLEAR : wa_mara.
    ENDLOOP.  
 

Thanks,

Sudha

[former_member555112]

Hi,

Selecting first into internal tables and then looping on them would be more efficient then using select statements within the loop.

Regards,

Ankur Parab

[former_member194613]

The above mentioned wki is pure nonsense

People with 18 points should not write performance wikis.

Hope that moderators can also close wikis.

at LOOP AT WHERE is always linear, independent whether that table is sorted or not.

A sorted table is something different! And the optimization of the standard table is possible but works also different.

The parallel cursor solution is not fast, but completely wrong! If there is record missing in the inner table

then it will be out of synch and will not find anything anymore!. The correct parallel cursor is much more complicated and not necessary.

Siegfried

[Former Member]

If you use data from mara, and you are sure you will reuse the same materials many time, first check if the data is already in static table, if yes, then you just do the read table, otherwise do select single from mara.

It will save a lot of performance replacing selecting the same data all the time.

[former_member182566]

> People with 18 points should not write performance wikis.

I also happen to have 18 points (I have never been active in SDN forums) so I take this personally...

> at LOOP AT WHERE is always linear, independent whether that table is sorted or not.

> A sorted table is something different! And the optimization of the standard table is possible but works also different.

what do you mean by this? in his example t_lips1 is a sorted table.

t_lips1 TYPE SORTED TABLE OF lips WITH NON-UNIQUE KEY vbeln.

> The parallel cursor solution is not fast, but completely wrong! If there is record missing in the inner

> table then it will be out of synch and will not find anything anymore!.

I only read the code very quickly, but from what I see he does not increment the index in case no record is found in the inner table. Why would it get out of synch?

(and by the way, i am not even sure you can have purchase orders with no items, but that is another story)

Rui Dantas

[ThomasZloch]

Here is the parallel cursor approach from that blog/wiki:

SORT t_likp BY vbeln.
SORT t_lips BY vbeln.

LOOP AT t_likp INTO likp.

  LOOP AT t_lips INTO lips FROM w_index.
    IF likp-vbeln NE lips-vbeln.
      w_index = sy-tabix.
      EXIT.
    ENDIF.

  ENDLOOP.
ENDLOOP.

This works only when the two tables adhere to a certain application logic (no item without header, and potentially no header without item, too tired to think this through right now...). A more generic approach is more complex and becomes harder to understand and maintain.

Sorted tables for the inner loop might be slightly slower, but the resulting program logic is much easier to understand, no EXIT, no help fields for SY-TABIX etc.

Thomas

[former_member182566]

>

> This works only when the two tables adhere to a certain application logic (no item without header, and potentially no header without item, too tired to think this through right now...). 
> Thomas

I beg to disagree. I don't thing headers without items will be problem. It's true that items with no headers will not appear, but that is of course the whole point of the code, and is true also for loop with sorted table.

In real life I would often also use the loop with sorted table, but in some cases the slight performance improvement can be important.

By the way, that code is not exactly "from that blog/wiki". It appears in the performance examples in se38 (or at least in my SAP version it does).

internal tables -> simple algorithms:

* Entries: 100 (ITAB1), 1000 (ITAB2)
* Line width: 100
* Both tables sorted by key K

I = 1.
LOOP AT ITAB1 INTO WA1.
  LOOP AT ITAB2 INTO WA2 FROM I.
    IF WA2-K <> WA1-K.
      I = SY-TABIX.
      EXIT.
    ENDIF.
    " ...
  ENDLOOP.
ENDLOOP.

as you can see, SAP calls it a simple algorithm..

[ThomasZloch]

Always happy to discuss.

The code I quoted is from that blog, it was probably based on the performance tips of SE38, as you correctly point out.

The code you quoted that SAP calls simple...imho it is unnecessarily complex (relatively speaking) for most purposes, especially when used in "real world" algorithms that would involve much more code than this stripped down example. These SE38 performance examples are quite old, wouldn't surprise me if they were published before SORTED tables were introduced (with release 4.0, just looked it up).

Thomas

[former_member182566]

well, ok, I suppose we are saying the same thing:

1) loop with sorted tables is simple, and will be enough in most cases

2) but i suppose you agree the loop from index solution can be important in performance critical scenarios with huge tables (they do exist in "real life"), and it is good for a developer to be aware that option exists

i think that the blog post is good to show that the option exists, and to prove that it can improve the performance if really needed (without these runtime tests and charts it might not be obvious which one is better)

in my first comment i was just arguing with Siegfried because he was saying the post was nonsense because his loop at sorted table was not really sorted (obviously not true, because if it was the blog poster wouldn't have noticed the huge performance improvements that he did) and that the loop from index would become out of synch (not true either).

(I started following this forum a couple of weeks ago in search of good discussions too, but I must say what I am seeing is lots of repeated questions, and often with wrong answers; i must say, though, that in general people asking do end up with correct answer and for that credit must be given to 3 or 4 posters, including you and Siefried)

[Former Member]

ok this is very funny so if someone gets a good way to code he should wait till he gets 1198 points till he write a performance wiki

so that means ppl who has high SDN points only can write wiki

for your information wiki definition is

[http://en.wikipedia.org/wiki/Wiki |http://en.wikipedia.org/wiki/Wiki]

its all about contribution and sharing.

did you try that code on a production or a Quality server. If you did you wont say that coz the results i have shown in that blog is what i my self tested on a Quality system of our client.

and for your information i did my internship at a SAP AFS consultancy firm and i created the account at that time. I have joined that company and now working as a developer over there.

if you have worked on a client system development on SD and MM you will know that most of the time

we use header and item tables like

likp,lips

vbak,vbap

vbrk,vbrp

.....

most of the time we come across nested loops with smiler kind of condition.

in this Q he has MATNR as reference.

if you see it properly you can see both tables are sorted.

and the select statement is for all entries.

for your information there can be a delivery document item with out a header if you are aware of DB concepts in that case there will be a foreign key error.

ok lets think about a situation like that even in that case if there ant any header data then simply the client wont request for that record.( you would know if you have worked with clients )

last but not least i dont care about my points rate at SDN i just wanted to share what i know coz anyway i have a very good job here. dont try to put down ppl just because they are new.

Thomas Zloch : i never told its my code i saw it some where then i checked and i bogged it so that i can get it when i want and i saw it in se30 ( its not se38 ) but i know most of ABAP developers dont check it much so i just wanted to help.

Rui Pedro Dantas : ya your correct we dont need to use it most of the time since sorted table is easy but there are programs which works with bulky data load we can use it in places like that. Thanks for talking the truth

Nafran

sorry if i said anything to hurt anyone.

[ThomasZloch]

In order to moderate a bit, I agree with all points in Rui's last post, and Nafran, nobody should feel hurt here, it should be professional discussion.

In a public forum, you put yourself in the spotlight with posting your findings and there might be critical comments on them.

Happens that Siegfried is both an expert in this matter (member of SAP's performance and scalability team according to his blog signature, I learned a lot from his posts) and at the same time somewhat direct in his statements, but he should rather speak for himself, of course.

Looking forward to fruitful discussions on various topics.

Cheers

Thomas

[Former Member]

ya lets forget a but this even i have learned from his posts. we all are on a learning process nobody knows everything so we always have to learn from each other.

SDN is kind of the open source community for SAP ( correct me if i am wrong i get that feeling since i have been with the community of apache axis2 during my final year project)

anyway lets go ahead and share our knowledge to improve our skills

Thanks Nafran

[Former Member]

The fact that poster does not have a lot of points does not mean that his or her ideas are less worthy.

The inverse is also true.

Rob