Solved: report performance

Former Member · ‎2007 Feb 28

I have a report program which is taking a lot of time in test system . I ran it in dev system it was ok . There are more records in test and its taking a lot of time . I tried my best to improve its performance . I appreciate if anyone can help me in improving its performance.

SELECT * FROM ZCOSTHIST INTO TABLE IT_ZCOSTHIST WHERE MATNR IN S-MATNR

AND BUKRS IN S-BUKRS.

take the most recent record for a material in zcosthist

SORT IT_ZCOSTHIST BY MATNR BUKRS ASCENDING DATERUN DESCENDING.

DELETE ADJACENT DUPLICATES FROM IT_ZCOSTHIST COMPARING MATNR BUKRS.

LOOP AT IT_ZCOSTHIST .

Compare zcosthist prices for PBTS,PSDI and PCSC with the prices
in VK13
check for the condition record in the access tables A118,A005,
A903,A904,A006,A004,A914 and A029 and take the price from KONP
based on the condition record number, compare the
prices if the prices are not equal move the record to it_zcosthistm
for displaying or updating the records
set cdate,ctime and cuser in zcosthist for the latest record of
each material by taking the latest record in cdhdr based on the
condition number

FOUND = 0.

MOVE-CORRESPONDING IT_ZCOSTHIST TO IT_ZCOSTHISTM.

SELECT KNUMH DATAB FROM A118 INTO TABLE IT_A118 WHERE KAPPL = 'V'

AND KSCHL = 'PR00'

AND MATNR = IT_ZCOSTHIST-MATNR

ORDER BY DATAB DESCENDING.

IF SY-SUBRC = 0.

READ TABLE IT_A118 INTO WA_A118 INDEX 1.

FOUND = 1.

KNUMH = WA_A118-KNUMH.

ELSE.

SELECT KNUMH DATAB FROM A904 INTO TABLE IT_A904 WHERE KAPPL = 'V'

AND KSCHL = 'PR00'

AND MATNR = IT_ZCOSTHIST-MATNR

ORDER BY DATAB DESCENDING.

IF SY-SUBRC = 0.

READ TABLE IT_A904 INTO WA_A904 INDEX 1.

FOUND = 1.

KNUMH = WA_A904-KNUMH.

ELSE.

SELECT KNUMH DATAB FROM A914 INTO TABLE IT_A914 WHERE KAPPL = 'V'

AND KSCHL = 'PR00'

AND MATNR = IT_ZCOSTHIST-MATNR

ORDER BY DATAB DESCENDING.

IF SY-SUBRC = 0.

READ TABLE IT_A914 INTO WA_A914 INDEX 1.

FOUND = 1.

KNUMH = WA_A914-KNUMH.

ELSE.

SELECT KNUMH DATAB FROM A005 INTO TABLE IT_A005 WHERE KAPPL = 'V'

AND KSCHL = 'PR00'

AND MATNR = IT_ZCOSTHIST-MATNR.

IF SY-SUBRC = 0.

FOUND = 1.

SORT IT_A005 BY DATAB DESCENDING.

READ TABLE IT_A005 INTO WA_A005 INDEX 1.

KNUMH = WA_A005-KNUMH.

ELSE.

SELECT KNUMH DATAB FROM A006 INTO TABLE IT_A006

WHERE KAPPL = 'V'

AND KSCHL = 'PR00'

AND MATNR = IT_ZCOSTHIST-MATNR.

IF SY-SUBRC = 0.

FOUND = 1.

SORT IT_A006 BY DATAB DESCENDING.

READ TABLE IT_A006 INTO WA_A006 INDEX 1.

KNUMH = WA_A006-KNUMH.

ELSE.

SELECT KNUMH DATAB FROM A004 INTO TABLE IT_A004

WHERE KAPPL = 'V'

AND KSCHL = 'PR00'

AND MATNR = IT_ZCOSTHIST-MATNR.

IF SY-SUBRC = 0.

FOUND = 1.

SORT IT_A004 BY DATAB DESCENDING.

READ TABLE IT_A004 INTO WA_A004 INDEX 1.

KNUMH = WA_A004-KNUMH.

ENDIF.

IF FOUND = 1.

SELECT SINGLE * FROM KONP WHERE KNUMH = KNUMH.

IF SY-SUBRC = 0.

SELECT USERNAME UDATE UTIME FROM CDHDR INTO TABLE IT_CDHDR

WHERE OBJECTCLAS = 'COND_A'

AND OBJECTID = KNUMH

ORDER BY UDATE UTIME DESCENDING.

IF SY-SUBRC = 0.

READ TABLE IT_CDHDR INTO WA_CDHDR INDEX 1.

IT_ZCOSTHIST-CDATE = WA_CDHDR-UDATE.

IT_ZCOSTHIST-CTIME = WA_CDHDR-UTIME.

IT_ZCOSTHIST-CUSER = WA_CDHDR-USERNAME.

MODIFY IT_ZCOSTHIST INDEX SY-TABIX.

ENDIF.

IF ( IT_ZCOSTHIST-LIST_PRICE <> KONP-KBETR ) .

IT_ZCOSTHISTM-ACTUALPRICE = KONP-KBETR.

APPEND IT_ZCOSTHISTM.

ENDIF.

ENDLOOP.

modify zcosthist to set cdate,ctime and cuser

MODIFY ZCOSTHIST FROM TABLE IT_ZCOSTHIST.

IF R1 = 'X'.

to display records from zcosthistm , a record will be inserted into
it_zcosthistm if the price in zcosthist is different from vk13 price

SKIP 2.

WRITE 😕 'Comparison of ZCOSTHIST prices with the VK13 prices'.

SKIP 2.

IF IT_ZCOSTHISTM[] IS INITIAL.

WRITE :/'No differences in the prices'.

SKIP 2.

ELSE.

WRITE :/5 'MATNR', 25 'BUKRS' , 35 'DATERUN' , 55 'LIST PRICE'

,75 'ACTUAL PRICE'.

SKIP 1.

LOOP AT IT_ZCOSTHISTM.

WRITE :/5 IT_ZCOSTHISTM-MATNR, 25 IT_ZCOSTHISTM-BUKRS, 35

IT_ZCOSTHISTM-DATERUN , 55 IT_ZCOSTHISTM-LIST_PRICE

LEFT-JUSTIFIED,75 IT_ZCOSTHISTM-ACTUALPRICE

LEFT-JUSTIFIED.

ENDLOOP.

ENDIF.

IF R2 = 'X'.

to update zcosthist with the latest prices

LOOP AT IT_ZCOSTHISTM.

IT_ZCOSTHISTM-LIST_PRICE = IT_ZCOSTHISTM-ACTUALPRICE.

UPDATE ZCOSTHIST SET LIST_PRICE = IT_ZCOSTHISTM-LIST_PRICE

WHERE MATNR = IT_ZCOSTHISTM-MATNR

AND BUKRS = IT_ZCOSTHISTM-BUKRS

AND DATERUN = IT_ZCOSTHISTM-DATERUN.

COMMIT WORK.

ENDLOOP.

Former Member · ‎2007 Feb 28

I didn't go through your entire code but this statement in the loop of ZCOSTHIST stopped me.

SELECT KNUMH DATAB FROM A118 INTO TABLE IT_A118 WHERE KAPPL = 'V'

AND KSCHL = 'PR00'

AND MATNR = IT_ZCOSTHIST-MATNR

ORDER BY DATAB DESCENDING.

It's always not safe to write selects in the loop since it goes to the database for each loop pass and tries to get the record. Rather do a select from A118 for all entries of ZCOSTHIST and play with your internal tables. Make this correction and check it to see if there is any improvement. then we can go with the other steps.

you can write your select like this outside the loop and in the loop you can read the statement or loop it again if necessary based on your requirement.

SELECT KNUMH

DATAB

FROM A118

for all entries of IT_ZCOSTHIST

INTO TABLE IT_A118

WHERE KAPPL = 'V'

AND KSCHL = 'PR00'

AND MATNR = IT_ZCOSTHIST-MATNR

ORDER BY DATAB DESCENDING.

Award points if it helps.

Former Member · ‎2007 Feb 28

I didn't go through your entire code but this statement in the loop of ZCOSTHIST stopped me.

SELECT KNUMH DATAB FROM A118 INTO TABLE IT_A118 WHERE KAPPL = 'V'

AND KSCHL = 'PR00'

AND MATNR = IT_ZCOSTHIST-MATNR

ORDER BY DATAB DESCENDING.

It's always not safe to write selects in the loop since it goes to the database for each loop pass and tries to get the record. Rather do a select from A118 for all entries of ZCOSTHIST and play with your internal tables. Make this correction and check it to see if there is any improvement. then we can go with the other steps.

you can write your select like this outside the loop and in the loop you can read the statement or loop it again if necessary based on your requirement.

SELECT KNUMH

DATAB

FROM A118

for all entries of IT_ZCOSTHIST

INTO TABLE IT_A118

WHERE KAPPL = 'V'

AND KSCHL = 'PR00'

AND MATNR = IT_ZCOSTHIST-MATNR

ORDER BY DATAB DESCENDING.

Award points if it helps.

Former Member · ‎2007 Feb 28

Thanks. i shall modify the program and check once again.

Former Member · ‎2007 Feb 28

Award points if it helps you.

Former Member · ‎2007 Feb 28

let me modify the program and check

Former Member · ‎2007 Feb 28

hi

good

you can try the TCODE SLIN,which ll give you detail errors where you need to be change to increase the performance of the program.

Thanks

mrutyun^

Former Member · ‎2007 Feb 28

SELECT * FROM ZCOSTHIST INTO TABLE IT_ZCOSTHIST WHERE MATNR IN S-MATNR

AND BUKRS IN S-BUKRS.

take the most recent record for a material in zcosthist

SORT IT_ZCOSTHIST BY MATNR BUKRS ASCENDING DATERUN DESCENDING.

DELETE ADJACENT DUPLICATES FROM IT_ZCOSTHIST COMPARING MATNR BUKRS.

IF I_ZCOSTHIST[] IS NOT INITIAL.

SELECT KNUMH DATAB FROM A118 INTO TABLE IT_A118

FOR ALL ENTRIES IN IT_ZCOSTHIST

WHERE KAPPL = 'V' -------> USECONSTANT

AND KSCHL = 'PR00' -------> USECONSTANT

AND MATNR = IT_ZCOSTHIST-MATNR

ORDER BY DATAB DESCENDING.

SELECT KNUMH DATAB FROM A904 INTO TABLE IT_A904

FOR ALL ENTRIES IN IT_ZCOSTHIST

WHERE KAPPL = 'V'-------> USECONSTANT

AND KSCHL = 'PR00'-------> USECONSTANT

AND MATNR = IT_ZCOSTHIST-MATNR

ORDER BY DATAB DESCENDING.

SELECT KNUMH DATAB FROM A914 INTO TABLE IT_A914

FOR ALL ENTRIES IN IT_ZCOSTHIST

WHERE KAPPL = 'V'-------> USECONSTANT

AND KSCHL = 'PR00'-------> USECONSTANT

AND MATNR = IT_ZCOSTHIST-MATNR

ORDER BY DATAB DESCENDING.

SELECT KNUMH DATAB FROM A005 INTO TABLE IT_A005

FOR ALL ENTRIES IN IT_ZCOSTHIST

WHERE KAPPL = 'V'-------> USECONSTANT

AND KSCHL = 'PR00'-------> USECONSTANT

AND MATNR = IT_ZCOSTHIST-MATNR.

SORT IT_A005 BY DATAB DESCENDING.

SELECT KNUMH DATAB FROM A006 INTO TABLE IT_A006

FOR ALL ENTRIES IN IT_ZCOSTHIST

WHERE KAPPL = 'V'-------> USECONSTANT

AND KSCHL = 'PR00'-------> USECONSTANT

AND MATNR = IT_ZCOSTHIST-MATNR.

SORT IT_A006 BY DATAB DESCENDING.

SELECT KNUMH DATAB FROM A004 INTO TABLE IT_A004

FOR ALL ENTRIES IN IT_ZCOSTHIST

WHERE KAPPL = 'V'

AND KSCHL = 'PR00'

AND MATNR = IT_ZCOSTHIST-MATNR.

SORT IT_A004 BY DATAB DESCENDING.

ENDIF.

LOOP AT IT_ZCOSTHIST .

Compare zcosthist prices for PBTS,PSDI and PCSC with the prices
in VK13
check for the condition record in the access tables A118,A005,
A903,A904,A006,A004,A914 and A029 and take the price from KONP
based on the condition record number, compare the
prices if the prices are not equal move the record to it_zcosthistm
for displaying or updating the records
set cdate,ctime and cuser in zcosthist for the latest record of
each material by taking the latest record in cdhdr based on the
condition number

FOUND = 0.

MOVE-CORRESPONDING IT_ZCOSTHIST TO IT_ZCOSTHISTM.

READ TABLE IT_A118 INTO WA_A118 INDEX 1.

IF SY-SUBRC EQ 0.

FOUND = 1.

KNUMH = WA_A118-KNUMH.

ELSE.

READ TABLE IT_A904 INTO WA_A904 INDEX 1.

IF SY-SUBRC EQ 0.

FOUND = 1.

KNUMH = WA_A904-KNUMH.

ELSE.

READ TABLE IT_A914 INTO WA_A914 INDEX 1.

IF SY-SUBRC EQ 0.

FOUND = 1.

KNUMH = WA_A914-KNUMH.

ELSE.

READ TABLE IT_A005 INTO WA_A005 INDEX 1.

IF SY-SUBRC EQ 0.

FOUND = 1.

KNUMH = WA_A005-KNUMH.

ELSE.

READ TABLE IT_A006 INTO WA_A006 INDEX 1.

IF SY-SUBRC EQ 0.

FOUND = 1.

KNUMH = WA_A006-KNUMH.

ELSE.

READ TABLE IT_A004 INTO WA_A004 INDEX 1.

IF SY-SUBRC EQ 0.

FOUND = 1.

KNUMH = WA_A004-KNUMH.

ENDIF.

IF FOUND EQ 1.

SELECT SINGLE * FROM KONP WHERE KNUMH = KNUMH.

IF SY-SUBRC EQ 0.

SELECT USERNAME UDATE UTIME FROM CDHDR INTO TABLE IT_CDHDR

WHERE OBJECTCLAS = 'COND_A'

AND OBJECTID = KNUMH

ORDER BY UDATE UTIME DESCENDING.

IF SY-SUBRC = 0.

READ TABLE IT_CDHDR INTO WA_CDHDR INDEX 1.

IT_ZCOSTHIST-CDATE = WA_CDHDR-UDATE.

IT_ZCOSTHIST-CTIME = WA_CDHDR-UTIME.

IT_ZCOSTHIST-CUSER = WA_CDHDR-USERNAME.

MODIFY IT_ZCOSTHIST INDEX SY-TABIX.

ENDIF.

IF ( IT_ZCOSTHIST-LIST_PRICE <> KONP-KBETR ) .

IT_ZCOSTHISTM-ACTUALPRICE = KONP-KBETR.

APPEND IT_ZCOSTHISTM.

ENDIF.

ENDLOOP.

modify zcosthist to set cdate,ctime and cuser

MODIFY ZCOSTHIST FROM TABLE IT_ZCOSTHIST.

IF R1 = 'X'.

to display records from zcosthistm , a record will be inserted into
it_zcosthistm if the price in zcosthist is different from vk13 price

SKIP 2.

WRITE 😕 'Comparison of ZCOSTHIST prices with the VK13 prices'.

SKIP 2.

IF IT_ZCOSTHISTM[] IS INITIAL.

WRITE :/'No differences in the prices'.

SKIP 2.

ELSE.

WRITE :/5 'MATNR', 25 'BUKRS' , 35 'DATERUN' , 55 'LIST PRICE'

,75 'ACTUAL PRICE'.

SKIP 1.

LOOP AT IT_ZCOSTHISTM.

WRITE :/5 IT_ZCOSTHISTM-MATNR, 25 IT_ZCOSTHISTM-BUKRS, 35

IT_ZCOSTHISTM-DATERUN , 55 IT_ZCOSTHISTM-LIST_PRICE

LEFT-JUSTIFIED,75 IT_ZCOSTHISTM-ACTUALPRICE

LEFT-JUSTIFIED.

ENDLOOP.

ENDIF.

IF R2 = 'X'.

to update zcosthist with the latest prices

LOOP AT IT_ZCOSTHISTM.

IT_ZCOSTHISTM-LIST_PRICE = IT_ZCOSTHISTM-ACTUALPRICE.

UPDATE ZCOSTHIST SET LIST_PRICE = IT_ZCOSTHISTM-LIST_PRICE

WHERE MATNR = IT_ZCOSTHISTM-MATNR

AND BUKRS = IT_ZCOSTHISTM-BUKRS

AND DATERUN = IT_ZCOSTHISTM-DATERUN.

COMMIT WORK.

ENDLOOP.

Message was edited by:

Ashwani Mittal

Former Member · ‎2007 Feb 28

Hey Deepthi,

You Should NEVER use SELECT statements inside the LOOP. This puts great impact on the performance.

At the End of the code when FOUND = 1, Please see if you can remove those SELECT statements also from the LOOP as i am not sure hat exactl the requirement is?

and ALWAYS use 'EQ' instead of '=' as i told u before.

DECLARE CONSTANTS wherever u used then in your coding.It shows the good coding practise and will be easy for other who go thru you's code.

ALWAYS check IF ITAB has entries before using FOR ALL ENTRIES otherwise it will pick al the data from the database and canresult in SHORT DUMP.

hope this will help.

Thanks n Regards,

Ashwani

Former Member · ‎2007 Feb 28

is it solved Raj?

Former Member · ‎2007 Feb 28

I am getting an error at this select statement. object id length is 90 and it_cond-knumh length is 10 . the error is because of the different lengths .

IF NOT IT_COND[] IS INITIAL.

SELECT USERNAME UDATE UTIME FROM CDHDR INTO TABLE IT_CDHDR FOR ALL ENTRIES IN IT_COND WHERE OBJECTCLAS = 'COND_A' AND OBJECTID = IT_COND-KNUMH.

ENDIF.

Former Member · ‎2007 Feb 28

Hello Deepthi,

U can make use of the FM READ_HEADER_CHANGES to read data from the CDHDR Table. It will faster also.

If useful reward.

Vasanth

Former Member · ‎2007 Feb 28

SELECT USERNAME UDATE UTIME FROM CDHDR INTO TABLE IT_CDHDR FOR ALL ENTRIES IN IT_COND WHERE OBJECTCLAS EQ 'COND_A' AND OBJECTID+0(10) EQ IT_COND-KNUMH.

ALWAYS use 'EQ' as i ave used in the above query for permonace.

Try uing it f dat works. i m not on the system so can't test it.

Ashwani

Message was edited by:

Ashwani Mittal

Former Member · ‎2007 Feb 28

objectid+0(10) is giving an error

Former Member · ‎2007 Feb 28

wat exactly error is ? it miet b the spacing prblem try using

objectid+0 (10)

Thanks

Ashwani

Message was edited by:

Ashwani Mittal

Former Member · ‎2007 Feb 28

I tried it in every possible way , +(10) is not working for this statement at all . i am trying to use the FM ,i see it as CHANGEDOCUMENT_READ_HEADERS. If i am using a set of object ids (i have it in it_cond) , how do i call the function module in a loop.

Former Member · ‎2007 Feb 28

Yes U need to call the FM in the LOOP AT IT_COND

Vasanth

Former Member · ‎2007 Feb 28

if you are sure of the length that its going to be 10 then declare the it_cond-knumh as 10 type c.

Is your problem solved or else try the above ?

Former Member · ‎2007 Feb 28

The performance has become even more worse now.

SELECT KNUMH

DATAB

FROM A005

INTO TABLE IT_A005

FOR ALL ENTRIES IN IT_ZCOSTHIST

WHERE KAPPL = 'V'

AND KSCHL = 'PR00'

AND MATNR = IT_ZCOSTHIST-MATNR.

SORT IT_A005 BY MATNR ASCENDING DATAB DESCENDING.

DELETE ADJACENT DUPLICATES FROM IT_A005 COMPARING MATNR.

A005 is a pooled table . How do i change this statement to improve the performance. The control is not coming out of this statement

Former Member · ‎2007 Feb 28

Is ur problem of different lenghts solved?

plz. chech the field status in table A005.

which comes after which ... order the fields in WHERE clause in the same manner as they are in Table A005.

hope this will help

Thanks

Ashwani

Message was edited by:

Ashwani Mittal

Former Member · ‎2007 Feb 28

your fields in the select should be in the order of the database table and also the fields in the where class also before all this the internal table it_ZCOSTHIST should be sorted for better performance.

I am sure writing a select outside the loop is much much better. It should improve. check it with these modifications too.

Former Member · ‎2007 Feb 28

.

Former Member · ‎2007 Mar 01

hey if its done or not ?

SDN is to help each other not to confuse each other.

Thanks,

Ashwani

Former Member · ‎2007 Mar 01

Hey deepthi,

Let us know if your problem is solved. If yes, close the thread and award points. Don't let it hang in the air.

By Category

Related Content

Activity Groups

Industry Groups

Influence and Feedback Groups

Interest Groups

Location Groups

Customer Only Groups

Forums

Related Resources

Products

Learning and Support

About

My SAP Profile

My SAP Profile

report performance