OUTLINE
Pushdown optimization (PO) is the one of the methods to increase the performance. This is done in the Session block of your project. In this, you make the transformation logic to be executed in source or target database that is you can push the transformation logic to be executed on the database side.
when you select the pushdown optimization,the integration service analyzes the mapping,transformation and session configured. Based on these, it writes 2 or more SQL queries and sends them to be excuted on Database side.The amount of transformation logic that can be pushed depends on the mapping, session, transformation logic and Database.
You can use the Pushdown Optimization viewer to preview the sql statements,mapping logic that is being pushed to the Databse.
This is of 3 types.
1) Source side PO
2) target side PO
3) Full PO
Source-Side Pushdown Optimization:
In source-side pushdown optimization, the Integration Service analyzes the mapping from the source to the target or until it reaches a downstream transformation it cannot push to the database. The Integration Service 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 statement and continues to run the session.
Target-Side Pushdown Optimization:
In target-side pushdown optimization, the Integration Service analyzes the mapping from the target to the source or until it reaches an upstream transformation it cannot push to the database. It generates an INSERT, DELETE, or UPDATE statement based on the transformation logic for each transformation it can push to the database. The Integration Service processes the transformation logic up to the point that it can push the transformation logic to the target database. Then, it executes the generated SQL.
Full Pushdown Optimization:
In pushdown optimization, the source and target must be on the same database. When you run a session configured for full pushdown optimization, the Integration Service analyzes the mapping starting with the source and analyzes each transformation in the pipeline until it analyzes the target. It generates and executes SQL statements against the source and target based on the transformation logic it can push to the database.
Databases with PO:
During pushdown optimization, the Integration Service pushes the transformation logic to one database, which is called the active database. A database that does not process transformation logic is called an idle database.
//Please do COMMENT if you have any DOUBTS or any UPDATES or any SUGGESTIONS//
Thank you
Pushdown optimization (PO) is the one of the methods to increase the performance. This is done in the Session block of your project. In this, you make the transformation logic to be executed in source or target database that is you can push the transformation logic to be executed on the database side.
when you select the pushdown optimization,the integration service analyzes the mapping,transformation and session configured. Based on these, it writes 2 or more SQL queries and sends them to be excuted on Database side.The amount of transformation logic that can be pushed depends on the mapping, session, transformation logic and Database.
You can use the Pushdown Optimization viewer to preview the sql statements,mapping logic that is being pushed to the Databse.
This is of 3 types.
1) Source side PO
2) target side PO
3) Full PO
Source-Side Pushdown Optimization:
In source-side pushdown optimization, the Integration Service analyzes the mapping from the source to the target or until it reaches a downstream transformation it cannot push to the database. The Integration Service 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 statement and continues to run the session.
Target-Side Pushdown Optimization:
In target-side pushdown optimization, the Integration Service analyzes the mapping from the target to the source or until it reaches an upstream transformation it cannot push to the database. It generates an INSERT, DELETE, or UPDATE statement based on the transformation logic for each transformation it can push to the database. The Integration Service processes the transformation logic up to the point that it can push the transformation logic to the target database. Then, it executes the generated SQL.
Full Pushdown Optimization:
In pushdown optimization, the source and target must be on the same database. When you run a session configured for full pushdown optimization, the Integration Service analyzes the mapping starting with the source and analyzes each transformation in the pipeline until it analyzes the target. It generates and executes SQL statements against the source and target based on the transformation logic it can push to the database.
Databases with PO:
During pushdown optimization, the Integration Service pushes the transformation logic to one database, which is called the active database. A database that does not process transformation logic is called an idle database.
The Integration Service uses the following criteria to determine which database is active or idle:
- When using full pushdown optimization, the target database is active and the source database is idle.
- In sessions that contain a Lookup transformation, the source or target database is active, and the lookup database is idle.
- In sessions that contain a Joiner transformation, the source in the detail pipeline is active, and the source in the master pipeline is idle.
- In sessions that contain a Union transformation, the source in the first input group is active. The sources in other input groups are idle.
//Please do COMMENT if you have any DOUBTS or any UPDATES or any SUGGESTIONS//
Thank you
