Skip to main content

Development

Several Performance Tuning Suggestions in SAP Business Objects Data Services XI 3.2

1.       In Table Comparison component

  1.1.    Use ‘Row-by-row select’ if

  1. a.       Target table is nearly or more than 1GB
  2. b.      The daily loaded row count is stable and small (less than 5%)

          Note: should create index for columns in ‘Input primary key columns’ section

  1.2.    Use ‘ Cached comparison table’ if

  1. a.       1.1 #a and #b are not matched

  1.3.    Check the ‘run as separate process’ if

          a.        ETL average execute duration is long (over 20 minutes)

                   Note: This option is to create a new thread (al_engine.exe) on the job server.  

                             It should not apply to all ETLs since that will impact server performance.

2.       Delete record by using Row_Generation component

   2.1.    Steps(see below sample dataflow)

 05 

             a.       Add a Row_Generation component, do not need to change anything in it;

             b.      Add a Query component, then add column EDW_CREATED_BY as varchar(20) Primary Key, Mapping it to cast( $G_RUN_ID, ‘varchar(20)’ );

                     Note: G_RUN_ID is indicator of the data inserted by which instance

             c.       Add a Map_Operation component, map normal input row type to delete, and all the others as discard;

             d.      Drag the target table into the dataflow, in “options” tab, check “Use input keys”

3.       Avoid loop in dataflow level

    3.1.    Case:

 

        In this dataflow, it will optimize these 3 queries into one and submit to database, but it will get Cartesian product

        Solution:

                        Drag source table again and use alias, see below

                        07

4.       Avoid using DS function in where condition when query from source

If you put DS function (e.g. is_valid_int) in where condition, it will load all data and calculate this function in memory instead of database level, then you need replace this DS function with database function or move this function to following Query, and here are steps to let you check which sql will submit to database level

       a.         Open Data Service Designer, select dataflow

       b.         Goto tab ValidationàDisplay Optimized SQL, then you could see the sql submit to database

 

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Follow Us