Kindly suggest a better way (consider performance) of coding this

[Former Member]

Hi ,

This statement block will be accessed by a transaction for several times.

If possible, I want this block to be very effiicient in terms of performance.

Do you have any suggestions on how to re-code this, so that this will gain a better performance?

      LOOP AT tb_spers_obj2 INTO wa_spers_obj3.
        SELECT SINGLE uname FROM agr_users INTO lv_uname
          WHERE agr_name = wa_spers_obj3-object_id.

        ls_app_table-fieldvalue = wa_spers_obj3-fieldvalue.
        ls_app_table-uname = lv_uname.
        APPEND ls_app_table TO it_app_table.
      ENDLOOP.

Thanks and regards!

Reymar

[ThomasZloch]

How often is several times? Only when (very roundabout) 10,000 or more the following recommendations make sense.

Use LOOP AT ... ASSIGNING with field symbol instead of INTO with work area.

Consider buffering AGR_USERS into a sorted, internal table once and use READ statement to access inside the loop.

Table AGR_USERS has more than just AGR_NAME in the primary key, are you sure you don't want to include e.g. the validity period in your logic?

Thomas

[Former Member]

Hi Reymar,

How big is your tb_spers_obj2 table?

Most of the time, select single inside a loop is acceptable performance wise.

I'm not in front of SAP system right now so i can not check it but if agr_name is the primary key of agr_users or agr_users have index with agr_name as the key, it should be fine.

The other way is:

        if tb_spers_obj2[] is not initial.
          SELECT agr_name
                       uname FROM agr_users 
                  INTO table lt_agr_users
                  for all entries in tb_spers_obj2
                  WHERE agr_name = tb_spers_obj2-object_id.
          sort lt_agr_users by agr_name.
         endif.
         LOOP AT tb_spers_obj2 INTO wa_spers_obj3.
         read table lt_agr_users into lw_agr_users 
                                             with key agr_name = wa_spers_obj3-object_id
                                             binary search.
          if sy-subrc = 0.
             clear ls_app_table.
             ls_app_table-fieldvalue = wa_spers_obj3-fieldvalue.
             ls_app_table-uname = lw_agr_users-uname.
             APPEND ls_app_table TO it_app_table.

          endif.

        endloop.

Regards,

Abraham

Edit:

I just realized that when I hit the post button, Thomas already reply with similar answer and with information of primary key or index in agr_users table. That's one of the critical aspect in program performace, whether you used key or index in your select statement.

Edited by: Abraham Bukit on Jan 14, 2009 4:34 PM

[Former Member]

Hi Reymar,

Try to avoid SELECTing in a loop if you can do better in a single statement. The other recommendations are going into that direction and should be applied.

What you are really trying to do in your LOOP is a little bit like this example from Stephane Faroult:

You go for weekend-shopping using your small, little car and having your complete shopping list with you:

>Shopping list:

>----

---

>milk

>butter

>soap

>cerials

>coke

Now SELECTING in a loop is like this:

>1. start your car , driving to the super market

>2. get the 1st item on your list (milk) , go to the cashpoint and pay

>3. drive back home

>4. put the milk in the fridge

>5. start your car , driving to the super market

>6. get the 2nd item on your list (butter) , go to the cashpoint and pay

>7. drive back home

>8. put the butter in the fridge

>...

Do you really like that? You would certainly do it in ONE shot.

Well , some would suggest a faster car (ferrari ?) - the ones that call for better hardware,

or another index ...

If you can express your (SELECT) problem in ONE statement, just do it- there are some optimizations

in OPEN SQL that make the alternatives mentioned much better than the boring weekend shopping

example...

bye

yk

Edited by: Julius Bussche on Jan 14, 2009 7:52 PM

Code tags on long text removed and quote added as requested.

[Former Member]

To YukonKid/Julius,

Thanks for your effort. You could've said it in one sentence (--,) hahaha!

I'll just try the codes of those who generously and kindly replied.

I appreciate more on direct answers and not on shopping answers.

Cheers!

[Former Member]

>

> To YukonKid/Julius, 
> 
> Thanks for your effort. You could've said it in one sentence (--,) hahaha! 
> 
> I'll just try the codes of those who generously and kindly replied. 
> 
> I appreciate more on direct answers and not on shopping answers. 
> 
> Cheers!

Reymar - this is the internet. You get what you pay for. You might like all the advice you get, but it's not the forums job to do your coding for you. The general advice YK gave was good. You should bear it in mind.

Rob

But on the other hand, if I had a Ferrari, I'd be tempted to take the long approach.

[Former Member]

>

> 

>       LOOP AT tb_spers_obj2 INTO wa_spers_obj3.
>         SELECT SINGLE uname FROM agr_users INTO lv_uname
>           WHERE agr_name = wa_spers_obj3-object_id.
> 
>         ls_app_table-fieldvalue = wa_spers_obj3-fieldvalue.
>         ls_app_table-uname = lv_uname.
>         APPEND ls_app_table TO it_app_table.
>       ENDLOOP.
> 

Never use a select inside loop.

do like this .

select uname agr_nam from agr_users into it_agr.
loop at tb_spers_obj2 INTO wa_spers_obj3.
read table agr_users INTO wa_agr_users with key agr_name = wa_spers_obj3-object_id.

         ls_app_table-fieldvalue = wa_spers_obj3-fieldvalue.
         ls_app_table-uname = lv_uname.
         APPEND ls_app_table TO it_app_table.
       ENDLOOP.

[Former Member]

HI,

Write this way..

1. Don't use the Seelct inside the Loop.

2. Use the binary search with the read.

Sort it _spers_obj3 by object_id.
select uname agr_nam
  from agr_users 
  into table it_agr 
   for all entries in it _spers_obj3
where agr_name = it _spers_obj3-object_id .
IF sy-subrc eq 0.
  sort lt_agrs by agr_name.
endif.

loop at tb_spers_obj2 INTO wa_spers_obj3.
    read table agr_users INTO wa_agr_users 
                              with key agr_name = wa_spers_obj3-object_id
                              Binary search.
    ls_app_table-fieldvalue = wa_spers_obj3-fieldvalue.
    ls_app_table-uname = wa_agr_users .
    APPEND ls_app_table TO it_app_table.
ENDLOOP.