Database Reference
In-Depth Information
Pushdown optimization
Pushdown optimization is a concept using which you can push the transformation logic on
the source or target database side. When you have a source as the database table, you can
make use of a SQL override to remove the logic written in the transformation. When you
use the SQL override, the session performance is enhanced, as processing the data at a
database level is faster compared to processing the data in Informatica. You cannot remove
all the transformations from the mapping. The part of transformation logic that can be
pushed at the source or target level is referred to as pushdown optimization.
Consider that you have a mapping with a sequence indicated as follows:
Source - Source Qualifier - Filter - Sorter - Aggregator - Expression - Lookup - Rank - Tar-
get.
In Filter transformations, we are filtering the data in a particular location. In Sorter trans-
formations, the data is sorted in a particular department ID. In the Aggregator, we are
grouping the data in the department ID. In Expression transformations, the unconnected
Lookup transformation is called using the
:LKP
function, and finally, a Rank is used to get
the top-salaried employee into the target.
We can remove the Filter transformation, Sorter transformation, and Aggregator transform-
ation by adding the
WHERE
clause, the
ORDER BY
clause, and the
GROUP BY
clause, re-
spectively, in the SQL override in the Source Qualifier transformation. We cannot remove
the Expression transformation, as we cannot write the
:LKP
function in the SQL override.
So, our mapping becomes simple after using the SQL override, which is indicated as fol-
lows:
Source - Source Qualifier - Expression - Lookup - Rank - Target.
Using pushdown optimization will help in saving the processing time by extracting fewer
number of records of data from the source and also fewer number of records in the trans-
formations.
