Skip to end of metadata
Go to start of metadata

 

Function-Based Index for Performance of Change Documents (CDHDR)

 
You may know about bad performance when one wants to retrieve the change documents.

To reproduce the issue you just need to go to transaction BP-> Open any BP -> Menu: Extras -> Change History -> For This Partner.

This is in SAP GUI. But the same story can happen in Web UI as well. We all know that our customers should disable unwanted assignment blocks.

This especially applies to Change Documents. Imagine if one user clicks it incidentally and it takes 1 hour.

Customer has serious concerns regarding performance and the below listed ABAP trace shows that it's even worse than we all could imagine.

There is a very old SAP note: 381466 - "SAP BP: Performance of change document display" which proposes to deactivate some events: CHGD1, CHGD2, CHGD3 and CHGD4 in a way that functions: BUB_BUPA_EVENT_CHGD1, BUB_BUPA_EVENT_CHGD2, BUB_BUPA_EVENT_CHGD3 and BUB_BUPA_EVENT_CHGD4 will not be called.

But in fact this means that proposed solution or workaround will impact the business as not all change records will be shown.

Basically there are several reasons for such extremely long selects.

The first one is the suboptimal access to the table CDHDR. We have a function: bus_cdobjectid_select_wth_like which is using patterns to get the data.

bus_cdobjectid_select_wth_like

*"----------------------------------------------------------------------
"*"Lokale Schnittstelle:
*"  IMPORTING
*"     VALUE(IV_OBJECTCLAS) TYPE  CDOBJECTCL
*"     VALUE(IV_OBJECTID_PATTERN) TYPE  CDOBJECTV
*"  TABLES
*"      ET_CDHDR STRUCTURE  CDHDR
*"----------------------------------------------------------------------

  REFRESH et_cdhdr.

  SELECT * FROM  cdhdr
           INTO  TABLE et_cdhdr
           WHERE objectclas =    iv_objectclas
           AND   objectid   LIKE iv_objectid_pattern.
ENDFUNCTION.



SQL Statement
--------------------------------------------------------------------------
SELECT
  *
FROM
  "CDHDR"
WHERE
  "MANDANT" = ‘400’ AND
  "OBJECTCLAS" = ‘BUPR_BUB’ AND (
  "OBJECTID" LIKE ‘___________3700044148_________________BUR001’

The ABAP code as above will cause the SQL as below. This statement is executed with extremely slow index scan, but only taking into account the second reason.

The second reason is of course, the amount of data in the table CDHDR. But this table usually is known to be one of the biggest tables in the system.

We need appropriate indices to get the data from this table. But due to very old design it can not be done.

Fortunately, in Oracle we have so called Function Based Indexes. (See more http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:667694821129 ).

This means that we are able to create an index on only on the field, but also on the part of the field. The function to be used is SUBSTR(str, x, y)

First of all we need to read the data accordingly. I wrote the following native SQL in ABAP:

Native SQL (Does not work)

try.
        exec sql performing append_itab.
          SELECT *
          INTO :wa_cdhdr
          FROM cdhdr
          WHERE mandant = :sy-mandt
          AND objectclas = :iv_objectclas
          AND substr(objectid,13,10) = :c10
        endexec.
      catch cx_sy_native_sql_error.
        clear: sy-tabix.
    endtry.

But such code does not work.

Next I found a workaround: we can use nice classes as: CL_SQL_CONNECTION, CL_SQL_STATEMENT, CL_SQL_RESULT_SET. Finally I wrote the following Function Module:

ZCDOBJECT_SELECT_WITH_LIKE

FUNCTION ZCDOBJECT_SELECT_WITH_LIKE.
*"----------------------------------------------------------------------*"
*"Local Interface:
*"  IMPORTING
*"     VALUE(IV_OBJECTCLAS) TYPE  CDOBJECTCL
*"     VALUE(IV_OBJECTID_PATTERN) TYPE  CDOBJECTV
*"  TABLES
*"      ET_CDHDR STRUCTURE  CDHDR
*"  EXCEPTIONS
*"      ERROR_OCCURED
*"      NO_DATA_FOUND
*"----------------------------------------------------------------------
*  REFRESH et_cdhdr.
*  SELECT * FROM  cdhdr
*           INTO  TABLE et_cdhdr
*           WHERE objectclas =    iv_objectclas
*           and   objectid   like iv_objectid_pattern.

  types: begin of tt_cdobjectv,
          objectid type cdobjectv,
         end of tt_cdobjectv.


  data: l_con_ref       type ref to cl_sql_connection.
  data: l_stmt_ref      type ref to cl_sql_statement.
  data: l_query         type string.
  data: l_rs_ref        type ref to cl_sql_result_set.
  data: l_data_ref      type ref to data.
  data: l_rows          type i.
  data: l_sqlerr_ref    type ref to cx_sql_exception.


  data: lv_objectid     type cdobjectv.
  data: lr_objectid     like range of lv_objectid with header line.
  data: lt_objectid     type standard table of tt_cdobjectv with header line.

  "  __________2000000118________________BURC01 %
  "  ____________________2000000118______BURC01 %

  refresh et_cdhdr.

  data: lv_bp_no1 type bu_partner.
  data: lv_bp_no2 type bu_partner.
  data: lv_bur_t  type bu_reltyp.
* Get First BP Number
  lv_bp_no1 = iv_objectid_pattern+12(10).
  select single partner from but000 into lv_bp_no1 where partner = lv_bp_no1.
  if sy-subrc <> 0.
    clear lv_bp_no1.
  endif.
* Get Second BP Number
  if lv_bp_no1 is initial.
    lv_bp_no2 = iv_objectid_pattern+22(10).
    select single partner from but000 into lv_bp_no2 where partner = lv_bp_no2.
    if sy-subrc <> 0.
      clear lv_bp_no2.
    endif.
  endif.
* BP Rel Type
  lv_bur_t = iv_objectid_pattern+40(6).
  select single reltyp from tbz9 into lv_bur_t where reltyp = lv_bur_t.
  if sy-subrc <> 0.
    clear: lv_bur_t.
  endif.
* Check all variables are set
  check: lv_bp_no1 is not initial or lv_bp_no2 is not initial.
 
* Prepare SQL statement
  concatenate
   'SELECT objectid FROM cdhdr'
   ' WHERE mandant = ''' sy-mandt ''''
   ' AND objectclas = ''' iv_objectclas ''''
   into l_query.

  if lv_bp_no1 is not initial.
    concatenate
     l_query
     ' AND substr(objectid,13,10) = ''' lv_bp_no1 ''''
    into l_query.
  elseif lv_bp_no2 is not initial.
    concatenate
     l_query
     ' AND substr(objectid,23,10) = ''' lv_bp_no2 ''''
    into l_query.
  endif.

 if lv_bur_t is not initial.   
  concatenate
     l_query
     ' AND substr(objectid,41,6) = ''' lv_bur_t ''''
   into l_query.
 endif.

  try.
      create object l_con_ref.
      l_stmt_ref = l_con_ref->create_statement( ).
      get reference of lv_objectid into l_data_ref.
      l_rs_ref = l_stmt_ref->execute_query( l_query ).

      l_rs_ref->set_param( l_data_ref ).
      while l_rs_ref->next( ) > 0.
        append lv_objectid to lt_objectid.
      endwhile.

      l_rs_ref->close( ).
      l_con_ref->close( ).

    catch cx_sql_exception into l_sqlerr_ref.
      l_rs_ref->close( ).
      l_con_ref->close( ).
      raise error_occured.
  endtry.


  if lt_objectid[] is not initial.
    sort lt_objectid[].
    delete adjacent duplicates from lt_objectid.
    delete lt_objectid where objectid is initial.

    select * from  cdhdr
             into  table et_cdhdr
             for all entries in lt_objectid
             where objectclas = iv_objectclas
             and   objectid = lt_objectid-objectid.
    if sy-subrc <> 0.
      raise no_data_found.
    endif.
  endif.
ENDFUNCTION.

 

This function is working in the same way as the standard one, but it uses SUBSTR function instead of LIKE.

Now we need required Function-Based Indices to be created on Oracle.

For that I wrote a report:

Z_ CDHDR_CREATE_INDEXES

*&-------------------------------------------------------------------**& Report  Z_CDHDR_CREATE_INDEXES*&*&-------------------------------------------------------------------*
REPORT  Z_CDHDR_CREATE_INDEXES.
data: lv_table_name like dd02l-tabname value 'CDHDR'.
data: lt_indexes    type table of dbindex.
data: lt_fields     type table of dbindfld.
data: ls_field      type dbindfld.
data: exc_ref       type ref to cx_sy_native_sql_error.
data: error_text    type string.


if sy-dbsys = 'ORACLE'.
  CALL FUNCTION 'DB_GET_INDEXES'
    EXPORTING
      TABNAME   = lv_table_name
    TABLES
      DBINDEXES = lt_indexes
      DBINDFLDS = lt_fields.

  read table lt_indexes with key name = 'CDHDR~ZS1' transporting no fields.
  if sy-subrc = 0.
    write: / 'Index CDHDR~ZS1 was created successfully'.
  endif.

  read table lt_indexes with key name = 'CDHDR~ZS2' transporting no fields.
  if sy-subrc = 0.
    write: / 'Index CDHDR~ZS2 was created successfully'.
  endif.

  loop at lt_fields into ls_field where field(3) = 'SYS'.
    message 'Please check existing database indices' type 'I'. leave program.
  endloop.

  try.
      exec sql.
        CREATE INDEX "CDHDR~ZS1" ON "CDHDR"
        ("MANDANT",
         "OBJECTCLAS",
         substr("OBJECTID",13,10),
         substr("OBJECTID",41,6),
         "OBJECTID")
      endexec.
    catch cx_sy_native_sql_error into exc_ref.
      error_text = exc_ref->get_text( ).
      message error_text type 'I'.
  endtry.

  try.
      exec sql.
        CREATE INDEX "CDHDR~ZS2" ON "CDHDR"
        ("MANDANT",
         "OBJECTCLAS",
         substr("OBJECTID",23,10),
         substr("OBJECTID",41,6),
         "OBJECTID")
      endexec.
    catch cx_sy_native_sql_error into exc_ref.
      error_text = exc_ref->get_text( ).
      message error_text type 'I'.
  endtry.


  refresh: lt_indexes[].
  refresh: lt_fields[].

  CALL FUNCTION 'DB_GET_INDEXES'
    EXPORTING
      TABNAME   = lv_table_name
    TABLES
      DBINDEXES = lt_indexes
      DBINDFLDS = lt_fields.

  read table lt_indexes with key name = 'CDHDR~ZS1' transporting no fields.
  if sy-subrc = 0.
    write: / 'Index CDHDR~ZS1 was created successfully'.
  endif.

  read table lt_indexes with key name = 'CDHDR~ZS2' transporting no fields.
  if sy-subrc = 0.
    write: / 'Index CDHDR~ZS2 was created successfully'.
  endif.
endif.

Finally I made an enhancement of the standard function module:

bus_cdobjectid_select_wth_like

FUNCTION bus_cdobjectid_select_wth_like."""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""$"$\SE:(1 ) Function Module BUS_CDOBJECTID_SELECT_WTH_LIKE, Start                                                                                        D*$$Start: (1 )-------------------------------------------------------------------------------$$*_ENHANCEMENT 1  ZDMSH_PERF_USE_FBI.    _"active version

if sy-dbsys = 'ORACLE' and
   sy-uname = 'SHARSHATKIN'.
clear: et_cdhdr[].
CALL FUNCTION 'ZCDOBJECT_SELECT_WITH_LIKE'
  EXPORTING
    IV_OBJECTCLAS             = iv_objectclas
    IV_OBJECTID_PATTERN       = iv_objectid_pattern
  TABLES
    ET_CDHDR                  = et_cdhdr
EXCEPTIONS
   ERROR_OCCURED             = 1
   NO_DATA_FOUND             = 2
   OTHERS                    = 3.
case sy-subrc.
  when 0.      exit.
  when 1.      clear: et_cdhdr[].
  when 2.      exit.
  when others. clear: et_cdhdr[].
endcase.endif.

ENDENHANCEMENT.*$$End:   (1 )-----------------------------------------------------------------------------$$* *"------------------------------------------------------------------ *"*"Lokale Schnittstelle: *"  IMPORTING *"     VALUE(IV_OBJECTCLAS) TYPE  CDOBJECTCL *"     VALUE(IV_OBJECTID_PATTERN) TYPE  CDOBJECTV *"  TABLES *"      ET_CDHDR STRUCTURE  CDHDR *"--------------------------------------------------------------------

  REFRESH et_cdhdr.

  SELECT * FROM  cdhdr
           INTO  TABLE et_cdhdr
           WHERE objectclas =    iv_objectclas
           AND   objectid   LIKE iv_objectid_pattern.
ENDFUNCTION.

To prove that my new indices are used, i did an explain in ST05.
 

I need to point out that by customizing in transaction BUS7 or directly via the table TBZ1F  you can insert your own function modules to be processed.So that you can do a substitute for the functions 'BUB_BUPA_EVENT_CHGD1' or CRMH_BUPR_EVENT_CHGD1.
If any questions, please let me know.
Have fun! With Best Regards,Dmitry

  • No labels

2 Comments

  1. Unfortunately this workaround seems to be working only for Oracle and DB2 on zOS. Sure it can't be implemented in the standard.

    Later i will provide the workaround about how can BW be used to resolve the same problem.

  2. For performance improvements in certain applications, you can also use the following index tables:

    BUP_ADDR3_CD - "BP Relationship: Index Table for Change Document Access" filled with reports BUR_ADDR3_CD_FILL / BUR_ADDR3_CD_FILL_VS1

    BUT050_CD - "BP Rel./Role Determination:Index Table for Change Doc.Access" filled with reports BURBUT050_CD_FILL / BURBUT050_CD_FILL_VS1

    FRG0080_CD - "BP Rel./Prt.Func/Sales Empl.:Index Table for Chng Doc Access" filled with report BURFRG0080_CD_FILL_VS1

    Please also search for relevant SAP notes and update them if needed.