Pushdown Optimization for Teradata ODBC Connection
When you use the ODBC connection to read data from a Teradata source, transform the data, and write the data to a Teradata 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 the database. The Secure Agent translates the transformation logic into SQL queries and sends the SQL queries to the database. The Teradata database runs the SQL queries to process the transformations. The amount of transformation logic that the Secure Agent pushes to the database depends on the database, the transformation logic, and the mapping configuration. The Secure Agent processes all transformation logic that it cannot push to a database.
Pushdown optimization improves mapping performance because the database processes the transformation logic faster than the Secure Agent. The amount of data that the Secure Agent needs to read from the database is reduced.
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.
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. The Secure Agent then 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 Teradata source and an Oracle target, you can configure source pushdown to push some transformation logic for processing to the Teradata source.
Full Pushdown
You can configure full pushdown optimization only when the source and target are in the same database.
When you configure full pushdown, the Secure Agent attempts to push all transformation logic in the mapping to the target database. If the Secure Agent cannot push all transformation logic to the database, it performs both source-side and target-side pushdown optimization.
When you run a mapping configured for full pushdown optimization, the Secure Agent analyzes the mapping from the source to the target or until it reaches a downstream transformation it cannot push to the target database. It generates and executes SQL statements against the source or target based on the transformation logic it can push to the database.