Avoid using Decimal (38,0) ports in the mapping. Decimal (38,0) ports add an additional CAST function in the pushdown SQL.

Maintain the same data type, precision and scale across the ports from source to transformations to target. Any change will result additional CAST statements in the pushdown SQL.

Avoid using a very large precision. For example, avoid specifying a port with a large precision of 1000 bytes or higher precision.

When you use an Aggregator, Expression, Filter, Joiner, Sorter, or Union transformation, pushing transformation logic to databases might improve performance by up to three times.

When you push Lookup transformation logic to databases, disable Null Comparison. If you enable Null Comparison, mappings with multiple Lookup transformations might slow down performance. In the Data Integration Service properties, set the

Optimizer.PdoLookupIgnoreNullCompare

custom property to

true

.

Push SQL transformation logic to databases. In the Data Integration Service properties, set the

Optimizer.PdoAllowSqlTxPushdown

custom property to

true

.

When you use Update Strategy transformation in a mapping, push the logic for the Insert, Delete, Update or Merge operations to the database. Internal tests using TPCH schema-based mappings have yielded up to 22 times improvement in performance when compared the default configuration.