Application Development 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: 

Optimize BW start and end routine

Go to solution
Former Member

‎09-11-2014 10:20 AM

0 Kudos

Hello,

I created start and end routine from dso to another dso to enhance some other data.

In developement system everything works fine. But in production system it takes a lot of time. Especially step "No Message: Transformation End" takes 90% of time. I dont knwo why.

Can you tell me how I can optimize coding?

global



* 20110202 2462
 * Deklaration der internen Tabellen und Workareas
 
 * Auftragspositionen
     TYPES:
     BEGIN OF gs_ord_itm_sts,
       doc_number     TYPE /bi0/oidoc_number,
       s_ord_item     TYPE /bi0/ois_ord_item,
       bill_block     TYPE /bi0/oibill_block,
       bilblk_itm     TYPE /bi0/oibilblk_itm,
       bill_date      TYPE /bi0/oibill_date,
       doc_type       TYPE /bi0/oidoc_type,
       salesorg       TYPE /bi0/oisalesorg,
       distr_chan     TYPE /bi0/oidistr_chan,
       division       TYPE /bi0/oidivision,
       sales_off      TYPE /bi0/oisales_off,
       sales_grp      TYPE /bi0/oisales_grp,
       plant          TYPE /bi0/oiplant,
       /bic/zsupp_off TYPE /bic/oizsupp_off,
       /bic/zsd_sugrp TYPE /bic/oizsd_sugrp,
       /bic/zsd_proad TYPE /bic/oizsd_proad,
       sold_to        TYPE /bi0/oisold_to,
       payer          TYPE /bi0/oipayer,
       net_value      TYPE /bi0/oinet_value,
       cost           TYPE /bi0/oicost,
       cml_or_qty     TYPE /bi0/oicml_or_qty,
       net_price      TYPE /bi0/oinet_price,
       doc_currcy     TYPE /bi0/oidoc_currcy,
       sales_unit     TYPE /bi0/oisales_unit,
       /bic/zsd_sdnit TYPE /bic/oizsd_sdnit,
       storno         TYPE /bi0/oistorno,
       createdon      TYPE /bi0/oicreatedon,
       doc_categ      TYPE /bi0/oidoc_categ,
       END OF gs_ord_itm_sts.
 
     DATA: lt_ord_itm_sts TYPE STANDARD TABLE OF gs_ord_itm_sts
           WITH KEY doc_number s_ord_item.
     "interne Tabelle
     DATA: ls_ord_itm_sts LIKE LINE OF lt_ord_itm_sts. "Workarea
 
 
 * sonstige Variablen
     DATA: lv_date TYPE /bi0/oicalday.
 
 
 *$*$ end of global - insert your declaration only before this line   *-*

start routine



*$*$ begin of routine - insert your code only below this line        *-*
     ... "insert your code here
 *--  fill table "MONITOR" with values of structure "MONITOR_REC"
 *-   to make monitor entries
     ... "to cancel the update process
 *    raise exception type CX_RSROUT_ABORT.
 
 * 20110202 2462
 * Füllen der internen Tabellen aus Auftragspositionen (ZSD_D02)
 
 * Auftragspositionen
 
     LOOP AT SOURCE_PACKAGE ASSIGNING <source_fields>.
       SELECT  doc_number
               s_ord_item
               bill_block
               bilblk_itm
               bill_date
               doc_type
               salesorg
               distr_chan
               division
               sales_off
               sales_grp
               plant
               /bic/zsupp_off
               /bic/zsd_sugrp
               /bic/zsd_proad
               sold_to
               payer
               net_value
               cost
               cml_or_qty
               net_price
               doc_currcy
               sales_unit
               /bic/zsd_sdnit
               storno
               createdon
               doc_categ
 
       FROM /bic/azsd_d0200
       INTO ls_ord_itm_sts
       WHERE doc_number = <source_fields>-doc_number
       AND   s_ord_item = <source_fields>-s_ord_item .
       ENDSELECT.
       APPEND ls_ord_itm_sts TO lt_ord_itm_sts.
     ENDLOOP.
 
 
 
 *$*$ end of routine - insert your code only before this line         *-*

end routine



*$*$ begin of routine - insert your code only below this line        *-*
     ... "insert your code here
 *--  fill table "MONITOR" with values of structure "MONITOR_REC"
 *-   to make monitor entries
     ... "to cancel the update process
 *    raise exception type CX_RSROUT_ABORT.
 
 * 20110202 2462
 * Füllen der fehlenden Felder im Ziel mit Daten aus den
 * Auftragspositionen.
 
     DATA: lt_rp LIKE RESULT_PACKAGE.
     DATA: lv_count TYPE i.
 
     LOOP AT RESULT_PACKAGE ASSIGNING <result_fields>.
 
 * Auftragspositionen
 
       READ TABLE lt_ord_itm_sts
       INTO ls_ord_itm_sts
       WITH KEY doc_number = <result_fields>-doc_number
                s_ord_item = <result_fields>-s_ord_item.
 
       <result_fields>-bill_block       = ls_ord_itm_sts-bill_block.
       <result_fields>-bilblk_itm       = ls_ord_itm_sts-bilblk_itm.
       <result_fields>-bill_date        = ls_ord_itm_sts-bill_date.
       <result_fields>-doc_type         = ls_ord_itm_sts-doc_type.
       <result_fields>-salesorg         = ls_ord_itm_sts-salesorg.
       <result_fields>-distr_chan       = ls_ord_itm_sts-distr_chan.
       <result_fields>-division         = ls_ord_itm_sts-division.
       <result_fields>-sales_off        = ls_ord_itm_sts-sales_off.
       <result_fields>-sales_grp        = ls_ord_itm_sts-sales_grp.
       <result_fields>-plant            = ls_ord_itm_sts-plant.
       <result_fields>-/bic/zsupp_off   = ls_ord_itm_sts-/bic/zsupp_off.
       <result_fields>-/bic/zsd_sugrp   = ls_ord_itm_sts-/bic/zsd_sugrp.
       <result_fields>-/bic/zsd_proad   = ls_ord_itm_sts-/bic/zsd_proad.
       <result_fields>-sold_to          = ls_ord_itm_sts-sold_to.
       <result_fields>-payer            = ls_ord_itm_sts-payer.
       <result_fields>-net_value        = ls_ord_itm_sts-net_value.
       <result_fields>-cost             = ls_ord_itm_sts-cost.
       <result_fields>-cml_or_qty       = ls_ord_itm_sts-cml_or_qty.
       <result_fields>-net_price        = ls_ord_itm_sts-net_price.
       <result_fields>-doc_currcy       = ls_ord_itm_sts-doc_currcy.
       <result_fields>-sales_unit       = ls_ord_itm_sts-sales_unit.
       <result_fields>-/bic/zsd_sdnit   = ls_ord_itm_sts-/bic/zsd_sdnit.
       <result_fields>-storno           = ls_ord_itm_sts-storno.
       <result_fields>-createdon        = ls_ord_itm_sts-createdon.
       <result_fields>-doc_categ        = ls_ord_itm_sts-doc_categ.
 
     ENDLOOP.
 
 
 *$*$ end of routine - insert your code only before this line         *-*

I hope you can help me!

Regards

Jesper

1 ACCEPTED SOLUTION

Go to solution
Former Member

‎09-15-2014 7:26 AM

0 Kudos

Hey,

does really nobody has an idea how to raise the performance of the mentioned query?

You would help me a lot because otherwise I have no chance to run the dtp in production system.

Regards

Jesper

5 REPLIES 5

Go to solution
Former Member

‎09-15-2014 7:26 AM

0 Kudos

Hey,

does really nobody has an idea how to raise the performance of the mentioned query?

You would help me a lot because otherwise I have no chance to run the dtp in production system.

Regards

Jesper

Go to solution
raymond_giuseppi
Active Contributor
In response to Former Member

‎09-15-2014 7:31 AM

0 Kudos

All I could suggest would be to replace the SELECT in the LOOP with a SELECT FOR ALL ENTRIES, and not much to hope on the READ TABLE as the table is already a SORTED TYPE.

Regards,

Raymond

Go to solution
aarif_baig
Active Participant
In response to Former Member

‎09-15-2014 7:32 AM

0 Kudos

Hi Jesper ,

                 Please change the below code.

LOOP AT SOURCE_PACKAGE ASSIGNING <source_fields>.

       SELECT  doc_number

               s_ord_item

               bill_block

               bilblk_itm

               bill_date

               doc_type

               salesorg

               distr_chan

               division

               sales_off

               sales_grp

               plant

               /bic/zsupp_off

               /bic/zsd_sugrp

               /bic/zsd_proad

               sold_to

               payer

               net_value

               cost

               cml_or_qty

               net_price

               doc_currcy

               sales_unit

               /bic/zsd_sdnit

               storno

               createdon

               doc_categ

       FROM /bic/azsd_d0200

       INTO ls_ord_itm_sts

       WHERE doc_number = <source_fields>-doc_number

       AND   s_ord_item = <source_fields>-s_ord_item .

       ENDSELECT.

       APPEND ls_ord_itm_sts TO lt_ord_itm_sts.

     ENDLOOP.


Instead use

                  

SELECT  doc_number

               s_ord_item

               bill_block

               bilblk_itm

               bill_date

               doc_type

               salesorg

               distr_chan

               division

               sales_off

               sales_grp

               plant

               /bic/zsupp_off

               /bic/zsd_sugrp

               /bic/zsd_proad

               sold_to

               payer

               net_value

               cost

               cml_or_qty

               net_price

               doc_currcy

               sales_unit

               /bic/zsd_sdnit

               storno

               createdon

               doc_categ

       FROM /bic/azsd_d0200

       INTO table lt_ord_itm_sts

for all entries in SOURCE_PACKAGE

       WHERE doc_number = SOURCE_PACKAGE-doc_number

       AND   s_ord_item = SOURCE_PACKAGE-s_ord_item .


this will speed up the performance


Go to solution
Former Member
In response to Former Member

‎09-15-2014 8:58 AM

0 Kudos

Dear Aarif,

this really helped a lot. Thank you!

Just for my understanding: When I was looking in dtp monitor before changes dtp did not hang in start routine. It was hanging in step "step "No Message: Transformation End". So why does your suggested changes in start routine has such an influence?

Best regards

Jesper

Go to solution
aarif_baig
Active Participant
In response to Former Member

‎09-15-2014 9:09 AM

0 Kudos

Hi Jesper,

it is because you are processing  huge volume of data  which the previous code was not able to handle.

Thanks.