topic Re: Optimize BW ABAP start routine in Application Development and Automation Discussions

Optimize BW ABAP start routine

Former Member — Fri, 21 Oct 2011 07:56:13 GMT

Hi guys

I have created a start routine in my update rules in SAP BW. The problem is that it takes forever and I do not know how to optimize the code further.

TYPES:
BEGIN OF ZPUR_O04,
DOC_NUM TYPE /BI0/OIDOC_NUM,
DOC_ITEM TYPE /BI0/OIDOC_ITEM,
PROCESSKEY TYPE /BI0/OIPROCESSKEY,
DOC_DATE TYPE /BI0/OIDOC_DATE,
ZPODELDAT TYPE DATS,
ORDER_QUAN TYPE /BI0/OIORDER_QUAN,
NETPRICE TYPE /BI0/OINETPRICE,
PRICE_UNIT TYPE /BI0/OIPRICE_UNIT,
PO_UNIT TYPE /BI0/OIPO_UNIT,
LOC_CURRCY TYPE /BI0/OILOC_CURRCY,
END OF ZPUR_O04.

DATA:
L_S_ZPUR_O04 TYPE ZPUR_O04,
L_S_DP_LINE TYPE DATA_PACKAGE_STRUCTURE.

STATICS:
L_TH_ZPUR_O04 TYPE TABLE OF ZPUR_O04 WITH KEY DOC_NUM
DOC_ITEM.

IF L_TH_ZPUR_O04 IS INITIAL.

 SELECT
 /BIC/AZPUR_O0400~DOC_NUM
 /BIC/AZPUR_O0400~DOC_ITEM
 /BIC/AZPUR_O0400~PROCESSKEY
 /BIC/AZPUR_O0400~DOC_DATE
 /BIC/AZPUR_O0400~/BIC/ZPODELDAT
 /BIC/AZPUR_O0400~ORDER_QUAN
 /BIC/AZPUR_O0400~NETPRICE
 /BIC/AZPUR_O0400~PRICE_UNIT
 /BIC/AZPUR_O0400~PO_UNIT
 /BIC/AZPUR_O0400~LOC_CURRCY
 INTO TABLE L_TH_ZPUR_O04
 FROM
 /BIC/AZPUR_O0400.

ENDIF.

LOOP AT DATA_PACKAGE INTO L_S_DP_LINE.

READ TABLE L_TH_ZPUR_O04 INTO L_S_ZPUR_O04
  WITH TABLE KEY DOC_NUM = L_S_DP_LINE-OI_EBELN
  DOC_ITEM = L_S_DP_LINE-OI_EBELP+1.

IF SY-SUBRC = 0.
  L_S_DP_LINE-DOC_DATE = L_S_ZPUR_O04-DOC_DATE.
  L_S_DP_LINE-/BIC/ZPODELDAT = L_S_ZPUR_O04-ZPODELDAT.
  L_S_DP_LINE-ORDER_QUAN = L_S_ZPUR_O04-ORDER_QUAN.
  L_S_DP_LINE-NETPRICE = L_S_ZPUR_O04-NETPRICE.
  L_S_DP_LINE-PRICE_UNIT = L_S_ZPUR_O04-PRICE_UNIT.
  L_S_DP_LINE-PO_UNIT = L_S_ZPUR_O04-PO_UNIT.
  L_S_DP_LINE-LOC_CURRCY = L_S_ZPUR_O04-LOC_CURRCY.

  MODIFY  DATA_PACKAGE FROM L_S_DP_LINE.

ENDIF.

ENDLOOP.

Did I make some "mistakes" somewhere?

Could the code be optimized further?

Thanks!

Re: Optimize BW ABAP start routine

yuri_ziryukin — Fri, 21 Oct 2011 09:00:47 GMT

Hello Stefan,

here is my proposal:

TYPES:
BEGIN OF ZPUR_O04,
DOC_NUM TYPE /BI0/OIDOC_NUM,
DOC_ITEM TYPE /BI0/OIDOC_ITEM,
PROCESSKEY TYPE /BI0/OIPROCESSKEY,
DOC_DATE TYPE /BI0/OIDOC_DATE,
ZPODELDAT TYPE DATS,
ORDER_QUAN TYPE /BI0/OIORDER_QUAN,
NETPRICE TYPE /BI0/OINETPRICE,
PRICE_UNIT TYPE /BI0/OIPRICE_UNIT,
PO_UNIT TYPE /BI0/OIPO_UNIT,
LOC_CURRCY TYPE /BI0/OILOC_CURRCY,
END OF ZPUR_O04.
 
FIELD-SYMBOLS: <F_S_DP_LINE> TYPE DATA_PACKAGE_STRUCTURE,
<F_S_ZPUR_O04> TYPE ZPUR_O04.
 
STATICS:
L_TH_ZPUR_O04 TYPE SORTED TABLE OF ZPUR_O04 WITH NON-UNIQUE KEY DOC_NUM
DOC_ITEM.
 
IF L_TH_ZPUR_O04 IS INITIAL.
 
 SELECT
 /BIC/AZPUR_O0400~DOC_NUM
 /BIC/AZPUR_O0400~DOC_ITEM
 /BIC/AZPUR_O0400~PROCESSKEY
 /BIC/AZPUR_O0400~DOC_DATE
 /BIC/AZPUR_O0400~/BIC/ZPODELDAT
 /BIC/AZPUR_O0400~ORDER_QUAN
 /BIC/AZPUR_O0400~NETPRICE
 /BIC/AZPUR_O0400~PRICE_UNIT
 /BIC/AZPUR_O0400~PO_UNIT
 /BIC/AZPUR_O0400~LOC_CURRCY
 INTO TABLE L_TH_ZPUR_O04
 FROM
 /BIC/AZPUR_O0400.
 
ENDIF.
 
LOOP AT DATA_PACKAGE ASSIGNING <F_S_DP_LINE>.
 
READ TABLE L_TH_ZPUR_O04 ASSIGNING <F_S_ZPUR_O04>
  WITH TABLE KEY DOC_NUM = L_S_DP_LINE-OI_EBELN
  DOC_ITEM = L_S_DP_LINE-OI_EBELP+1.
 
IF SY-SUBRC = 0.
  <F_S_DP_LINE>-DOC_DATE = <F_S_ZPUR_O04>-DOC_DATE.
  <F_S_DP_LINE>-/BIC/ZPODELDAT = <F_S_ZPUR_O04>-ZPODELDAT.
  <F_S_DP_LINE>-ORDER_QUAN = <F_S_ZPUR_O04>-ORDER_QUAN.
  <F_S_DP_LINE>-NETPRICE = <F_S_ZPUR_O04>-NETPRICE.
  <F_S_DP_LINE>-PRICE_UNIT = <F_S_ZPUR_O04>-PRICE_UNIT.
  <F_S_DP_LINE>-PO_UNIT = <F_S_ZPUR_O04>-PO_UNIT.
  <F_S_DP_LINE>-LOC_CURRCY = <F_S_ZPUR_O04>-LOC_CURRCY. 
ENDIF.
 
ENDLOOP.

Regards,

Yuri

Re: Optimize BW ABAP start routine

Former Member — Fri, 21 Oct 2011 09:39:57 GMT

Hi Yuri

Your proposal worked amazing! When I tested the dataload yesterday it took +20 hours. Today it seems to be reduced to less than 10 minutes.

I made a very small correction in the SELECT statement myself and corrected a few syntax errors in your code:

TYPES:
BEGIN OF ZPUR_O04,
DOC_NUM TYPE /BI0/OIDOC_NUM,
DOC_ITEM TYPE /BI0/OIDOC_ITEM,
PROCESSKEY TYPE /BI0/OIPROCESSKEY,
DOC_DATE TYPE /BI0/OIDOC_DATE,
ZPODELDAT TYPE DATS,
ORDER_QUAN TYPE /BI0/OIORDER_QUAN,
NETPRICE TYPE /BI0/OINETPRICE,
PRICE_UNIT TYPE /BI0/OIPRICE_UNIT,
PO_UNIT TYPE /BI0/OIPO_UNIT,
LOC_CURRCY TYPE /BI0/OILOC_CURRCY,
END OF ZPUR_O04.

FIELD-SYMBOLS: <F_S_DP_LINE> TYPE DATA_PACKAGE_STRUCTURE,
<F_S_ZPUR_O04> TYPE ZPUR_O04.

STATICS:
L_TH_ZPUR_O04 TYPE SORTED TABLE OF ZPUR_O04 WITH NON-UNIQUE KEY DOC_NUM
DOC_ITEM.

IF L_TH_ZPUR_O04 IS INITIAL.

 SELECT
 /BIC/AZPUR_O0400~DOC_NUM
 /BIC/AZPUR_O0400~DOC_ITEM
 /BIC/AZPUR_O0400~PROCESSKEY
 /BIC/AZPUR_O0400~DOC_DATE
 /BIC/AZPUR_O0400~/BIC/ZPODELDAT
 /BIC/AZPUR_O0400~ORDER_QUAN
 /BIC/AZPUR_O0400~NETPRICE
 /BIC/AZPUR_O0400~PRICE_UNIT
 /BIC/AZPUR_O0400~PO_UNIT
 /BIC/AZPUR_O0400~LOC_CURRCY
 INTO TABLE L_TH_ZPUR_O04
 FROM
 /BIC/AZPUR_O0400
 WHERE /BIC/AZPUR_O0400~PROCESSKEY = '001'.

ENDIF.

LOOP AT DATA_PACKAGE ASSIGNING <F_S_DP_LINE>.

READ TABLE L_TH_ZPUR_O04 ASSIGNING <F_S_ZPUR_O04>
  WITH TABLE KEY DOC_NUM = <F_S_DP_LINE>-OI_EBELN
  DOC_ITEM = <F_S_DP_LINE>-OI_EBELP+1.

IF SY-SUBRC = 0.
  <F_S_DP_LINE>-DOC_DATE = <F_S_ZPUR_O04>-DOC_DATE.
  <F_S_DP_LINE>-/BIC/ZPODELDAT = <F_S_ZPUR_O04>-ZPODELDAT.
  <F_S_DP_LINE>-ORDER_QUAN = <F_S_ZPUR_O04>-ORDER_QUAN.
  <F_S_DP_LINE>-NETPRICE = <F_S_ZPUR_O04>-NETPRICE.
  <F_S_DP_LINE>-PRICE_UNIT = <F_S_ZPUR_O04>-PRICE_UNIT.
  <F_S_DP_LINE>-PO_UNIT = <F_S_ZPUR_O04>-PO_UNIT.
  <F_S_DP_LINE>-LOC_CURRCY = <F_S_ZPUR_O04>-LOC_CURRCY.
ENDIF.

ENDLOOP.

Is it possible for you to explain why your code performs so much better? To me the changes looks minimal but the performance change was HUGE!

Stefan

Re: Optimize BW ABAP start routine

yuri_ziryukin — Fri, 21 Oct 2011 12:29:10 GMT


Hi Yuri
> 
> Your proposal worked amazing! When I tested the dataload yesterday it took +20 hours. Today it seems to be reduced to less than 10 minutes.
> 
> Is it possible for you to explain why your code performs so much better? To me the changes looks minimal but the performance change was HUGE!
> 
> BR
> Stefan

Hello Stefan, sure.

Basically there are 2 changes.

1) I have declared your buffer table as a sorted table. For the sorted tables the READ TABLE statement automatically uses binary search (assuming key fields are specified).

2) I have used field symbols instead of work areas. Field symbols are references and allow to change entries in a loop in a fast and effective way.

Regards,

Yuri

Re: Optimize BW ABAP start routine

Former Member — Fri, 21 Oct 2011 13:05:54 GMT

Thanks a lot Yuri!

I will keep that in mind next time I will have to deal with all this ABAP stuff.

Stefan

Re: Optimize BW ABAP start routine

volker_borowski2 — Thu, 27 Oct 2011 16:48:19 GMT

Very impressive result!

Yuri, did you ever measure, if it makes a difference to fill an

ABAP-sorted table from a simple SELECT or a SELECT that is already DB-ordered in the required way?

I am just curious, if it is better, to let SAP doing the sort while inserting/copying an unsorted result to the internal table

or let the DB sort (may be in parallel) and feed the sorted data to ABAP so that it can simply append.

Volker

Re: Optimize BW ABAP start routine

yuri_ziryukin — Thu, 03 Nov 2011 14:52:30 GMT

Hi Volker,

not tested yet, when I have time, I can do some tests in internal test systems.

Yuri

Re: Optimize BW ABAP start routine

Former Member — Sat, 05 Nov 2011 03:49:32 GMT

Hello Yuri,

Just out of curiosity, why use field symbol instead of hash table? Newbie here so pardon if itu2019s a little bit silly.

Re: Optimize BW ABAP start routine

Former Member — Sat, 05 Nov 2011 03:52:43 GMT

Yuri,

is it because there is no unique key?