Database Reference
In-Depth Information
Optimizing the Joiner transformation
It is recommended that you assign the table with a smaller number of records compared to
the master while using the Joiner transformation. Also, the table with a smaller number of
duplicates should be used as the master table.
It is also recommended that you perform the join in the Source Qualifier using the SQL
override, as performing joins on the database is sometimes faster as compared to perform-
ing them in Informatica.
Also, pass the sorted data to the Joiner transformation to enhance performance, as this
utilizes less disk space compared to unsorted data.
Optimizing Lookup transformations
Lookup transformations are one of the most complex transformations in Informatica
PowerCenter. Optimizing Lookup transformations will significantly help in improving the
performance.
When you use Lookup transformations in the mapping, use the concurrent cache. When
you use the concurrent cache, Integration Service caches the lookup table data before it
starts processing the data from the source; otherwise, the lookup performs caches on a
row-wise basis, which takes up more time. So, it is recommended that you enable caching
when you use lookups.
If your mapping contains multiple lookups with a lookup on the same lookup table, it is
suggested that you share the cache in order to avoid performing the caching multiple
times.
You can reduce the processing time if you use the lookup SQL override properly in the
Lookup transformation. If you are using lookups to look up a database table, you can use
the lookup SQL override to reduce the amount of data you look up. This also helps in sav-
ing the cache space.
If you are using more than one lookup condition in a Lookup transformation, it is recom-
mended that you place the conditions in the optimized order, that is, place the equal to (
=
)
condition first, then the less than (
<
), greater than (
>
), less than or equal to (
<=
), greater
than or equal to (
>=
), and at last, not equal to (
!=
) condition. This enhances the perform-
ance.
