ABAP Code performance

[Former Member]

Hi Guys,

The following code seems to be taking considerable time in production. Is there a better way to optimize theis code.

LOOP AT I_T_DATA...

SELECT SINGLE BUKRS

INTO (l_bukrs)

FROM T001K

WHERE T001K~BWKEY = I_T_DATA-WERKS.

SELECT SINGLE TARGET1 TARGET2

INTO (l_salesorg, l_dischan)

from ZDERIV

where VALID_FROM le sy-datum

and SOUR3_FROM le I_T_DATA-prctr

and SOUR3_TO ge I_T_DATA-prctr

and SOUR2_FROM le l_bukrs

and SOUR2_TO ge l_bukrs

and SOUR1_FROM = 'C100'

and SOUR1_TO = 'C100'.

CASE sy-subrc.

when 0.

SELECT SINGLE PRODH

INTO I_T_DATA-zzPRODH

from mvke

where matnr eq I_T_DATA-matnr

and vkorg eq l_salesorg

and vtweg eq l_dischan.

WHEN 4.

Clear: l_salesorg, l_dischan.

ENDCASE.

Thanks

[Former Member]

how many records are there in i_t_data..

my feeling is get all the records into the internal table first and then in the loop process the records. use read table command to read ur internal tables

santhosh

[Former Member]

Hi santosh,

There are nearly half-a-million records.

Thanks

[Former Member]

Hi Sachin,

Regarding the query on the Z-table:

1. Is there an index present on the fields given in the where clause. If there is no index and if this kind of query is always used you can consider creating one.

2. Are there more than one fields in the where clause that correspond to different indexes. Then you might have to force the usage of one index by '%_hints' command or by adding more fields in the where clause and passing some default values or ranges.

3. If you can paste the execution plan of the query as in ST05, it will be helpful.

Regards,

Sameer

[RichHeilman]

I would imagine that the time being spent on this select statement.

SELECT SINGLE TARGET1 TARGET2
INTO (l_salesorg, l_dischan)
from ZDERIV
where VALID_FROM le sy-datum
  and SOUR3_FROM le I_T_DATA-prctr
  and SOUR3_TO ge I_T_DATA-prctr
  and SOUR2_FROM le l_bukrs
  and SOUR2_TO ge l_bukrs
  and SOUR1_FROM = 'C100'
  and SOUR1_TO = 'C100'.
  

Can you please verify that the fields in your WHERE clause are in the same order as they are in the table.

Regards,

Rich Heilman

[Former Member]

Hi Rich,

They are not in the same order. In the table their order is,

SOUR1_FROM

SOUR1_TO

SOUR2_FROM

SOUR2_TO

SOUR3_FROM

SOUR3_TO

VALID_FROM

I was not aware that the sequence of the fields does matter.

Thanks

[RichHeilman]

It does matter, very much Please recontruct your WHERE clause in such a way that the order is the same as the key fields of table database table.

Regards,

Rich Heilman

[Former Member]

Hi All,

This is how my new code looks:

LOOP AT I_T_DATA...

SELECT SINGLE BUKRS

INTO (l_bukrs)

FROM T001K

WHERE T001K~BWKEY = I_T_DATA-WERKS.

CASE sy-subrc.

WHEN 0.

WHEN 4.

CLEAR: l_bukrs.

ENDCASE.

SELECT SINGLE TARGET1 TARGET2

INTO (l_salesorg, l_dischan)

from ZDERIV

where SOUR1_FROM = 'C100'

and SOUR1_TO = 'C100'

and SOUR2_FROM le l_bukrs

and SOUR2_TO ge l_bukrs

and SOUR3_FROM le I_T_DATA-prctr

and SOUR3_TO ge I_T_DATA-prctr

and VALID_FROM le sy-datum.

CASE sy-subrc.

WHEN 0.

SELECT SINGLE PRODH

INTO I_T_DATA-zzPRODH

from mvke

where matnr eq I_T_DATA-matnr

and vkorg eq l_salesorg

and vtweg eq l_dischan.

WHEN 4.

Clear: l_salesorg, l_dischan.

ENDCASE.

Can this be optimized further. If not, how do we check to see if this improved the performance.

Where to check for field indexes?

Thanks

[Former Member]

We need to see the indexes on table ZDERIV. could you post them please?

It may be better to use FOR ALL ENTRIES on I_T_DATA rather than looping through it.

If not, you can definitely move the select on T001K outside of the loop.

Rob

[Former Member]

I don't know what platform he's on, but if it is Oracle, the predicate order doesn't matter.

It used to matter under older versions of Oracle, under the Rule-based optimizer.

Assuming he's using the Cost-based optimizer, which he should be, as long as he's specified all the keys, or at least the leading part, he'll fully or partially use the index.

[Former Member]

Hi Guys,

Thanks for your guidance. We are on Oracle 9.2.

Rob, when I clicked on 'Indexes' button for ZDERIV table, i got a message saying - No index has been defined for table ZDERIV. Do you want to create an Index?

Could you give more details as to how to optimize the code?

Thanks

[Former Member]

OK - what are the primary keys of the table (in order)?

Rob

[Former Member]

Rob,

These are the keys of the table in the order:

MANDT

SOUR1_FROM

SOUR1_TO

SOUR2_FROM

SOUR2_TO

SOUR3_FROM

SOUR3_TO

VALID_FROM

Thanks,

Ram

[Former Member]

See if this is faster:

SELECT SINGLE target1 target2
  INTO (l_salesorg, l_dischan)
  FROM zderiv
  FOR ALL ENTRIES IN i_t_data
  WHERE sour1_from = 'C100'
    AND sour1_to   = 'C100'
    AND sour2_from LE i_t_data-werks
    AND sour2_to   GE i_t_data-werks
    AND sour3_from LE i_t_data-prctr
    AND sour3_to   GE i_t_data-prctr
    AND valid_from LE sy-datum.

CASE sy-subrc.
  WHEN 0.
    SELECT SINGLE prodh
    INTO i_t_data-zzprodh
    FROM mvke
   WHERE matnr EQ i_t_data-matnr
    AND vkorg EQ l_salesorg
    AND vtweg EQ l_dischan.

  WHEN 4.
    CLEAR: l_salesorg, l_dischan.

ENDCASE.

I wasn't able to test this because of the custom tables, so you must test it thoroughly.

The order of the fields in the SELECT may or may not make a difference. But I always find it good programming practice to put them in the where clause in the same order as they are in the table; however, if I'm trying to use a secondary index, I put them in that order. This makes it much easier for subsequent programmers to read.

Rob

[Former Member]

Hi Rob,

I got an error:

The addition "FOR ALL ENTRIES" is not allowed for SELECT SINGLE.

Thanks

[Former Member]

You'll have to take the SINGLE out of the SELECT and create an internal table for the results.

DATA: BEGIN OF itab OCCURS 0,
        l_salesorg LIKE zderiv-target1,
        l_dischan  LIKE zderiv-target2,
      END   OF itab.

SELECT target1 target2
  INTO TABLE itab
  FROM zderiv
  FOR ALL ENTRIES IN i_t_data
  WHERE sour1_from = 'C100'
    AND sour1_to   = 'C100'
    AND sour2_from LE i_t_data-werks
    AND sour2_to   GE i_t_data-werks
    AND sour3_from LE i_t_data-prctr
    AND sour3_to   GE i_t_data-prctr
    AND valid_from LE sy-datum.

The logic will also change for the select from MVKE.

Rob

Message was edited by: Rob Burbank

[Former Member]

Rob,

I am planning to use 'Field-Symbols' instead of I_T_DATA since it gives a better performance.

LOOP AT I_T_DATA ASSIGNING <FS>.

When i used <FS> with FOR ALL ENTRIES in the SELECT statement, i get an error. Can't we use 'FOR ALL ENTRIES' with <FS>.

SELECT TARGET1 TARGET2

INTO TABLE target

FROM ZDERIV

FOR ALL ENTRIES IN <FS>

where SOUR1_FROM = 'C100'

and SOUR1_TO = 'C100'

and SOUR2_FROM le l_bukrs

and SOUR2_TO ge l_bukrs

and SOUR3_FROM le <FS>-prctr

and SOUR3_TO ge <FS>-prctr

and VALID_FROM le sy-datum.

This code didn't work. Thanks for your patience and help.

Regards

[former_member183804]

Hello Sachin,

you can use a field-symbols which of course must be type as table. But with the specifc statement the field-symbol wont improve the performance.

Field-Symbols help when looping over the table avoiding the necessity to copy each record into a (header) line. But for operations on the table content itself they are of no great use.

Regards

Klaus

[Former Member]

Using field symbols might give a small increase in performance, but the best way for you to acheive what you want is to reduce the number of trips to the database. FOR ALL ENTRIES or database joins are still your best bet.

Rob

[Former Member]

Hi Sachin,

The problem is since you are having a lot of record nearly ( 1/2 a million records in the looping table ) , so even the select single statement will also

worsen the situation if it is used inside a loop , the reason being that it will have to search the data base again half a million times.

This surely will have a harmfulll impact on the performance.

Is your custom table very big. If it is not you can make the full table buffering switched on for the table and see.

The other thing is to take the values of the custom table prior to the loop in an

internal table and use a read statement instead of a loop.

This will surely decrease the time taken as there will be no data base reads.

Hope this helps !

[Former Member]

Sachin,

If possible avoid the select statements inside the loop. You can select the data before the loop and store in some internal table. Inside the loop use read statement (with binary search) two read the record (can use Hash Table also).

Please mark the thread answered if yiu already have the solution.

[Former Member]

You could try removing LT, GT from the where condition of the SELECT and use

it within a loop.

LOOP AT I_T_DATA...

SELECT SINGLE BUKRS

INTO (l_bukrs)

FROM T001K

WHERE T001K~BWKEY = I_T_DATA-WERKS.

SELECT TARGET1 TARGET2

INTO table t_zderiv

from ZDERIV

<b>where SOUR1_FROM = 'C100'

and SOUR1_TO = 'C100'.</b>

<b>if sy-subrc eq 0.

loop at t_zderiv where VALID_FROM le sy-datum

and SOUR3_FROM le I_T_DATA-prctr

and SOUR3_TO ge I_T_DATA-prctr

and SOUR2_FROM le l_bukrs

and SOUR2_TO ge l_bukrs .

SELECT SINGLE PRODH

INTO I_T_DATA-zzPRODH

from mvke

where matnr eq I_T_DATA-matnr

and vkorg eq t_zderiv-vkorg

and vtweg eq t_zderiv-vtweg.

EXIT.

endloop.

endif.</b>

[Former Member]

Instead of looping ane then selecting single.

Use :

if not I_T_DATA[] is initial.

select bukrs bwkey

into table tb_1

FROM T001K

for all entries in I_T_DATA

where T001K~BWKEY = I_T_DATA-WERKS.

SELECT TARGET1 TARGET2

INTO table tb_target

from ZDERIV

for all entries in I_T_DATA

where

and SOUR3_FROM le I_T_DATA-prctr

and SOUR3_TO ge I_T_DATA-prctr

and SOUR1_FROM = 'C100'

and SOUR1_TO = 'C100'

and VALID_FROM le sy-datum.

after which cntinue with logic u r gooing forward with...

ne help please post the problem

Regards,

Rajashree

[Former Member]

Hi,

Plz. consider the following rules to optimize your entire program's performance:

1. Define internal tables as sorted ones

2. Try to use joins insted of loops & selects, or nested selects

2'. Instead of using joins you may define a database view (SE11) which is quite faster than a join (between the originary tables used to fill I_T_DATA and ZDERIV, or between ZDERIV and MVKE)

3. Define secondary indexes with the fields you need (not only key fields are allowed to be used in an index) (for ZDERIV)

4. Select single is only useful if in Where clause are exactly the key fields

4' Use select up to 1 row instead of select single

5. Try to avoid using select within a loop

5'. For T001K use other itab in which you get in bulk all needed data before looping, and in loop use read table with binary search

Try to implement all these rules within your code and you'll see

I don't have time unfortunately to write the exact code, but ask if questions.

[Former Member]

Hi,

Remove all the SELECT statments from the loop and use For all entries to select the data from tables and keep the data in internal tables then implement your validations in the loop by reading internal table data.

Thanks,

[Former Member]

SELECT SINGLE BUKRS

INTO (l_bukrs) "l_bukrs must be an internal table. if possible add some other necessary fields also.

FROM T001K

for all entries in I_T_DATA-WERKS

WHERE T001K~BWKEY = I_T_DATA-WERKS.

sort l_bukrs by bukrs.

LOOP AT I_T_DATA...

Read table l_bukrs with key werks - I_T_DATA-WERKS

if sy-subrc = 0.

v_bukrs = I_T_DATA-WERKS-bukrs.

endif.

<b>SELECT SINGLE TARGET1 TARGET2

INTO (l_salesorg, l_dischan)

from ZDERIV

where VALID_FROM le sy-datum

and SOUR3_FROM le I_T_DATA-prctr

and SOUR3_TO ge I_T_DATA-prctr

and SOUR2_FROM le l_bukrs

and SOUR2_TO ge l_bukrs

and SOUR1_FROM = 'C100'

and SOUR1_TO = 'C100'.

CASE sy-subrc.

when 0.

SELECT SINGLE PRODH

INTO I_T_DATA-zzPRODH

from mvke

where matnr eq I_T_DATA-matnr

and vkorg eq l_salesorg

and vtweg eq l_dischan.

WHEN 4.

Clear: l_salesorg, l_dischan.

ENDCASE.</b>

End loop.

Similarly change the other select statement also. Look Select staments inside loop hampers performance and you have used 2 select statements here. We can use Read statement in place of Select. But the temporary tables to be refreshed to provide adiquate ABAP memory.

Please look to this Suggestions.

Thanks

Dibyajyoti

[alison_lloyd]

Go into transaction st05, activate trace, run your program, go back to st05 and deactivate and display. Go to trace list -> summarize by table or combined table accesses then table list aggregate and post the results.

Also run se30 to check for inefficient code.

As most peiple have already mentioned select for all entries then read is probably the way to go. Please consider using auxiliary tables to do your selections if the data is repeated particularly if your material is repeated often e.g.

• define auxiliary table for your internal table same type and fill with entries from your existing internal table

i_t_data_aux[] = i_t_data[].

Sort i_t_data_aux by werks.

delete adjacent duplicates from i_t_data_aux[] comparing werks.

select bukrs

from t001k

for all entries in i_t_data_aux[].

where T001K~BWKEY = I_T_DATA_aux-WERKS.

[Former Member]

Hi,

Could you post here the structure of your Z* table?

[suresh_s9]

u don't use select statements in side the loop...cause suppoer ur internal table wch is used for loop has 1000 records..and the table wch is used inside the loop T001w had 500 records..

on the database hitting is for I_T_DATA is 1000(for example)

1000*500 = 500000... total 1000+500000see hw many times is...for avoid this one u first select the data form db and put into 1 intenal table ...( so it is comes to ur local buffer)..by using this interal table we can use read statement in side the loop...here no data base hitiing...

here the total db hittings : for I_T_DATA: 1000

T001w is 500

Total is 1500.....see the diffrence...

see the code below:

select bukrs into i_too1 for all entries in I_T_DATA where bwkey = I_T_DATA-werks.

SELECT SINGLE TARGET1 TARGET2

INTO table i_ZDERIV----

---

here it has fields as target1 and target2

from ZDERIV

for all entries in l_i_data

where VALID_FROM le sy-datum

and SOUR3_FROM le I_T_DATA-prctr

and SOUR3_TO ge I_T_DATA-prctr

and SOUR1_FROM = 'C100'

and SOUR1_TO = 'C100'.

after this..

loop at I_T_DATA into wa_t_data.

read table i_t001w into wa_too1w with key bukrs = wa_i_data-werks.

read table i_ZDERIV

into wa_ZDERIV

and SOUR3_FROM le wa_i_DATA-prctr

and SOUR3_TO ge wa_i_DATA-prctr

and SOUR2_FROM le wa_t001w-bukrs

and SOUR2_TO ge wa_t001w-bukrs

with key bukrs = wa_t_data-werks.

here the value of bukrs in wa_t001w-bukrs...

endloop.

inside the loop we can check the sy-subrc's...

i thnk it is useful for u...please give me rely hw it is woking...