dotConnect for Salesforce Marketing Cloud can translate a subset of SQL-92 SELECT statements to the corresponding ExactTarget API calls for remote execution. As remote execution provides much higher performance than local one, it makes sense to use SELECT statements that are translatable to API Calls.

SELECT statements that correspond to the following pattern and conditions can be converted to API calls and executed remotely:

SELECT result_columns FROM table [WHERE where_condition]  

• result_columns can be a list of the columns, separated with commas, or "*", which means all the result columns.
• table - the name of the ExactTarget object.
• where_condition - one condition. Condition must be a comparison of a column of the selected object and a constant or a parameter. No arithmetic or other functions are allowed. The following comparison operators are supported: =, <=, >=, !=, <, >, IN, LIKE, BETWEEN, IS NULL, IS NOT NULL. Only the columns that allows filtering can be included in where_condition

If a SELECT statement contains any of the features, not explicitly mentioned above, it cannot be translated to ExactTarget API calls and by default, it is executed locally, which usually takes more time and resources than remote execution. (Local execution can be disabled completely if necessary - see Select Statement Execution).

Exceptions

If a statement contains any of the exceptions, listed below, it also cannot be translated to the ExactTarget API calls and is executed locally.

• WHERE clause with a string column with a length more than 4000 characters.
• WHERE clause with the SendableSubscriberField or SendableCustomObjectField column of the DataExtensionTemplate object.
• WHERE clause with the custom columns of the Subscriber object.
• LIKE operator for non-string columns or for columns of nested or related objects or for custom columns of the Subscriber object or for any custom object (data extension) columns.
• LIKE operator for the ModifiedByName column of the Portfolio object.