Skip to Content
SAP Test Data Migration Server

Performance Improvement of Data Selection and Data Transfer Activities in SAP TDMS 4.0

Tags:
Objective:
This document is used to spread awareness about various options available to improve performance of data selection and data transfer activity in TDMS migration packages. We can classify all available options in following types:
  • Performance improvements of Data Transfer Activity
  • Performance improvements of Data Selection Activit
1. Performance Improvement of Data Transfer Activity:
Following options are available to improve performance of data transfer activity:
·         Index drop/re-create solution
·         System Settings

1.1  Index Drop and Re-create Solution:
Writing data into the receiver system can be substantially accelerated if indexes are dropped before the data is transferred, and recreated after the transfer is completed.
Prerequisite:
1.   If no other user is working in any other client of receiver system.
2.   It might even be possible to drop the primary index. This, however, is only possible if INSERT is the only write behavior to be used, and If strictly no SELECT is done for that table in parallel to the transfer.
Implementation:
1.    In TDMS, you can use troubleshooter “Drop Indexes in Receiver System” of activity “Start Data Transfer” to drop indexes of table. You need to pass table name and type of indexes (to be dropped) that is “all” (also include primary index) or “only non-unique”.
2.    Once transfer of any table is finished, you can re-create dropped indexes using troubleshooter “Re-Create the Dropped Indexes in Receiver System” of activity “Start Data Transfer” by providing table name.
3.    One new activity “Re-Create the Dropped Indexes in Receiver System” would also be dynamically inserted to process tree if you have dropped any index using above stated troubleshooter. It will take care of all remaining indexes which were deleted using TDMS but not yet recreated.
Note:
  • If Oracle is used as DB system, it might also be considered to define a partitioning of a primary index by the client field. If the transfer only refers to a certain client the data of which are not read in parallel to the transfer, it is helpful to drop only the index partition referring to that client, while keeping those partitions referring to the other clients.
  • In the absence of the primary and all secondary indexes, any delete or modify access to the table would be executed as full table scan.
  • In case of Oracle, above stated TDMS implementation uses the following options of the DDL statement to recreate indexes: NOLOGGING, PARALLEL and ONLINE.
  • In case duplicates have occurred, set parameter P_DUPL = ‘X’ for activity “Re-Create the Dropped Indexes in Receiver System” to delete duplicate records before recreating indexes. This needs to be done before executing index re-creation activity.
  • In case of oracle database, performance of index recreation can be increased by using following options: multiple parallel database process, online index recreation and no logging by setting parameters P_HINT to any integer and P_ONLINE to ‘X’ in CNVMBTACTPARAMS for activity TD05P_CREATE_INDEX_TS and TD05P_CREATE_INDEX using view V_CNVMBTACTPAR.

1.2  Receiver System Settings:
The receiver system is only accessed by means of synchronous rfc calls, which requires a high number of dialog work processes. Recommendations for profile parameters:
  • rec/client OFF
  • rdisp/btctime 60

For all larger tables in the receiver system, the switch "log data changes" in the technical settings in the SAP DDIC should not be active. Otherwise, the database will then write the data records in single record mode.
If it is not possible / not allowed to deactivate this logging switch, It is then necessary to set, before the runtime objects are generated, parameter SUPPRESS_DBTABLOG_UPDATE in parameter table DMC_RT_PARAMS needs to be set to 'X' in the central system. For details, see note 1549208.
The system should be run in NOARCHIVELOGMODE as per database.
The mirroring of the redo log files should be switched off (there should be only one member per redo log group).

2.   Performance Improvement of Data Selection Activity:
Following options are available to improve performance of data selection activity:
· Parallelization of data selection
· Reading Type 7
· Uses of Hints
· Different Selection logics
· System Settings

2.1  Parallelization of Data Selection:
For DMIS and DMIS_CNT SP09 and higher, Please refer to another document "Parallelization of Data Selection in TDMS 4.0 from SP09 onwards"

By default, single batch job runs for data selection of one migration object. Multiple batch jobs can be scheduled for access plan calculation (data selection) of any migration object based on number of records in application table in case of flat migration object and number of records in header table in case of header migration object.
Prerequisites:
1.    Resources of systems would be used with higher degree at the same time in terms of number of batch processes in sender and central system.
2.    In case sender system is productive system, impact of parallelization would be quite high as we need to compete with other applications for system resource (CPU power and batch processes).
Implementation:
You can perform parallelization of any migration object in 2 ways:
  • Automatic option
  • Manual option

Automatic way to parallelize data selection:
Maintain entry in table CNVMBTPRECALCOBJ using view V_CNVMBTPRECALC for migration object. You need to specify name of header table (in case it is structured migration object), delimiter value (maximum number of records which one batch job should handle) and name of migration object.
Fo  For Example: if number of records filled in TDMS header table CNVTDMS_05_BKPF is 10 million (You can find this information from logs of corresponding fill header activity) and you want to schedule 5 batch jobs for data selection of migration objects X_BKPF.
You need to maintain entry in stated table with CNVTDMS_05_BKPF, 2000000 and X_BKPF as name of header table, delimiter value and name of migration logic.
In case of flat migration object, name of header table would be blank.
In case of fill header migration objects, set parameter P_SEL = ‘X’ for corresponding fill header as well.
Note:
  • Above stated settings need to be done before starting “System Analysis” phase.
  • Above stated customization is not considered with “force redefinition” of migration objects.
  • You can achieve parallel data selection of any fill header object which is based upon any of following TDMS ERP fill headers using above stated steps:
    • Identification of Compressed Data for FI-CO Documents (TD05X_FILL_ACCTHD)
    • Identification of Accounting Documents (TD05X_FILL_BKPF)
    • Identification of Operating Concern-Related Data (TD05X_FILL_CE)
    • Identification of Controlling Documents (TD05X_FILL_COTAB)
    • Identification of Foreign Trade Data (TD05X_FILL_EIKP)
    • Identification of Purchasing Document Data (TD05X_FILL_EKKO)
    • Identification of Object Numbers with Status (TD05X_FILL_JEST)
    • Identification of Material Ledger Data for Orders (TD05X_FILL_VBSK)
    • Identification of Relevant Sales History Documents (TD05X_FILL_VBUK_2)

Manual way to parallelize data selection:
  • Kill already running data selection job (if running) in sender system.
  • Force redefine migration object using troubleshooter.
  • Execute report DMC_GENERATE_ACPLAN_DELIMITER in the central system. It generates and executes a report in the sender system to calculate the basis of the parallelization (that means, it determines key field values to be used as boundaries of the selections applied to the multiple parallel-running jobs). Note down name of report generated.
  • Go to sender system, open tcode sa38 and press F8. Enter Package number and execute it in background mode using menu option (program-> Execute in Background):
  • Check progress of this batch job in sm37 of sender system. Name of batch job would be same as that of report program executed in previous step
  • Once the batch job in the sender system is finished, report DMC_CREATE_PRECALC_ACP_W_DELIM can be started in the central system to retrieve the results.
  • After that, change number of maximum jobs for migration object using troubleshooter “Change Technical Settings” and start data selection activity from process tree.
Note:
  • Technically, any object can be configured for parallelization but number of tables involved in migration object put significant load on database server of sender system.
  • Any number of background jobs can be created for selection of a migration object but it is recommended to use max 20 batch jobs for selection of oneobject.
2.2  Reading Type 7:
This is new reading type which is launched in new DMIS 2011 (TDMS 4.0). This option should be used if there are very many child table records for each header table record in other words it should be used if 1:N relationship is there in between header and child table record with N roughly >10 on an average base.

Pre-requisite:
  • This is applicable for structured migration objects.
  • This is applicable for objects which have one to one hierarchy (one header and one child table).

Implementation:
  • Use troubleshooter “Change Read Behaviors” present under node “'Change Settings of Migration Objects“for data selection activity from web-ui to change reading type to 7.
  • There is one more way to change the reading type in view V_CNVMBTCOBJ for a migration object and force redefine the migration object using troubleshooter.
  • If selection is already running and you have observed bad performance, you can kill batch job in sender system and use troubleshooter to change reading type to 7 and force redefine object.
  • Once this is done, restart data selection activity.

Note:

  • You need to force redefine object if you need to change reading type once “Generation of Runtime Objects” activity is already finished. It is required to generate new runtime objects as per reading type 7.
  • In case of many header table records for the current package, this might cause a memory overflow shortdump, unless you also specify a parallelization which would subdivide the set of header table records into manageable subsets.

2.3  Database Hints:
In case application table has large number of records, we can use database hints to use multiple parallel database processes. In case of reading type 5, we are making full table scan. We can use multiple parallel database processes to perform full table scan on application table.

Implementation:
Maintain entry in table DMC_PERF_OPTIONS for particular mass transfer ID (MT_ID) and migration object (MIGR_OBJ_ALIAS) by providing following information:

PACKAGE_SIZE (Package Size for Fetch statement)
This customization is for package size used in fetch statement.
We have the following default values for package size in current solution:
  • If nothing is specified in this table DMC_PERF_OPTIONS, the default is set to 1000 records.
  • In case of FOR ALL ENTRIES being activated, we take only 100, to make sure that the FAE select will not give too many records
    If this logic is considered as not optimal, customer can specify any value in package _size for particular migration object.

FOR_ALL_ENTRIES (For all Entries logic)
FOR_ALL_ENTRIES will make sure that for all header table records which have been selected in a FETCH statement, we do one single SELECT with FOR ALL ENTRIES to get all related child table records.
This can be helpful to improve the performance, provided that we know that we will never have a large number of child table records per of header table record. Otherwise, a data portion might become too large.
We are usually more efficient if more records per DB operation are considered, 50 might be a good choice. This is controlled by the MAX_IN_BLOCK field.
FOR_ALL_ENTRIES and MAX_IN_BLOCK are interrelated, specifying a value (possibly 50 that might be a good choice) for MAX_IN_BLOCK is only meaningful if FOR_ALL_ENTRIES is set to X / true.

PARALLEL_HINT (Parallel Hints)
Customer can mention parallel hint to be used in any select query. It is applicable only for oracle and DB6.
2.4 Include Technique:
We can change the selection logic of structured migration objects by reducing number of select queries on header table. We can achieve this in different ways as per number of records in header table.
This technique is replaced by reading type 7 and parallelization of data selection. It should be used only in limited cases.
We have provided different included programs with naming convention of DMC_INCL_ACS*XXX* (XXX – name of migration object).
Pre-requisite:
  • This is applicable to structured migration objects only.
  • Structured migration object should be split in such a way that it should have one header and one child table only.