Please Improve Performance of Code

[Former Member]

Hi All,

How to improve performance of this following code.

Please help me.

Its too urgent.

**********************

TYPES : BEGIN OF ICUSTOMER,

KUNNR TYPE KUNNR,

NAME1 TYPE NAME1,

SORTL TYPE SORTL,

KTOKD TYPE KTOKD,

TELF1 TYPE TELF1,

TELFX TYPE TELFX,

STRAS TYPE STRAS,

REGIO TYPE REGIO,

ORT01 TYPE ORT01,

LAND1 TYPE LAND1,

PSTLZ TYPE PSTLZ,

LOEVM TYPE LOEVM,

NODEL TYPE NODEL,

ADRNR TYPE ADRNR,

SPRAS TYPE SPRAS,

ZTERM TYPE DZTERM,

TRANSPZONE TYPE LZONE,

ATINN TYPE ATINN,

ATWTB TYPE ATWTB,

STR_SUPPL1 TYPE AD_STRSPP1,

STR_SUPPL2 TYPE AD_STRSPP2,

END OF ICUSTOMER.

TYPES : BEGIN OF ICAWN ,

ATINN TYPE CAWN-ATINN,

ATWTB TYPE CAWNT-ATWTB,

END OF ICAWN.

DATA : I_ICUSTOMER TYPE STANDARD TABLE OF ICUSTOMER. " OCCURS 0 WITH HEADER LINE.

DATA : WA_ICUSTOMER TYPE ICUSTOMER.

DATA : I_ICAWN TYPE STANDARD TABLE OF ICAWN. " OCCURS 0 WITH HEADER LINE.

DATA : WA_ICAWN TYPE ICAWN.

DATA: FIELD_POSITION LIKE DD03L-POSITION.

DATA: HOLD_POSITION LIKE DD03L-POSITION.

DATA: HOLD_LINE_COUNT LIKE SY-TABIX .

FIELD-SYMBOLS : <FC>.

select k1_{KUNNR k1}NAME1 k1_{SORTL k1}KTOKD k1_{TELF1 k1}TELFX k1_{STRAS k1}REGIO

k1_{ORT01 k1}LAND1 k1_{PSTLZ k1}LOEVM k1_{NODEL k1}ADRNR k1~SPRAS

K2~ZTERM

A_{TRANSPZONE A}STR_SUPPL1 A~STR_SUPPL2

INTO corresponding fields of TABLE I_ICUSTOMER FROM

KNA1 AS K1

INNER JOIN KNB1 AS K2 ON K2_{KUNNR = K1}KUNNR

INNER JOIN ADRC AS A ON A_{ADDRNUMBER = K1}ADRNR

WHERE ( k1~KTOKD = 'Z001' OR

k1~KTOKD = 'Z002' OR

k1~KTOKD = 'Z003' OR

k1~KTOKD = 'Z004' OR

k1~KTOKD = 'ZSAI' ) and

k2~bukrs = '0100'. " To Avoid duplicate records.

IF SY-SUBRC NE 0.

RAISE NO_DATA_FOUND.

ENDIF.

LOOP AT I_ICUSTOMER INTO WA_ICUSTOMER WHERE KTOKD = 'Z001'.

SELECT T2_{ATINN T3}ATWTB INTO WA_ICAWN UP TO 1 ROWS

FROM AUSP AS T1

INNER JOIN CAWN AS T2

ON T2_{ATINN = T1}ATINN AND

T2_{ATWRT = T1}ATWRT

INNER JOIN CAWNT AS T3

ON T3_{ATINN = T1}ATINN AND

T3_{ATZHL = T2}ATZHL

WHERE T1~OBJEK = WA_ICUSTOMER-KUNNR AND

T3~SPRAS = 'EN' AND

T3~ADZHL = '0' .

CHECK SY-SUBRC EQ 0.

WA_ICUSTOMER-atinn = wa_ICAWN-atinn.

WA_ICUSTOMER-atwtb = wa_ICAWN-atwtb.

MODIFY I_ICUSTOMER FROM WA_ICUSTOMER INDEX SY-TABIX.

ENDSELECT.

ENDLOOP.

LOOP AT I_ICUSTOMER INTO WA_ICUSTOMER.

CUSTOMER-KUNNR = WA_ICUSTOMER-KUNNR.

CUSTOMER-NAME1 = WA_ICUSTOMER-NAME1.

CUSTOMER-SORTL = WA_ICUSTOMER-SORTL.

CUSTOMER-TELF1 = WA_ICUSTOMER-TELF1.

CUSTOMER-TELFX = WA_ICUSTOMER-TELFX.

CUSTOMER-STRAS = WA_ICUSTOMER-STRAS.

CUSTOMER-STR_SUPPL1 = WA_ICUSTOMER-STR_SUPPL1.

CUSTOMER-STR_SUPPL2 = WA_ICUSTOMER-STR_SUPPL2.

CUSTOMER-REGIO = WA_ICUSTOMER-REGIO.

CUSTOMER-ORT01 = WA_ICUSTOMER-ORT01.

CUSTOMER-LAND1 = WA_ICUSTOMER-LAND1.

CUSTOMER-PSTLZ = WA_ICUSTOMER-PSTLZ.

if WA_icustomer-ktokd = 'Z002'.

CUSTOMER-TRANSPZONE = WA_ICUSTOMER-TRANSPZONE.

ELSE.

CUSTOMER-TRANSPZONE = ''.

endif.

if WA_icustomer-ktokd = 'Z001' or WA_icustomer-ktokd = 'Z004'.

CUSTOMER-ZTERM = WA_ICUSTOMER-ZTERM.

ELSE.

CUSTOMER-ZTERM = ''.

endif.

CUSTOMER-LOEVM = WA_ICUSTOMER-LOEVM.

CUSTOMER-NODEL = WA_ICUSTOMER-NODEL.

• if WA_ICUSTOMER-KTOKD = 'Z001'.

CUSTOMER-ATINN = WA_ICUSTOMER-ATINN.

CUSTOMER-ATWTB = WA_ICUSTOMER-ATWTB.

• ELSE.

IF WA_ICUSTOMER-KTOKD = 'Z001'.

CUSTOMER-KTOKD = 'SP'.

ELSEIF WA_ICUSTOMER-KTOKD = 'Z002'.

CUSTOMER-KTOKD = 'SH'.

ELSEIF WA_ICUSTOMER-KTOKD = 'Z003'.

CUSTOMER-KTOKD = 'PY'.

ELSEIF WA_ICUSTOMER-KTOKD = 'Z004'.

CUSTOMER-KTOKD = 'BP'.

ELSEIF WA_ICUSTOMER-KTOKD = 'ZSAI'.

CUSTOMER-KTOKD = 'IC'.

ENDIF.

append CUSTOMER.

ENDLOOP.

Thanks,

Subu.

[Former Member]

Have you done a performance trace or runtime analysis to pinpoint the source of the problem?

Rob

[Former Member]

Thanks for Replay Rob.

I have been checked Performance Trace but didnt check runtime analysis because in my system SE30 is not working.

Please concentrate on inner joins select statment b/w loop endloop.

It will take more for excuting.

Thanks,

Subbu.

[Former Member]

The SELECT on KNA1 isn't using an index.

Rob

[Former Member]

What i will do Rob.

Please give me a Help.

[Former Member]

pass the values from kna1-kunnr to you're selection. that's the index on kna1

[Former Member]

I dont have any selection values.

Its fetch the total Custmer Master Data from DB.

Thanks,

Subbu.

[Former Member]

Hi,

do this:

1. do not use INTO corresponding fields of clause in select statement.

2. Never use logic like this :

loop at itab.

select from dbtab....

endselect.

endloop.

Instead use

select <fld_list> from dbtab into itab2

for all entries in itab

where...

sort itab by key_flds

loop at itab.

read table itab2... binary search.

"process

endloop.

3. why you want last itab customer instead of that u can add few field in i_cust... and modify that itab itself in one loop.

Note: second option is most improtant

Jogdand M B

[Former Member]

Hi ,

In my case we need to get data from multiple DB tables.

So i have to use inner joins.

Can we use for all entries in inner joins select statment.

Please help me.

Thanks,

Subbu.

[Former Member]

why is the necesity to progamm it and don't build a query with SQ01 ??

[Former Member]

Hi,

yes it is possible to use inner join and for all entries in same sql statement.

this is ur code:

LOOP AT I_ICUSTOMER INTO WA_ICUSTOMER WHERE KTOKD = 'Z001'.

SELECT T2~ATINN T3~ATWTB INTO WA_ICAWN UP TO 1 ROWS
FROM AUSP AS T1
INNER JOIN CAWN AS T2
ON T2~ATINN = T1~ATINN AND
T2~ATWRT = T1~ATWRT
INNER JOIN CAWNT AS T3
ON T3~ATINN = T1~ATINN AND
T3~ATZHL = T2~ATZHL
WHERE T1~OBJEK = WA_ICUSTOMER-KUNNR AND
T3~SPRAS = 'EN' AND
T3~ADZHL = '0' .
CHECK SY-SUBRC EQ 0.

WA_ICUSTOMER-atinn = wa_ICAWN-atinn.
WA_ICUSTOMER-atwtb = wa_ICAWN-atwtb.
MODIFY I_ICUSTOMER FROM WA_ICUSTOMER INDEX SY-TABIX.
ENDSELECT.
ENDLOOP.

modified code will be :

if i_icustomer is not initial.
SELECT T2~ATINN T3~ATWTB INTO table itab_ICAWN  "see change
FROM AUSP AS T1
INNER JOIN CAWN AS T2
ON T2~ATINN = T1~ATINN AND
T2~ATWRT = T1~ATWRT
INNER JOIN CAWNT AS T3
ON T3~ATINN = T1~ATINN AND
T3~ATZHL = T2~ATZHL
for all entries in i_icustomer
WHERE T1~OBJEK = i_ICUSTOMER-KUNNR AND "see change
T3~SPRAS = 'EN' AND
T3~ADZHL = '0' .
endif. "see change

loop at i_icustomer.
read table itab_icusomer with key ......
"update here atinn and atwtb

WA_ICUSTOMER-atinn = wa_ICAWN-atinn. "modify here
WA_ICUSTOMER-atwtb = wa_ICAWN-atwtb. "moify here
MODIFY I_ICUSTOMER transporting atinn atwtb.
ENDLOOP.

Hope this will help u. revert if u hv any doubt.

Jogdand M B

[Former Member]

Thanks Jogdand,

But what i put in key field area...

read table itab_icusomer with key ????

Here I want to which fields i need to mention..

please tel me.

[Former Member]

Hi,

try this:

if i_icustomer is not initial.
SELECT T2~ATINN T3~ATWTB INTO table itab_ICAWN  "see change
FROM AUSP AS T1
INNER JOIN CAWN AS T2
ON T2~ATINN = T1~ATINN AND
T2~ATWRT = T1~ATWRT
INNER JOIN CAWNT AS T3
ON T3~ATINN = T1~ATINN AND
T3~ATZHL = T2~ATZHL
for all entries in i_icustomer
WHERE T1~OBJEK = i_ICUSTOMER-KUNNR AND "see change
T3~SPRAS = 'EN' AND
T3~ADZHL = '0' .
endif. "see change
 
loop at i_icustomer.
read table itab_icusomer with key OBJEK = I_ICUSTOMER-KUNNR . "see KEY field 
"update here atinn and atwtb
 
WA_ICUSTOMER-atinn = wa_ICAWN-atinn. "modify here
WA_ICUSTOMER-atwtb = wa_ICAWN-atwtb. "moify here
MODIFY I_ICUSTOMER transporting atinn atwtb.
ENDLOOP.

revert back if u hv any doubt.

Jogdand M B

[Former Member]

I have done like that only but i am getting syntax error like

When using the addition "FOR ALL ENTRIES IN itab", the fields "OBJEK"and "I_ICUSTOMER-KUNNR" must have the same type and length.

I couldn't understand why getting this error.

Thanks,

Subbu

[Former Member]

Because both objek and kunnr are not same data type and lenth.

So we are getting this type of error.

what i wil do.

[Former Member]

Hi,

What u want to do actually? bcoz codes below, i m not able to find link between tables in following sql:

select t2_{atinn t3}atwtb into table itab_icawn "see change

from ausp as t1

inner join cawn as t2

on t2_{atinn = t1}atinn and

t2_{atwrt = t1}atwrt

inner join cawnt as t3

on t3_{atinn = t1}atinn and

t3_{atzhl = t2}atzhl

for all entries in i_icustomer

where t3~kunnr = i_icustomer-kunnr and "see change

t3~spras = 'EN' and

t3~adzhl = '0' .

Give me your (functional) requirement briefly ....

My earlier posts where purely based on performance issues, anyway, just give me ur req. then i will be abe to do something.

Jogdand M B

[Former Member]

--

[Former Member]

I need to create a RFC for get Customer Master Data .

Tables Kna1, Knb1, Adrc, Cawn.

Some fields in Kna1,

TRANSPZONE, STR_SUPPL1, STR_SUPPL2 from ADRC,

ZTERM from KNB1,

ATINN from CAWN, (Classification Data)

Thanks,

Subbu.

[Former Member]

Hi,

try this:

if i_icustomer is not initial.
SELECT T2~ATINN T3~ATWTB INTO table itab_ICAWN  "see change
FROM AUSP AS T1
INNER JOIN CAWN AS T2
ON T2~ATINN = T1~ATINN AND
T2~ATWRT = T1~ATWRT
INNER JOIN CAWNT AS T3
ON T3~ATINN = T1~ATINN AND
T3~ATZHL = T2~ATZHL
for all entries in i_icustomer
WHERE T2~ATINN = i_ICUSTOMER-ATINN AND "see change
T3~SPRAS = 'EN' AND
T3~ADZHL = '0' .
endif. "see change
 
loop at i_icustomer.
read table itab_icusomer with key ATTIN = I_ICUSTOMER-ATTIN . "see KEY field 
"update here atinn and atwtb
 
WA_ICUSTOMER-atinn = wa_ICAWN-atinn. "modify here
WA_ICUSTOMER-atwtb = wa_ICAWN-atwtb. "moify here
MODIFY I_ICUSTOMER transporting atinn atwtb.
ENDLOOP.

For details refer ur thread :

Jogdand M B

[Former Member]

I_ICUSTOMER doesnt have ATINN field.

I mean in select statment no atinn field has fetch.

[Former Member]

Also, change the multiple if statements at the end to a CASE statement.

This will make your code more clearer to understand.

Instead of IF WA_ICUSTOMER-KTOKD = 'Z001'.

CUSTOMER-KTOKD = 'SP'.

ELSEIF WA_ICUSTOMER-KTOKD = 'Z002'.

CUSTOMER-KTOKD = 'SH'.

ELSEIF WA_ICUSTOMER-KTOKD = 'Z003'.

CUSTOMER-KTOKD = 'PY'.

ELSEIF WA_ICUSTOMER-KTOKD = 'Z004'.

CUSTOMER-KTOKD = 'BP'.

ELSEIF WA_ICUSTOMER-KTOKD = 'ZSAI'.

CUSTOMER-KTOKD = 'IC'.

ENDIF.

use

CASE WA_ICUSTOMER-KTOKD.

WHEN 'Z001'.

• do something

WHEN 'Z002'.

• do something else

WHEN OTHERS.

• nothing else matched

ENDCASE.

Next point: you do the following statement:

"LOOP AT I_ICUSTOMER INTO WA_ICUSTOMER WHERE KTOKD = 'Z001'.".

As internal table I_ICUSTOMER is a standard table, this statement will lead looping through each record of this table, to find all records where KTOKD is 'Z001'. Try to change I_ICUSTOMER to a sorted table on this field, which can in case of a huge number of records lead to a much better performance. Check the help on sorted and hashed tables. Note that loop at .. where.... with hashed tables also gives a table scan, so you must use sorted tables...