Database Reference
In-Depth Information
Eliminating the target bottleneck
There are various ways in which you can optimize the target loading.
Minimizing the target table deadlocks
There can be a scenario where Informatica is trying to load the data into a table that is
already being used by another system. When Informatica encounters the deadlock, it
hampers the processing by slowing down the loading process. To avoid this, make sure that
the target table is not used by other processes at the same time.
Drop indexes and constraints
Loading the data into tables takes more time because multiple indexes and constraints are
created on the table. Each time a new record is loaded into the table, it is checked for in-
dexes and constraints before it gets loaded. This hampers the performance. To avoid this,
you can use pre-SQL and post-SQL commands in the session task. Using pre-SQL com-
mands, you can remove the indexes, and using post-SQL commands, you can apply the in-
dexes. When you define pre-SQL and post-SQL, Informatica applies these commands be-
fore and after the data is loaded into the table.
Removing the indexes and constraints as described previously is not always recommended,
but it definitely improves the performance. You can opt for this option if the data you are
loading in the table is not very critical.
Increasing the checkpoint interval
When you run the workflow, the Integration Service keeps on creating checkpoints at a pre-
defined interval. The checkpoints are used for recovery purposes. Reducing the checkpoint
interval will help enhance the performance by storing less checkpoints and less data related
to the checkpoint.
If you reduce the checkpoint interval, even though the performance will increase, it will
hamper the recovery time if the system fails due to an error.
Using an external loader
Informatica PowerCenter supports the usage of multiple external loaders (IBM DB2,
Oracle, Teradata, and Sybase IQ), which can help with faster loading into the target table.
