To enhance the transformation performance, you can configure the following parameters in the Runtime Service configuration file to an appropriate value:
BDTReaderMaxQueueSize: This is the number of internal cache queue entries that Data Exchange will use when running a BDT. This property should be configured carefully as too large a value could use too much memory and too small a value could result in poor performance.
Once the Reader thread gets this number of entries ahead of the Processing thread, it will sleep to allow the Processing thread to catch up. If this value is too small, the Processing thread could empty the cache before it gets any more entries, resulting in poor performance.
CDTReaderMaxQueueSize: This is the number of internal cache queue entries that Data Exchange will use when running a CDT. This property should be configured carefully as too large a value could use too much memory and too small a value could result in poor performance.
BDTProcessingMaxQueueSize: This is the number of internal cache queue entries that Data Exchange will use when running a BDT. This property should be configured carefully as too large a value could use too much memory and too small a value could result in poor performance.
Once the Processing thread gets too far ahead of the Commit thread, it will sleep until the Commit thread catches up. If this value is too large it might take some time for the Commit thread to catch up to the Processing thread and could affect system operations such as Stop.
CDTProcessingMaxQueueSize: This is the number of internal cache queue entries that Data Exchange will use when running a CDT. This property should be configured carefully as too large a value could use too much memory and too small a value could result in poor performance.
BDTTargetBatchSize: This is the maximum number of target records that can be bundled in one transaction. But at the end of the table if there are less records than the BDTTargetBatchSize, they are still committed as one transaction. The default value for BDTTargetBatchSize is 200.
Note: Insufficient physical memory on the machine on which you are running the Runtime Service can cause the BDTTargetSize to throttle down. For example, when BDT is initiated if the actual available memory is less than 2GB, the BDTTargetBatchSize is throttled to 100, even if it is set to a higher value. You can check this value in the Runtime Service log.
CDTTargetBatchSize: During a CDT, the Runtime Service processes the transactions from the source data store to the target data store. CDTTargetBatchSize is the maximum number of DML commands that can be grouped in one target transaction. The DML commands are added to the batch as each transaction is processed. When the number of DML commands reaches or exceeds the maximum number, it gets committed to the target. The default value for CDTTargetBatchSize is 200.
Note: Insufficient physical memory on the machine on which you are running the Runtime Service can cause the CDTTargetSize to throttle down. For example, when CDT is initiated if the actual available memory is less than 2GB, the CDTTargetBatchSize is throttled to 100, even if it is set to a higher value. You can check this value in the Runtime Service log.
MaxCommitWaitTimeInMilliseconds: This is the time the Runtime Service waits for, to commit a batch of transactions to the target table. The default value is 3000.
If the time specified for MaxCommitWaitTimeInMilliseconds has elapsed, the Runtime Service commits the batch of transactions even if the batch size is less than the value specified for CDTTargetBatchSize.
ParallelFeatureProcessingOn: Turn this parameter ON if there are many time-consuming expressions in the feature maps of the transformation. Data Exchange then processes these expressions in parallel, to improve the performance. This is applicable to both BDT and CDT. The default value is OFF.
TrackingCommandTimeoutInSeconds: This is the command timeout period of the Data Exchange Runtime Service Tracking Database (Tracking DB). Sometimes, Data Exchange attempts to reduce the number of records in the tracking database to ensure good performance and control disk usage. This clean up fails due to a command timeout. To avoid such command timeouts with the Tracking DB, increase the default value of this parameter from 30 to a greater value.
CommandTypeInTargetSqlServer:
SingleSQL: It is the default value. For each source data record, a SQL command is generated and executed in the target database.
StoredProcedure: You can use this option to improve the performance of Data Exchange. However, using this option automatically creates stored procedures for the transformation in your target database before BDT or CDT is started.
Note: Data Exchange does not support command type of StoredProcedure if the SQL Server target table is an in-memory table with primary key constraint. In such a case, you can use one the following options:
Remove the primary key constraint on the target table
Use the option SingleSQL instead of StoredProcedure
Use a table without memory optimization instead of an in-memory table.
CommandTypeInTargetOracle:
SingleSQL: It is the default value. For each source data record, a SQL command is generated and executed in the target database.
BatchSQL: You can use this option to improve the performance of Data Exchange if the performance is impacted by a high network latency.
To configure these parameters
Navigate to the folder where the Runtime Service is installed.
Open the configuration file Data Exchange Runtime Service.exe.config.
Set the value of the parameter to the desired value.
Restart the Runtime Service.
Note: Although the default values of these options provide optimal results, you may want to vary it based on your environment.
This change is captured in the Runtime Service log file.