Community
Application Development and Automation Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Read only

Optimize BW ABAP start routine

Go to solution
Former Member

‎2011 Oct 21 8:56 AM

0 Likes
1,542

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!

1 ACCEPTED SOLUTION
Read only

Go to solution
yuri_ziryukin
Product and Topic Expert
Product and Topic Expert

‎2011 Oct 21 10:00 AM

0 Likes
1,430

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

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

Reply
8 REPLIES 8
Read only

Go to solution
yuri_ziryukin
Product and Topic Expert
Product and Topic Expert

‎2011 Oct 21 10:00 AM

0 Likes
1,431

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

Read only

Go to solution
Former Member
In response to yuri_ziryukin

‎2011 Oct 21 10:39 AM

0 Likes
1,430

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!

BR

Stefan

Read only

Go to solution
yuri_ziryukin
Product and Topic Expert
Product and Topic Expert
In response to yuri_ziryukin

‎2011 Oct 21 1:29 PM

0 Likes
1,430 
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

Read only

Go to solution
Former Member
In response to yuri_ziryukin

‎2011 Oct 21 2:05 PM

0 Likes
1,430

Thanks a lot Yuri!

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

BR

Stefan

Read only

Go to solution
volker_borowski2
Active Contributor
In response to yuri_ziryukin

‎2011 Oct 27 5:48 PM

0 Likes
1,430

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

Read only

Go to solution
yuri_ziryukin
Product and Topic Expert
Product and Topic Expert
In response to yuri_ziryukin

‎2011 Nov 03 2:52 PM

0 Likes
1,430

Hi Volker,

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

Yuri

Read only

Go to solution
Former Member
In response to yuri_ziryukin

‎2011 Nov 05 3:49 AM

0 Likes
1,430

Hello Yuri,

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

Read only

Go to solution
Former Member
In response to yuri_ziryukin

‎2011 Nov 05 3:52 AM

0 Likes
1,430

Yuri,

is it because there is no unique key?