topic Re: Performance problem with CO-PA process in Application Development and Automation Discussions

Performance problem with CO-PA process

Former Member — Mon, 29 Jan 2007 20:55:39 GMT

Hello

We’re running a CO-PA process and it’s taking a long time to execute (14 hours).

Using transaction ST05 we got the sql statement with performance problem. The statement is:

SELECT

/*+

FIRST_ROWS (1)

FROM

"CE4PAFL"

WHERE

"MANDT" = :A0 AND "AKTBO" = :A1 AND "KNDNR" = :A2 AND "ARTNR" = :A3 AND

"FKART" = :A4 AND "KAUFN" = :A5 AND "KDPOS" = :A6 AND "AUFNR" = :A7 AND

"BUKRS" = :A8 AND "KOKRS" = :A9 AND "WERKS" = :A10 AND "GSBER" = :A11 AND

"VKORG" = :A12 AND "VTWEG" = :A13 AND "SPART" = :A14 AND "PRCTR" = :A15 AND

"PPRCTR" = :A16 AND "KSTRG" = :A17 AND "PSPNR" = :A18 AND "MEINH" = :A19 AND

"BRSCH" = :A20 AND "BZIRK" = :A21 AND "KDGRP" = :A22 AND "KMVKBU" = :A23 AND

"KMVTNR" = :A24 AND "MATKL" = :A25 AND "VKGRP" = :A26 AND "KMWNHG" = :A27 AND

"BRAN1" = :A28 AND "LAND1" = :A29 AND "VHART" = :A30 AND "EXTWG" = :A31 AND

"KTGRM" = :A32 AND "KONDM" = :A33 AND "KWSVME_ME" = :A34 AND "VVABS_ME" =

:A35 AND "ABSMG_ME" = :A36 AND "VVVDI_ME" = :A37 AND ROWNUM <= :A38

And values for each field are:

MANDT A0(CH,3) = 700

AKTBO A1(CH,1) = X

KNDNR A2(CH,10) =

ARTNR A3(CH,18) =

FKART A4(CH,4) =

KAUFN A5(CH,10) =

KDPOS A6(NU,6) = 000000

AUFNR A7(CH,12) =

BUKRS A8(CH,4) = FDIS

KOKRS A9(CH,4) = COFL

WERKS A10(CH,4) =

GSBER A11(CH,4) = 10

VKORG A12(CH,4) =

VTWEG A13(CH,2) =

SPART A14(CH,2) =

PRCTR A15(CH,10) = 0000005800

PPRCTR A16(CH,10) =

KSTRG A17(CH,12) =

PSPNR A18(NU,8) = 00000000

MEINH A19(CH,3) =

BRSCH A20(CH,4) = 2116

BZIRK A21(CH,6) =

KDGRP A22(CH,2) = 00

KMVKBU A23(CH,4) =

KMVTNR A24(NU,8) = 00000000

MATKL A25(CH,9) =

VKGRP A26(CH,3) = SIS

KMWNHG A27(NU,2) = 00

BRAN1 A28(CH,10) =

LAND1 A29(CH,3) =

VHART A30(CH,4) = 0403

EXTWG A31(CH,18) =

KTGRM A32(CH,2) =

KONDM A33(CH,2) =

KWSVME_ME A34(CH,3) =

VVABS_ME A35(CH,3) =

ABSMG_ME A36(CH,3) =

VVVDI_ME A37(CH,3) =

ROWNUM A38(I4,1) = 1

Table CE4PAFL has four indexes

~1 ~Z01 ~Z02 ~Z03

MANDT MANDT MANDT MANDT

AKTBO AKTBO AKTBO AKTBO

PRCTR KOKRS BUKRS PRCTR

KNDNR BUKRS KOKRS BRSCH

ARTNR VTWEG GSBER KDGRP

BUKRS VKGRP PRCTR VKGRP

WERKS VHART BRSCH VHART

VTWEG VKGRP

This sql statement is using index ~1 (as seen in trace generated by ST05) but this statement contains fields with null values (KNDNR, ARTNR, WERKS, VTWEG), annulling the index. I think the select must use index ~Z03 because all fields have values in the statement.

The select statement cannot be modified because is automatically generated by customizing, and I need to know how to modify the indexes or some tip to improve the performance of this select.

Thanks in advance

Ronald

Re: Performance problem with CO-PA process

Former Member — Mon, 29 Jan 2007 21:00:48 GMT

What transaction or program is being run here?

Rob

Re: Performance problem with CO-PA process

Former Member — Mon, 29 Jan 2007 21:23:26 GMT

Hi Rob, the transaction we're using is KEU5 - Execute Actual Assessment.

Thanks

Re: Performance problem with CO-PA process

Former Member — Mon, 29 Jan 2007 21:35:27 GMT

There are a number of notes regarding performance for this transaction. Start with 416507 and perhaps 122104 will also help.

Rob

Re: Performance problem with CO-PA process

christian_wohlfahrt — Tue, 30 Jan 2007 08:20:24 GMT

Hi Ronald!

How old is the statistic for this table? If the statistic was last updated before the new index Z3 was created, then a cost based optimizer will ignore the index caused by bad statistics. Have a look at DB20 to check the date / trigger a refresh. Also after hugh table changes a refresh might help to get the 'right' index.

Regards,

Christian

Re: Performance problem with CO-PA process

christian_wohlfahrt — Tue, 30 Jan 2007 08:28:34 GMT

I just saw, that indices 1 and Z3 start with the same fields - might be difficult for an optimizer to get the right one. Maybe you have better results when changing (modifying!) index 1 - e.g. re-order the fields and move AKTBO and PRCTR behind KNDNR and ARTNR. Then empty fields in the first places might make the difference. Also removing them can help: selects, which have KNDNR and ARTNR will still have a fast access, but your Z3 will now be prominent for the other way.

Thinking about this: removing might be best - if you have already KNDNR and ARTNR, PRCTR might be fixed -> does not shrink the number of possible entries. If you just have PRCTR, then Z3 will do the job for you, too.

Re: Performance problem with CO-PA process

Former Member — Thu, 01 Feb 2007 16:29:15 GMT

Hi Christian. The statistics are up to date, exactly from past sunday, because the process runs twice a week.

Thanks for your answer.

Ronald

Re: Performance problem with CO-PA process

Former Member — Thu, 01 Feb 2007 16:35:33 GMT

Christian, the situation here is index _{1 is used in other processess, so I can't modify it at this time. Now we're trying creating a new index with characteristics of both indexes (}1 and ~Z3, similar to your advice) to see if the new index is used, but we'll get results today in the afternoon.

Thanks

Re: Performance problem with CO-PA process

Former Member — Thu, 01 Feb 2007 16:49:11 GMT

Heve you checked the notes? If SAP has an answer, I'd try that before creating an index. If none of the notes help, create a customer message.

Rob