Microsoft SQL Server Connector > Mappings and synchronization tasks with Microsoft SQL Server Connector > Pushdown optimization
  

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 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 Pushdown Optimization 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: You cannot configure the 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

Supported functions for Expression transformation

The following table summarizes the availability of pushdown functions for the Expression transformation in a Microsoft SQL Server database:
Functions
Pushdown Type
ASCII()
Source, Full
CEIL()
Source, Full
CONCAT()
Source, Full
FLOOR()
Source, Full
LENGTH()
Source, Full
LTRIM()
Source, Full
MD5()
Source, Full
MOD()
Source, Full
RTRIM()
Source, Full
SYSTIMESTAMP()
Source, Full
TO_CHAR(DATE)
Source, Full
TO_CHAR(NUMBER)
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 task, navigate to the Pushdown Optimization section.
    2. From the Pushdown Optimization list, select the required type of pushdown optimization.
    The following image shows the types of pushdown optimization that you can configure:
    You can configure source or full pushdown optimization for Microsoft SQL Server sources or targets.

Cross-schema pushdown optimization

You can use cross-schema pushdown optimization for a mapping task to read from or write data to Microsoft SQL Server objects associated with different schemas within the same database.
To use cross-schema pushdown optimization, create two Microsoft SQL Server connections and specify the schema in each connection. Ensure that the schema in the source connection is different from the schema in the target connection, but both the schemas must belong to the same database. When you configure pushdown optimization for the mapping task, enable cross-schema pushdown optimization in the advanced session properties. By default, the check box is selected.

Configuring cross-schema optimization

Consider the following steps to configure cross-schema pushdown optimization for a Microsoft SQL Server mapping task:
    1. Create the following two Microsoft SQL Server connections, each defined with a different schema:
    1. a. Create a sqlsv_1 Microsoft SQL Server connection and specify CQA_SCHEMA1 schema in the connection properties.
    2. b. Create a sqlsv_2 Microsoft SQL Server connection and specify CQA_SCHEMA2 schema in the connection properties.
    2. Create a Microsoft SQL Server mapping, m_sqlsv_pdo_acrossSchema. Perform the following tasks:
    1. a. Add a Source transformation and include a Microsoft SQL Server source object and connection sqlsv1 to read data using CQA_SCHEMA1.
    2. b. Add a Target transformation and include a Microsoft SQL Server target object and connection sqlsv2 to write data using CQA_SCHEMA2.
    3. Create a Microsoft SQL Server mapping task, and perform the following tasks:
    1. a. Select the configured Microsoft SQL Server mapping, m_sqlsv_pdo_acrossSchema.
    2. b. On the Schedule tab, in the Pushdown Optimization section, set the pushdown optimization value to Full.
    3. c. In the Advanced Session Properties section, select the Enable cross-schema pushdown optimization check box.
    4. The following image shows the configured Enable cross-schema pushdown optimization property:
      The Schedule tab shows the configured Enable cross-schema pushdown optimization property in the Advanced Session Properties section.
    5. d. Save the task and click Finish.
    When you run the mapping task, the Secure Agent reads data from the Microsoft SQL Server source object associated with the CQA_SCHEMA1 schema and writes data to the Microsoft SQL Server target object associated with CQA_SCHEMA2 schema.