performance problem on Function Module

[Former Member]

Hi Experts,

We are facing performance problem on RKWTPBTC program's background job when i checked from sm50, job status is sequential read from table AUFK and its oracle process is using half of all cpu, i've updated the table statics but nothing changed,

i displayed the source code from oracle sessions; source code is a Function Module (customer developed) ;

data: aufex like aufk-aufex.

aufex = ''.

select single aufex into aufex

from aufk

where objnr = objnr.

RTABLE_VAL-period = period_from.

RTABLE_VAL-value = aufex.

append RTABLE_VAL.

ENDFUNCTION.

I've run the abap trace from se30, and the result is execution of DB %99,8 , hints shows the most Gross value is in the same Function ;

Job completed in 3 days, i am not a abap developer, could you please tell me your recommendations,

Best Regards,

[Former Member]

Hi again,

I've uploded printscreen of the execution plan sql statement following link;

http://img143.imageshack.us/i/sql.jpg/%5D%5BIMG%5Dhttp://img143.imageshack.us/img143/3043/sql.th.jpg...

Regards

[Former Member]

Hello atakan,

I think you wrote only half of the code , AUFK is big table if i am not wrong then abaper used there nested select condition and also he was not buffered the select query so this problem is comming.But if you corrected these problems then also it will increase efficency by 10-12 % only.

For better result you have to create secondary index on field OBJNR because of which the access time will resuce to 75%.

Try these options , definately this will help you.

Regards,

Shrikant.

[Former Member]

Hi Atakan,

Since OBJNR is not an indexed field of table AUFK, you read nearly the whole table for each call of this FM.

So you mainly have 2 solutions :

1 - rewrite your code by using an indexed field of AUFK

2 - create an index on MANDT/OBJNR for this table

Best regards,

Samuel

[Former Member]

Thank you very much for your recommendations, I informed the customer for tuning this Function,

AUFK has 148.160 rows, and DISTINCT for OBJNR field is to high,

SQL> SELECT COUNT(DISTINCT OBJNR) FROM sapr3.aufk;

COUNT(DISTINCTOBJNR)

-

---

146852

Can we provide a good improvement via MANDT/OBJNR index ?

Regards

[Former Member]

Yes : higher is the count of distinct value for some field, better is the index on this field, since for each value of OBJNR you will read less lines in the newly created index!

Best regards,

Samuel

[Former Member]

Hi atakan yavuz,

Try asking your developer to use this logic.

DATA: aufex LIKE aufk-aufex.

SELECT SINGLE aufex 
  INTO aufex
  FROM aufk
  WHERE aufnr = objnr+2(12).
  
IF sy-subrc EQ 0.

  rtable_val-value = aufex.

ELSE.

  CLEAR rtable_val-value.

ENDIF.

rtable_val-period = period_from.

APPEND rtable_val.

[Former Member]

Is this SELECT in standard SAP code or is it in your custom code?

Rob

[Former Member]

Hi Rob,

This is customer developed Function which is starting with Z,

It's FunctionPool is SAPLZCOABC, backgorund job is standart SAP program RKWTPBTC,

Regards

[Former Member]

Well, I think Mark has pointed you in the right direction. The only thing I would add is that there may be different logic to construct object numbers depending on what type of object you are retrieving.

Rob