Solved: The Function Module PERFORMANCE is VERY VERY BAD, ...

Former Member · ‎2008 Sep 23

Hi all,

We have a Function Module for the Custom Extractor to get the data from VBRK VBFA, FPLT & FPLA tables and the performance is VERY VERY BAD. Could you please help me to improve the performance.

Thanks in advance.

Regards,

Venkat.

FUNCTION zbi_ccs_bp01 .

*"----

types

TYPES:BEGIN OF t_vbeln_range,

sign TYPE c LENGTH 1,

option TYPE c LENGTH 2,

low TYPE vbuk-vbeln,

high TYPE vbuk-vbeln,

END OF t_vbeln_range.

TYPES:BEGIN OF t_vbeln_vf_range,

sign TYPE c LENGTH 1,

option TYPE c LENGTH 2,

low TYPE vbrk-vbeln,

high TYPE vbrk-vbeln,

END OF t_vbeln_vf_range.

Declaration of internal table and work areas

DATA:l_r_vbeln TYPE STANDARD TABLE OF t_vbeln_range,

l_r_vbeln_vf TYPE STANDARD TABLE OF t_vbeln_vf_range,

l_wa_vbeln_vf TYPE t_vbeln_vf_range,

l_wa_vbeln TYPE t_vbeln_range,

l_whereclause TYPE string.

CONSTANTS: l_c_pack TYPE sy-tabix VALUE '1000'.

Maximum number of lines for DB table

STATICS: s_s_if TYPE srsc_s_if_simple,

s_counter_datapakid LIKE sy-tabix,

v_nodata TYPE c. " No Data available Flag

Initialization mode (first call by SAPI) or data transfer mode
(following calls) ?

IF i_initflag = sbiwa_c_flag_on.

Check DataSource validity

CASE i_dsource.

WHEN 'ZBWP_CCS_BP01'.

WHEN OTHERS.

IF 1 = 2. MESSAGE e009(r3). ENDIF.

this is a typical log call. Please write every error message like this

log_write 'E' "message type

'R3' "message class

'009' "message number

i_dsource "message variable 1

' '. "message variable 2

RAISE error_passed_to_mess_handler.

ENDCASE.

APPEND LINES OF i_t_select TO s_s_if-t_select.

Fill parameter buffer for data extraction calls

s_s_if-requnr = i_requnr.

s_s_if-dsource = i_dsource.

s_s_if-maxsize = i_maxsize.

APPEND LINES OF i_t_fields TO s_s_if-t_fields.

ELSE. "Initialization mode or data extraction ?

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

Data transfer: First Call OPEN CURSOR + FETCH
Following Calls FETCH only

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

First data package -> OPEN CURSOR

IF s_counter_datapakid = 0.

Determine number of database records to be read per FETCH statement
from input parameter I_MAXSIZE. If there is a one to one relation
between DataSource table lines and database entries, this is trivial.
In other cases, it may be impossible and some estimated value has to
be determined.

Read ranges table for Billing Doc no.

LOOP AT s_s_if-t_select INTO wa_select

WHERE fieldnm = 'VBELN_VF'.

l_wa_vbeln_vf-sign = wa_select-sign.

l_wa_vbeln_vf-option = wa_select-option.

l_wa_vbeln_vf-low = wa_select-low.

l_wa_vbeln_vf-high = wa_select-high.

APPEND l_wa_vbeln_vf TO l_r_vbeln_vf.

CLEAR l_wa_vbeln_vf.

ENDLOOP.

Read ranges table for Billing Doc no.

LOOP AT s_s_if-t_select INTO wa_select

WHERE fieldnm = 'VBELN'.

l_wa_vbeln-sign = wa_select-sign.

l_wa_vbeln-option = wa_select-option.

l_wa_vbeln-low = wa_select-low.

l_wa_vbeln-high = wa_select-high.

APPEND l_wa_vbeln TO l_r_vbeln.

CLEAR l_wa_vbeln.

ENDLOOP.

Delta process

SELECT SINGLE logsys

INTO t000-logsys

FROM t000

WHERE mandt = sy-mandt.

IF sy-subrc = 0.

CLEAR : roosgendlm, l_whereclause.

SELECT SINGLE *

FROM roosgendlm

INTO roosgendlm

WHERE oltpsource = i_dsource

AND slogsys = t000-logsys.

IF sy-subrc = 0 AND i_requnr NE 'TEST'..

v_date = sy-datum.

v_date1 = roosgendlm-deltaid+0(8).

v_time1 = roosgendlm-deltaid+8(6).

v_time1 = v_time1 - 1. "Safety limit of 30 min

v_date2 = v_date1 + 1.

CONCATENATE 'vbeln IN l_r_vbeln_vf' "Billing

'AND ( ( erdat = v_date1 AND erzet >= v_time1 ) OR erdat GE v_date2 ).'

INTO l_whereclause

SEPARATED BY space.

ELSE. " Init load

CONCATENATE 'vbeln IN l_r_vbeln_vf' "Billing

INTO l_whereclause SEPARATED BY space.

ENDIF.

Get billing document header and item details

REFRESH i_billing.

SELECT vbeln

waerk

netwr

kunag

vkont

FROM vbrk

PACKAGE SIZE l_c_pack

APPENDING TABLE i_billing

WHERE (l_whereclause).

IF sy-subrc EQ 0.

SORT i_billing.

ENDIF.

Get Document Flow

REFRESH i_vbfa.

SELECT vbelv

posnv

vbeln

posnn

vbtyp_n

vbtyp_v

fplnr

fpltr

FROM vbfa

APPENDING TABLE i_vbfa

FOR ALL ENTRIES IN i_billing

WHERE vbelv IN l_r_vbeln

AND vbeln EQ i_billing-vbeln

AND vbtyp_n EQ 'M'

AND vbtyp_v EQ 'G'.

IF sy-subrc EQ 0.

SORT i_vbfa.

ENDIF.

Get the billing plan details

REFRESH i_plan.

SELECT a~fplnr

a~fpltr

b~fptyp

b~bedat

b~endat

b~vbeln

APPENDING TABLE i_plan

FROM fplt AS a INNER JOIN fpla AS b

ON a_{fplnr = b}fplnr

FOR ALL ENTRIES IN i_vbfa

WHERE a~fplnr EQ i_vbfa-fplnr

AND b~vbeln EQ i_vbfa-vbelv.

IF sy-subrc EQ 0 .

SORT i_plan BY fplnr fpltr.

ENDIF.

LOOP AT i_billing INTO wa_billing.

APPEND wa_billing to i_billing_final.

ENDLOOP.

REFRESH i_billing .

ENDSELECT.

ENDIF. "First data package ?

Fetch records into interface table.
named E_T_'Name of extract structure'.

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

FETCH NEXT CURSOR S_CURSOR
APPENDING CORRESPONDING FIELDS
OF TABLE E_T_DATA
PACKAGE SIZE S_S_IF-MAXSIZE.

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

v_currsize = '000000'.

IF v_nodata = 'Y'.

RAISE no_more_data.

ENDIF.

v_maxrec_bp01 = 1.

SORT i_plan BY fplnr fpltr.

SORT i_vbfa.

SORT i_billing_final.

DO.

r_tabix = r_tabix + v_maxrec_bp01.

Read plan details

READ TABLE i_plan INTO wa_plan INDEX r_tabix.

IF sy-subrc NE 0.

v_nodata = 'Y'.

EXIT.

ELSE.

wa_zbwp_ccs_bp01-fplnr = wa_plan-fplnr.

wa_zbwp_ccs_bp01-fpltr = wa_plan-fpltr.

wa_zbwp_ccs_bp01-bedat = wa_plan-bedat.

wa_zbwp_ccs_bp01-endat = wa_plan-endat.

wa_zbwp_ccs_bp01-waers = wa_plan-waers.

wa_zbwp_ccs_bp01-vbeln = wa_plan-vbeln.

Read billing details by billing plan and billing plan item

READ TABLE i_vbfa INTO wa_vbfa

WITH KEY fplnr = wa_plan-fplnr

fpltr = wa_plan-fpltr

vbelv = wa_plan-vbeln binary search.

IF sy-subrc EQ 0.

READ TABLE i_billing_final INTO wa_billing

WITH KEY vbeln = wa_vbfa-vbeln.

IF sy-subrc EQ 0.

wa_zbwp_ccs_bp01-vbeln_vf = wa_vbfa-vbeln.

wa_zbwp_ccs_bp01-fkart = wa_billing-fkart.

wa_zbwp_ccs_bp01-erdat = wa_billing-erdat.

wa_zbwp_ccs_bp01-kunag = wa_billing-kunag.

ENDIF.

The details of document currency,company code,sales org,distribution
channel,division needs to be displayed for all the sales release,
partially released and open

IF wa_plan-vbeln IS NOT INITIAL.

wa_zbwp_ccs_bp01-waerk = wa_billing-waerk.

wa_zbwp_ccs_bp01-vkorg = wa_billing-vkorg.

wa_zbwp_ccs_bp01-vtweg = wa_billing-vtweg.

wa_zbwp_ccs_bp01-spart = wa_billing-spart.

ENDIF.

Read contract number by billing plan

READ TABLE i_vbfa INTO wa_vbfa

WITH KEY fplnr = wa_plan-fplnr

vbelv = wa_plan-vbeln binary search.

IF sy-subrc EQ 0.

READ TABLE i_billing_final INTO wa_billing

WITH KEY vbeln = wa_vbfa-vbeln.

IF sy-subrc EQ 0.

wa_zbwp_ccs_bp01-vkont = wa_billing-vkont.

ENDIF.

APPEND wa_zbwp_ccs_bp01 TO e_t_data.

CLEAR wa_zbwp_ccs_bp01.

v_currsize = v_currsize + 1.

IF s_s_if-maxsize LE v_currsize.

CLEAR v_currsize.

EXIT.

ENDIF.

ENDDO.

v_maxrec_bp01 = s_s_if-maxsize.

s_counter_datapakid = s_counter_datapakid + 1.

ENDIF. "Initialization mode or data extraction

BH2408 · ‎2008 Sep 23

Hi Venkat,

You used the For all Entries Here we need to consider the these things,

1.Internal table should not be empty if it is empty in that case where condition is fail, I mean to say how many records are in the table select will retrive that many records.if 10,000 u have it will retrive 10000 and where condition will be fail there.Try to check this.

2. Delete Adjacent Duplicates

3.Avoid record update one by one

3. Sort the internal table provides faster access to the data records .

4.For Read internal table try to go for Binary search

5.try to create the secondary Index

6.Dynamic field assignment and Data manipulation(Field symbols)

7. Goto St05 SQL TRACE and Analize the performance.

Former Member · ‎2008 Sep 23

Why don't you run ST05 on this to see if you can narrow down where the problem is before posting your entire code?

Rob

BH2408 · ‎2008 Sep 23

Hi Venkat,

You used the For all Entries Here we need to consider the these things,

1.Internal table should not be empty if it is empty in that case where condition is fail, I mean to say how many records are in the table select will retrive that many records.if 10,000 u have it will retrive 10000 and where condition will be fail there.Try to check this.

2. Delete Adjacent Duplicates

3.Avoid record update one by one

3. Sort the internal table provides faster access to the data records .

4.For Read internal table try to go for Binary search

5.try to create the secondary Index

6.Dynamic field assignment and Data manipulation(Field symbols)

7. Goto St05 SQL TRACE and Analize the performance.

Former Member · ‎2008 Sep 24

Hi,

check your code with SE30 (ABAP Runtime analysis).

Now you are only guessing what's going bad.

Invest time to learn how to analyze your code.

If the SQL is the problem you can narrow down

With ST05 (as already Rob recommded) you can focus on

the SQL statements and their execution plans.

Bye

yk

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

The Function Module PERFORMANCE is VERY VERY BAD, please help me ???