topic Re: ABAP Code performance in Application Development and Automation Discussions

ABAP Code performance

Former Member — Wed, 08 Nov 2006 14:36:06 GMT

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

Re: ABAP Code performance

Former Member — Wed, 08 Nov 2006 14:41:09 GMT

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

Re: ABAP Code performance

RichHeilman — Wed, 08 Nov 2006 14:43:05 GMT

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

Re: ABAP Code performance

Former Member — Wed, 08 Nov 2006 14:45:12 GMT

Hi santosh,

There are nearly half-a-million records.

Thanks

Re: ABAP Code performance

Former Member — Wed, 08 Nov 2006 14:49:04 GMT

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

Re: ABAP Code performance

RichHeilman — Wed, 08 Nov 2006 14:53:34 GMT

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

Re: ABAP Code performance

Former Member — Wed, 08 Nov 2006 14:54:01 GMT

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

Re: ABAP Code performance

Former Member — Wed, 08 Nov 2006 15:44:53 GMT

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

Re: ABAP Code performance

Former Member — Wed, 08 Nov 2006 15:54:47 GMT

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

Re: ABAP Code performance

Former Member — Wed, 08 Nov 2006 16:00:52 GMT

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.

Re: ABAP Code performance

Former Member — Wed, 08 Nov 2006 16:11:56 GMT

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

Re: ABAP Code performance

Former Member — Wed, 08 Nov 2006 16:22:04 GMT

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

Rob

Re: ABAP Code performance

Former Member — Wed, 08 Nov 2006 16:29:38 GMT

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

Re: ABAP Code performance

Former Member — Wed, 08 Nov 2006 17:07:46 GMT

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

Re: ABAP Code performance

Former Member — Wed, 08 Nov 2006 17:53:48 GMT

Hi Rob,

I got an error:

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

Thanks

Re: ABAP Code performance

Former Member — Wed, 08 Nov 2006 18:07:19 GMT

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

Re: ABAP Code performance

Former Member — Wed, 08 Nov 2006 18:39:18 GMT

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

Re: ABAP Code performance

former_member183804 — Wed, 08 Nov 2006 18:45:54 GMT

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

Re: ABAP Code performance

Former Member — Wed, 08 Nov 2006 18:46:02 GMT

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

Re: ABAP Code performance

Former Member — Fri, 24 Nov 2006 11:06:36 GMT

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.