Pushdown Optimization
When you read data from a Microsoft SQL Server source, transform the data, and write the data to a target, you can configure pushdown optimization to push the transformation logic to the source or target database system. If the source and target databases are the same, you can configure full pushdown optimization for improved performance.
When the Secure Agent applies pushdown optimization, it pushes transformation logic to a database. The Secure Agent translates the transformation logic into SQL queries and sends the SQL queries to the database. The database runs the SQL queries to process the transformations.
Pushdown optimization improves mapping performance when the database can process the transformation logic faster than the Secure Agent. The Secure Agent also reads less data from the database.
The amount of transformation logic that the Secure Agent pushes to the database depends on the database, the transformation logic, and the Mapping Configuration task. The Secure Agent processes all transformation logic that it cannot push to a database.
When you configure pushdown optimization for the mapping, the Secure Agent analyzes the optimized mapping from the source to the target or until it reaches a downstream transformation that it cannot push to the source database. The Secure Agent generates and executes a SELECT statement for each source that has transformation logic pushed down. Then, it reads the results of this SQL query and processes the remaining transformations in the mapping.
Note: When you push down transformation logic to the database, ensure that the database has enough resources to process the queries faster. Otherwise, there could be a performance degradation.
The Secure Agent can push the following transformation logic to a Microsoft SQL Server source or target:
Transformations | Supported Pushdown Type |
|---|
Aggregator | Source, Full |
Expression | Source, Full |
Filter | Source, Full |
Joiner | Source, Full |
Sorter | Source, Full |
Union | Source, Full |
Router | Full |
Full Pushdown
When the Secure Agent applies full pushdown optimization, it pushes all the transformation logic in the mapping to the target database. You can configure full pushdown in the Advanced Session Properties section.
Full pushdown optimization is ideal when the source and target are in the same connections. For example, if a mapping contains a Microsoft SQL Server source and a Microsoft SQL Server target, configure full pushdown optimization to push all the transformation logic for processing from a Microsoft SQL Server source database to a Microsoft SQL Server target database.
Note: Microsoft SQL Server Connector does not support upsert operation in a full pushdown optimization.
Source Pushdown
When the Secure Agent applies source pushdown, it analyzes the mapping from source to target or until it reaches a downstream transformation it cannot push to the source database.
The Secure Agent generates and executes a SELECT statement based on the transformation logic for each transformation it can push to the database. Then, it reads the results of this SQL query and processes the remaining transformations.
You can configure a mapping to use source pushdown if the source and target reside in different databases. For example, if a mapping contains a Microsoft SQL Server source and an Oracle target, you can configure source pushdown to push some transformation logic for processing to the Microsoft SQL Server source.
Pushdown Optimization Functions
The following table summarizes the availability of pushdown functions in a Microsoft SQL Server source or target:
Functions | Pushdown Type |
|---|
ABS() | Source, Full |
AVG() | Source, Full |
COS() | Source, Full |
COUNT() | Source, Full |
DATE_COMPARE() | Source, Full |
DECODE() | Source, Full |
EXP() | Source, Full |
IIF() | Source, Full |
IN() | Source, Full |
ISNULL() | Source, Full |
LOWER() | Source, Full |
MAX() | Source, Full |
MIN() | Source, Full |
POWER() | Source, Full |
SIN() | Source, Full |
SQRT() | Source, Full |
SUM() | Source, Full |
TAN() | Source, Full |
UPPER() | Source, Full |
Configuring Pushdown Optimization
Perform the following steps to configure pushdown optimization for Microsoft SQL Server sources or targets:
1. In the Schedule tab of the Mapping Configuration task, navigate to the Advanced Session Properties section.
2. Click Add to add a new session property.
3. From the Session Property Name list, select Pushdown Optimization.
4. From the Session Property Value list, select the required type of pushdown optimization. The following image shows the types of pushdown optimization that you can configure: 