dotConnect for Dynamics 365 can translate a subset of SQL-92 SELECT statements to the corresponding Dynamics 365 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 [LEFT joins][WHERE where_condition] [ORDER BY orderByClause] 

• result_columns can be a list of the columns, separated with commas, or "*", which means all the result columns.
• table - the name of the Dynamics 365 object.
• joins - JOIN clause, which can be the following: JOIN table ON condition. condition here must use only the equality operator.
• where_condition - one or more conditions, united with 'AND' or 'OR'. 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
• orderByClause - one or more column names optionally followed by "ASC" or "DESC", separated by commas.

If a SELECT statement contains any of the features, not explicitly mentioned above, it cannot be translated to Dynamics 365 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).