Opinion on BW User Exit

[amine_lamkaissi]

Hello experts,

I implemented few days ago some user exits to enhance some Datasources. Among them one concerning DS 2LIS_11_VAITM.

Her's the code:

WHEN '2LIS_11_VAITM'.

     DATA: s_data_11va0itm TYPE mc11va0itm.

     LOOP AT c_t_data INTO s_data_11va0itm.

*     Retrieve ZZXFIELD and ZPROG from table VBAP

       SELECT SINGLE zprog INTO s_data_11va0itm-zzprog FROM vbap

               WHERE vbeln = s_data_11va0itm-vbeln

               AND   posnr = s_data_11va0itm-posnr .

       SELECT SINGLE zzxfield INTO s_data_11va0itm-zzxfield FROM vbap

         WHERE vbeln = s_data_11va0itm-vbeln

         AND   posnr = s_data_11va0itm-posnr.

       MODIFY c_t_data FROM s_data_11va0itm .

       CLEAR s_data_11va0itm.

     ENDLOOP.

The aim of this Abap code was to add two fields from table VBAP to the exractor.

After an audit of the different codes by an Abap expert. He tolds me that there is scope for performance improvment (Select Queries inside the loops can be avoided).

Is that possible? If yes how?

Many thanks for your ideas.

Amine.

[Former Member · Accepted solution]

Hi,

Usually, select within a loop is no no. What you can do is to select all the needed data before the loop and then just read in inside the loop. See below

Pseudocode:

ctdata_dummy[] = c_t_data[]

sort ctdata_dummy by vbeln posnr.
delete adjacent duplicates from ctdata_dummy comparing vbeln posnr.

select vbeln
          posnr
          zprog
          zzxfield
into it_table
from vbap

for all entries in ctdata_dummy
where vbeln EQ ctdata_dummy-vbeln
    and posnr EQ ctdata_dummy-posnr.

if    sy-subrc EQ 0.

sort it_tab by vbeln posnr.
endif.

loop at c_t_data assigning <fs> (i advise you to use field symbol, coz it directly updates your table c_t_data)

read table it_table into wa_warea with key vbeln = c_t_data-vbeln
                                                     posnr = c_t_data-posnr
                                                     BINARY SEARCH.
if  sy-subrc EQ 0.

<fs>-zprog = wa_warea-zprog.
<fs>-zzxfield = wa_warea-zzxfield.
endif.

endloop.

free: ctdata_dummy, it_table.

hope this helps

[amine_lamkaissi · Accepted solution]

Many thanks Nicanor for you answer.

I have juste one question.

What's the last line in your Abap code?

free: ctdata_dummy, it_table.

Amine

[Former Member · Accepted solution]

that is clearing up the internal table value and releasing it on the memory

[amine_lamkaissi · Accepted solution]

That's very usefull.

Everyday we learn new things.

Amine

[Alexandre]

Amine,

If you will use FOR ALL ENTRIES, please check this wiki

http://wiki.sdn.sap.com/wiki/display/ABAP/FOR+ALL+ENTRIES+-+Home+page

Regards

[amine_lamkaissi]

Very usefull link Alexandre

[sander_vanwilligen]

Hi Alexandre,

You are right, one has to use SELECT ... FOR ALL ENTRIES carefully to avoid potentially very big datasets during the SQL processing on database level.

Please have a look at the following cool blog post from Lars Breddemann:

The suggested approach is to SORT the FAE table and subsequently DELETE ADJACENT DUPLICATES from the FAE table. This should result in a unique dataset which can be safely used by the SELECT ... FOR ALL ENTRIES statement.

By the way, always check if the FAE table is not empty, this will otherwise give nasty results.

Looking at my suggested coding example, I thought that the hashed table would be sufficient. However, reading the excellent blog from Lars a few times more, I think that the "FOR ALL ENTRIES disaster" can also occur in my example.

So here it would be the safest option to first make a unique dataset which can be subsequently used by the SELECT ... FOR ALL ENTRIES statement.

The enhanced coding will then become:

...
* Fill table with sales document and sales organization  
  l_t_data = c_t_data[].  
  sort l_t_data on vbeln.  <<< INSERT
  delete adjacent duplicates from l_t_data comparing vbeln.  <<< INSERT
  select vbeln vkorg from vbak  
    into corresponding fields of table l_th_vbak  
    for all entries in l_t_data  
    where vbeln = l_t_data-vbeln.  
...

Best regards,

Sander

[sander_vanwilligen]

Hi Amine,

From a performance perspective I would always try to work with a hashed internal table. This is an internal table that behaves like a transparent table with a primary key. In this case it works perfectly since you will use the primary key to retrieve the VBAP data.

Another performance tip is using field-symbols.

Please refer to the example below (based on VBAK but the idea remains the same):

* Local data declaration
  types:
    begin of ty_vbak,
      vbeln   type vbeln_va,
      vkorg   type vkorg,
    end of ty_vbak.
  data:
    l_t_data  type standard table of mc11va0sth,
    l_th_vbak type hashed table of ty_vbak
                   with unique key vbeln.
  field-symbols:
    <data>    type mc11va0sth,
    <vbak>    type ty_vbak.
* Initialization
  refresh:
    l_t_data,
    l_th_vbak.
* Fill table with sales document and sales organization
  l_t_data = c_t_data[].
  select vbeln vkorg from vbak
    into corresponding fields of table l_th_vbak
    for all entries in l_t_data
    where vbeln = l_t_data-vbeln.
* Enhance data package with sales organization
  loop at c_t_data assigning <data>.
    read table l_th_vbak assigning <vbak>
      with table key vbeln = <data>-vbeln.
    if sy-subrc = 0.
      <data>-zzvkorg = <vbak>-vkorg.
    endif.
  endloop.

Last but not least, a word on encapsulation of coding. It's very safe to implement the coding in such a way that you are able to maintain each DataSource enhancement individually. This way you can avoid transport problems and other coding conflicts or human errors.

Please have a look at my collaboration document which describes how to implement encapsulation via ABAP OO classes in conjunction with the SAPI BAdI.

Best regards,

Sander

[amine_lamkaissi]

Many thanks Sander for your (as-always) well explanations and interesting links.

Amine

[Former Member]

if it was up 2 me, i would not write any code in user exit, but call   a form to go to another program that calls my user exit as a program..

the biggest issue with that is you will have all enhancements in 1 place..this has been an issue if somebody transported a wrong code, all the data source gets in to an issue.

here is a sample

*

*                   The name of ABAP should begin with 'ZBW_VAREXIT_'

*                   followed by variable name for which program is

*                   written. For example Program name for Customer Exit

*                   variable 'BCU_MFC1' should be ZBW_VAREXIT_BCU_MFC1.

*                   These programs should include a 'FORM' with name

*                   begining with 'VAR_' followed by variable name. For

*                   example the program ZBW_VAREXIT_BCU_MFC1 should have

*                   all the logic coded in a FORM 'VAR_BCU_MFC1'.

*

* CMOD:        ZBW001

* Enhancement: RSR00001

* Conponent:   EXIT_SAPLRRS0_001

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

* CHANGE LOG

*-----------------------------------------------------------------------

* Date       | GSM/CTS    | Changed by     | Description

*-----------------------------------------------------------------------

*-----------------------------------------------------------------------

*

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

DATA:   l_pname LIKE trdir-name VALUE 'ZBW_VAREXIT_',

            l_rname(80) TYPE c VALUE 'VAR_'.

field-symbols <loc_range> type rrrangeexit.

DATA  l_loc_var_range TYPE  rrrangeexit.

CASE i_step.

WHEN '0'.

*       Below is the generic code for step 1 of all the customer exit

*       variables. Logic to process each of these variables should be

*       written in a Subroutine (FORM) of a ABAP program for each

*       variable.

*    IF i_vnam+2(1) = 'E'.

      CONCATENATE l_pname i_vnam INTO l_pname.

      SELECT SINGLE  name INTO l_pname FROM trdir

      WHERE name = l_pname.

      IF sy-subrc <> 0.

        MESSAGE e001(ZBW_VAR) WITH i_vnam.

      ELSE.

        CONCATENATE l_rname i_vnam INTO l_rname.

        PERFORM (l_rname) IN PROGRAM (l_pname) USING i_vnam

i_vartyp

i_iobjnm

                                                     i_s_cob_pro

i_s_rkb1d

i_periv

i_t_var_range

                                                     i_step

CHANGING e_t_range

e_meeht

e_mefac

                                                     e_waers

e_whfac

c_s_customer.

      ENDIF.

*    endif.

  WHEN '1'.

*       Below is the generic code for step 1 of all the customer exit

*       variables. Logic to process each of these variables should be

*       written in a Subroutine (FORM) of a ABAP program for each

*       variable.

      l_pname = 'ZBW_VAREXIT_1_'.

      l_rname = 'VAR_'.

      CONCATENATE l_pname i_vnam INTO l_pname.

      SELECT SINGLE  name INTO l_pname FROM trdir

      WHERE name = l_pname.

      IF sy-subrc <> 0.

      ELSE.

        CONCATENATE l_rname i_vnam INTO l_rname.

        PERFORM (l_rname) IN PROGRAM (l_pname) USING i_vnam

i_vartyp

i_iobjnm

i_s_cob_pro

i_s_rkb1d

                                                     i_periv

i_t_var_range

i_step

CHANGING e_t_range

e_meeht

e_mefac

e_waers

e_whfac

                                                     c_s_customer.

      ENDIF.

  WHEN '2'.

*       Below is the generic code for step 2 of all the customer exit

*       variables. Logic to process each of these variables should be

*       written in a Subroutine (FORM) of a ABAP program for each

*       variable.

    CONCATENATE l_pname i_vnam INTO l_pname.

    SELECT SINGLE  name INTO l_pname FROM trdir

    WHERE name = l_pname.

    IF sy-subrc <> 0.

     " MESSAGE e001(ZBW_VAR) WITH i_vnam.

    ELSE.

      l_rname = 'VAR_'.

      CONCATENATE l_rname i_vnam INTO l_rname.

      PERFORM (l_rname) IN PROGRAM (l_pname) USING i_vnam

i_vartyp

i_iobjnm

i_s_cob_pro

i_s_rkb1d

i_periv

i_t_var_range

                                                   i_step

CHANGING e_t_range

e_meeht

e_mefac

                                                   e_waers

e_whfac

c_s_customer.

    ENDIF.

  WHEN '3'.

*       Below is the code for step 3 which is called after all variable

*       processing. It gets called only once and not per variable.

*       The user entered variable values are available globally

*       in the internal table l_loc_var_range.

    loop at i_t_var_range assigning <loc_range>.

*    append <loc_range>.

    l_pname = 'ZBW_VAREXIT_3_'.

    l_rname = 'VAR_'.

    CONCATENATE l_pname <loc_range>-vnam INTO l_pname.

      SELECT SINGLE  name INTO l_pname FROM trdir

      WHERE name = l_pname.

      IF sy-subrc = 0.

      CONCATENATE l_rname <loc_range>-vnam INTO l_rname.

      PERFORM (l_rname) IN PROGRAM (l_pname) USING i_vnam

i_vartyp

i_iobjnm

i_s_cob_pro

i_s_rkb1d

i_periv

i_t_var_range

i_step

                                          CHANGING e_t_range

e_meeht

e_mefac

e_waers

e_whfac

c_s_customer.

      ENDIF.

    endloop.

    clear <loc_range>.

1. ENDCASE.  "     CASE i_step.

[ankurgodre]

Hello Amine,

Since your SELECT query is retrieving only single fields there should not be much issue with regards to the performance, however you may:

Try using the FOR ALL ENTRIES addition in the select query to avoid select under loop, however make sure the initial table is not empty before applying this addition by putting a check before the select query.

BR

Ankur

Message was edited by: Ankur Godre