Microsoft SQL Server Connector

Back Next

Rules and guidelines for SQL ELT optimization

Rules and guidelines for
SQL ELT optimization

Consider the following rules and guidelines when you configure

SQL ELT optimization

for a Microsoft SQL Server mapping:

When you push transformation logic to the database, ensure that the database has enough resources to process the queries faster. Otherwise, there could be a performance degradation.

If the following transformation or mapping conditions is true, the Secure Agent processes a logic instead of pushing it to the database:

The transformation contains a variable port.

The transformation is not a Sorter transformation, Union transformation, or target in a mapping.

The transformation downstream from a Sorter or Union transformation, or contains a distinct sort.

A configured

mapping

task to override the default values of input or output ports.

The database does not have an equivalent operator, variable, or function to use in an expression in the transformation.

The mapping contains too many branches. The Secure Agent can't generate an SQL query for a mapping that contains more than 64 two-way branches, 43 three-way branches, or 32 four-way branches. If the number of branches exceeds these limitations, the Integration Service processes the downstream transformations.

mapping

task to log row errors.

Consider the following rules and guidelines when you configure full

SQL ELT optimization

for a Microsoft SQL Server mapping:

You can't push the LTRIM(), RTRIM(), or MOD() function that contains more than one argument.

To push the MOD() function to the Microsoft SQL Server database, the argument that you pass must be of the Integer date type.

When you push the INSTR() function, you can only define string, search_value, and start arguments.

Consider the following rules and guidelines when you use an Expression transformation in a Microsoft SQL Server mapping enabled with full

SQL ELT optimization

When you push the IS_DATE() function that contains the Text or Ntext data type, the IS_DATE() function returns 0.

When you push the IS_DATE() and IS_NUMBER() functions and the values in the argument contain NULL, the functions return 0.

You can't get a case-sensitive return value for the REPLACECHR() or REPLACESTR() functions.

When you push the MD5() function that contains the nchar data type, the function returns a different value for the nchar data type as compared to a mapping that runs without

SQL ELT optimization

To get the same return value for the nchar and char data types when you push the MD5() function, enter the

ConvertToVarcharForMD5InPDO=Yes

property in the mapping task.

When you push the LPAD() function that contains the second_string argument, the function truncates the second string from left to right.

For example, for the LPAD('Infa',9,'RELATIONAL CONNECTIVITY') expression, the function returns the following value: IVITYInfa

When you push the DATE_DIFF() function that contains the date1 and date2 arguments, the function returns the following different values as compared to a mapping that runs without

SQL ELT optimization

The function returns a negative number when the value of date1 is later than the value of date2.

The function returns a positive number when the value of date1 is earlier than the value of date2.

Rename Saved Search

Table of Contents

Microsoft SQL Server Connector

Microsoft SQL Server Connector

Rules and guidelines for SQL ELT optimization

Rules and guidelines for SQL ELT optimization

Rules and guidelines for
SQL ELT optimization