dotConnect for Magento can translate a subset of SQL-92 SELECT statements to the corresponding Magento 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 Magento object.
• where_condition - one or more conditions, united with 'AND'. 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 Magento 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 Magento API calls and is executed locally.

• WHERE clause for columns of nested or related objects.

Filterable Columns and Allowed Comparison Operators

Magento supports only the following columns with the following comparison operators in the WHERE clause:

• Customers object - all supported operators for any column.
• CustomerAddresses object - "=" operator for the "customer_id" column.
• Regions object - "=" operator for the "country_id" column.
• ProductAttributes object - "=" operator for the "set_id" column.
• ProductImages object - "=" operator for the "product_id" column.
• InventoryStockItems object - "=" operator for the "product_id" column.
• SalesOrders object - all supported operators for any column.
• SalesOrderItems object - all supported operators for "order_id" and "order_increment_id" columns.
• SalesOrderInvoices object - all supported operators for "increment_id", "created_at", "order_currency_code", "order_currency_code", "order_id", "state", "grand_total", and "invoice_id" columns.
• SalesOrderInvoiceItems object - all supported operators for "invoice_id" and "invoice_increment_id" columns.
• SalesOrderCreditMemos object - all supported operators for any column.
• SalesOrderCreditMemoItems object - all supported operators for "creditmemo_id" and "creditmemo_increment_id" columns.
• SalesOrderShipments object - all supported operators for "increment_id", "created_at", "total_qty", and "shipment_id" columns.
• SalesOrderShipmentItems object - all supported operators for "shipment_id" and "shipment_increment_id" columns.

Remote Execution Performance Tips

Many objects are retrieved in two steps: first the list of all the object IDs is retrieved, and then their data are read. However, if you specify the object ID in the WHERE clause, the necessary object is read immediately. Thus, specifying the necessary object ID in the WHERE clause speeds up the query execution. It's better to specify the object ID in the WHERE clause for the following objects: Categories, Products, SalesOrders, SalesOrderInvoices, and SalesOrderShipments.

All the columns are actually queried regardless of the column list in the query, and omitting some columns in the SELECT clause does not affect performance.

Many objects depends on the other objects directly, and cannot be retrieved without the ID of the parent entity. In such cases parent objects are read first, and then the queried objects are read by the second request. If the ID of the corresponding parent object will be specified in the WHERE clause, the first step can be omitted, and this improves performance. Here is the list of such objects with the necessary parent ID fields:

• CustomerAddresses - it's better to specify customer_id.
• Regions object - it's better to specify country_id.
• ProductImages and InventoryStockItems objects - it's better to specify product_id.
• SalesorderItems object - it's better to specify order_increment_id.
• SalesOrderInvoiceItems object - it's better to specify invoice_increment_id.
• SalesOrderCreditMemoItems object - it's better to specify creditmemo_increment_id.
• SalesOrderShipmentItems object - it's better to specify shipment_increment_id.