Performance Tuning in XDM

On the source connection, the relevant parameter is:

The Subset Extract Package Size defines how many data sets are extracted together in one package during data extract using SQL. A package contains the data records that belong to the selected keys. This setting affects processing speed and memory usage. A larger package size can improve processing speed, because more keys are processed with a single query. It also increases memory usage. If the package size is too large, the generated queries may become too long for the source database system.

For performance tests, a typical range is:

On the target connection, the relevant parameters are:

The Subset Apply Batch Mode controls whether JDBC batch mode is used. If batch mode is activated, XDM bundles multiple rows and sends them together as a batch to the target database when applying the data. Using batch mode can improve performance. It also requires more memory on the execution server and on the database server. The default value is true. Generic JDBC connections do not support these parameter.

The Subset Apply Batch Size controls how many rows are packed into one unit when Subset Apply Batch Mode is active. This parameter is only used if batch mode is active. Valid values are integer values of 1 or greater. A value of 1 effectively disables batch processing. To use batch processing, set the value to an integer greater than 1. The practical maximum depends on the capacity of the execution server and the database server. Typically, the maximum is around 5,000 rows per batch statement. The default value is 500 for all connection types that support this parameter. Generic JDBC connections do not support this parameter.

For performance tests, a typical range is: 50 to 5,000. This range is intended as a general guideline only. The appropriate value depends on the test objective, the environment, and the expected workload. Most database systems impose technical limits on batch SQL INSERT statements, such as the maximum number of parameters and the maximum statement length. Therefore, the maximum recommended batch size also depends on the number of columns per row.

A useful formula is:

If the batch size is too high, database errors such as the following may occur:

The relevant performance parameters depend on the task type and on the extract and load mechanism used during task execution.

For CTC-Tasks and RLP-Tasks, the Subset Extract Package Size controls how many data sets or keys are processed together during subset extraction.

For CTC-Tasks and RLP-Tasks, the Subset Apply Batch Size controls how many rows are bundled into one unit when subset apply batch mode is active.

For NTC-Tasks, the relevant extract parameter depends on the extract and load mechanism used for the affected table. If XDM uses the generic extract unit, Data Extract Generic Fetch Size controls how many rows are fetched from the database in one network operation. If XDM uses a native unload utility, this parameter is not relevant for the unload step.

In special cases, XDM may use SQL Data Transport for NTC-Tasks. Only in this case, the parameters for Subset Extract and Subset Apply can become relevant for the affected tables. This does not apply to generic extract.

For more information about the available extract, transport, and load mechanisms, see Extracting, Data Transport and Loading.

Another relevant group of parameters controls parallel processing with threads.

For table copy tasks, the Thread Count defines the number of parallel threads used to extract data from the source environment and load it into the target environment. One thread can process one table at a time. The minimum value is 1. If the value is set to 1, objects are processed sequentially.

For Row Level Processor Tasks, the thread configuration is split between extract and apply processing. The Thread Count (extractThreadCount) controls the number of threads used during subset extract. The Subset Apply Thread Count (subsetApplyThreadCount) controls the number of threads used during subset apply.

The ideal number of threads depends on the CPU and I/O capacity of the database server. It can also vary if the database server is processing other workload at the same time as the XDM task.

As a rough starting point for performance tests, values between 1 and 10 are often sufficient to identify a useful direction. This is not a technical limit. The appropriate value depends on the task type, the database system, the environment, and the expected workload.

Test performance-related parameters step by step. Change only one parameter at a time so that the effect of each change can be evaluated separately.

Recommended procedure:

Performance supervision is useful when the regular task statistics are not detailed enough to explain a performance issue. The regular task statistics provide an overview of the task execution. Performance supervision adds more detailed diagnostic information, especially profiling statistics for JDBC queries. This can help identify whether a changed parameter improves the task execution or only shifts the bottleneck to another part of the process.

Performance supervision is available for all task types. After a few minutes, for example after approximately five minutes, the statistics can already show how much data XDM has read and written during the test run.

Use longer test runs only when the first results are promising and need to be confirmed under more realistic conditions. A longer run can help to detect effects that only occur over time, such as changing database load, caching effects, network limitations, or increasing pressure on the execution server.

When no modification is applied, the achievable throughput is often close to the throughput that the source and target databases can provide through JDBC. However, the effective throughput still depends on the database system, JDBC driver, network connection, table structure, selected task type, and current system load.